I am new to this forum and a beginner to intermediate with Access.
I was given a database that has 3 tables with data on the capture of alligators. One from 1998-2000, one from 2001-2003 and one from 2003-present. Each of these tables has different field names because they were created by different people on different projects. I need to combine the three tables into one that pulls such data as "date", "time", "size" etc. I don't need all of the fields from all of the tables just select ones and some of the tables do not have the information that I'd need in the final table. I've read through append, update, and make-table queries and am not sure if I can even solve this problem with a query. The error messages I get refer to null values or if I do get a table it has 14000 records, which is way more than the actual number of records.
I have a database where the primary key is a field for pass numbers. Many of the pass numbers begin with zeros (example: 0023456). I changed the table property for the pass numbers to text so the zeros would be recognized. However, I have a form based on a query to search this pass number field. How can I get the query to recognize the pass numbers that begin with zeros. When I put in any other number above zero, the pass number satisfies the query and the employee information pops up. Aside from AllowZeroLength and trying to format the text field, I cannot get the query to recognize the pass numbers that begin with zero. Please help:eek:
Please can anybody help. I wanted to have a form containing a list of members forenames and surnames in one record. I wanted to be able to input the members reference number and then the forename and surname would be automatically filled in. I have achieved this through a query based on a table with ten foreign keys for the members. The query uses the members table ten times to join the ten foreign keys. This works fine and the form runs with space for ten members and I can enter a members reference number in each field and their forenames and surnames are filled in. The problem is that I wish to run queries with members surnames as criteria to bring out all records containing those members. How can this be done without having to set the criteria in the query in each of the members surname fields.
I know I can do this if I have a separate record for each member in the form but I really need ten members in each record. I hope this makes sense and somebody can help.
I wonder if someone can clear up a problem I am having. We have a system under development which is heavily dependent on an existing MS Access database. Some of the queries in the Access version of the application query Memo fields and they work fine within Access.
If I try to use exactly the same query from an ASP/Vbscript web page the query does not return the expected results. I am using ADO and an ODBC DSN connection, all of the other queries seem to work fine.
I have a vague recollection of being told that you can not query Memo fields through an ODBC DSN, or that not all of the Memo field is queried, only the first 255 characters. Can anyone confirm this is true. I have tried searching the web, and some references that I have available, but it seems that there is nothing out there to confirm my suspicions.
Any direction anyone could give me would be hugely appreciated.
Just joined this forum and posting my very first thread.. ^^
I need to create query to pull data from two different tables. This should be really easy query for most of you. Once someone help me set it up, I can do the modifying.
If you look at the image, there are two tables. For each of pf_id (which is unique ID), I need to know the dept_name which is on other table. I know two tables can be linked with dept_id but I don't know how to create query. If you can help me , I would greatly appreciate it~
Result should be like below.
pf_id, dept_id, dept_name ___________________________ wh08051bh-Navy, 01-001, washed hat wh60516bh-white, 01-001, washed hat . . . ru11072bh-white_with_navy, 01-002, roll up bucket hat . . dh03871bh-Dk._Blue, 01-003, denim hats . .
I'm trying to query tables I have imported as 'linked tables' from a SQL database. I've built a query, but when I run the query it says 'Invalid Procedure Call'
I have two tables, which have same fields. The tables are used for keeping record of news clippings, that are clipped in two different offices. I understand that the best way to manage the database will be combining them into the same table. Yet, it's not an option for the time for some technical reasons. Each table has the same fields as follow:
Input date issue date headline in English news source category
I want to have a list of news clips from the two tables by a specific category. I will have a form, that allows user to select category as the criteria to query the two tables. I manage to do that in a single table, but how to do it for two tables. This will be very help if you can help. ideally, I can use category to query news clips as well as academic journal clips (from another table) table.
During a client's stay with our agency, they are served among multiple contracts and are placed within multiple programs. Because clients move frequently between contracts and between programs, we have separate tables which have start and end date fields. It is common for any client to have multiple listings in these two tables, but with different event dates.
I am struggling to create a query that will capture each client's most CURRENT contract AND program. In the future, I'll want to create a parameter query that will allow the end user to enter a range of dates and capture all of the contract and program movement during a period of time.
I have linked tables from an Oracle database. I want to run a query to find records that have dates in a defined range. The date field in my linked Oracle table is in the date/time format. When I run my Access query, I only get those records that have a date (and no time) in the field. How can I get all records, even those with a date/time entry?
how to query data in my database based on a number of different criteria.I have reached a stage where I can get all the data I need from one query, however I can't figure out how to further query this data to return records from a table with the most recent date only. I have searched the forum, googled and experimented myself but I am running into "Aggregate Function" errors.In this scenario there are 3 tables. tblJobs, tblEquipment and tblInspectionLog. Each tblJobs record can have multiple tblEquipment records attached to it, and each tblEquipment record can have multiple tblInspectionLog records attached to them.
I would like to query the database for what tblEquipment records have been assigned to a tblJob ID and also return only the tblInspectionLog record with the latest Inspection_Date field.At the moment I am able to see tblEquipment records attached to tblJobs, however duplicate records appear due to multiple InspectionLog records associated with the equipment.
I have tried to filter records from tblInspectionLog using the "Max" criteria under Inspection_Date field in my query. This however returns an "Aggregate Function" error.
Basically in my order details table i have the following fields
Product Unit Size
At the moment i have the Product field with a dropdown that gives me all the products from my ProductT. But once i choose the correct product in the unit field it gives me all the possibilities of every product not just the units associated with that product. ie
ProductT Grasshopper Box1000 Adult Grasshopper Box1000 Subadult Worm 10pz Big
When I select the grasshopper product and move on to the unit field i also get "10pz" option but this is not a product available.
How do i set up validation of the fields Unit and size based on another fields data?
I reached the limit of 255 fields in a table. I just need to add one more field so I deleted several fields I no longer needed thinking I would then be able to add one more new field. However, I am still unable to add one more field. How to free up fields that are no longer needed?
I am using calculated field as a data type in access 2010.
They are working fine.
However, I added a new field and now the final calc won't work.
I have Subtotal adding loads of fields together. Works fine.
Then I have a VATunit field which is a double integer, so enter 20 and my next field is VATTotal calculates the SubTotal + the VATunit by doing (Subtotal/100)*VATunit. This calculation is fine and gives me the correct amount.
The next field is a Total field. Which adds Subtotal and the VATTotal together. Howver, the Total is the same as Subtotal. It is not adding the VATTotal to it?
I have two tables of data, each relating to three business branches (branches A, B and C).
Table 1 shows the expenditure of each branch (by fuel, premises and wages).
Table 2 shows a number of units for each branch (mileage, floorspace and sales).
What I would like to do is calculate unit costs, based on the expenditure in Table 1, divided by a relevant unit in Table 2. The catch is that I want to have a third table which allows the user to specify which expenditure (from Table 1) is combined with which unit (from Table 2) to generate the calculated unit costs. I've been able to do this in Excel, and have attached an example. I've also attached an incomplete Access version with the first two tables. Given the complexity of my actual data, I feel this could be better handled in Access than Excel.
I am working on a database which has two tables used as part of a registration and login process.
I would like a couple of fields from table one to automatically update in table two, once the fields in table one are populated without using an 'on click' event.
The reason I would prefer not to use an onclick is because the completion of the form used to generate the users table does not require any buttons for the data to save.
i would like to sum all the column fields in a tables column where an id is the same as the id in that table. I know you can use the SUM function in the select statement of your query.
But how can i set this sum result into a variable, so i can use it for calculation further on in the program?
I have 2 tables tblworkdone and tbltests, both have a date field and are both subforms in a tabbed form on the main form. When I enter a date into the tbltests subform I would like the date to automatically be entered into the tblworkdone date field and create a new record so that when I move to the tblworkdone subform with the date already there.
Hi I'm having a bit of a problem with the sql query below
SELECT [Table 1 Personal Details].Surname, Sum([Table 3 Daily Activity].[Public Holiday]) AS [SumOfPublic Holiday], [Table 3 Daily Activity].[Week Commencing], Sum([Table 3 Daily Activity].[Authorised Absence]) AS [SumOfAuthorised Absence], Sum([Table 3 Daily Activity].Holiday) AS SumOfHoliday, Sum([Table 3 Daily Activity].Sickness) AS SumOfSickness, Sum([Table 3 Daily Activity].[Unauthorised Absence]) AS [SumOfUnauthorised Absence], Sum([Table 3 Daily Activity].[Hours Per Week]) AS [SumOfHours Per Week], [Table 9 Funding].Project FROM [Table 1 Personal Details] INNER JOIN ([Table 3 Daily Activity] INNER JOIN [Table 9 Funding] ON [Table 3 Daily Activity].[ID Number] = [Table 9 Funding].[ID Number]) ON [Table 1 Personal Details].[ID Number] = [Table 3 Daily Activity].[ID Number] GROUP BY [Table 1 Personal Details].Surname, [Table 3 Daily Activity].[Week Commencing], [Table 9 Funding].Project HAVING ((([Table 3 Daily Activity].[Week Commencing]) Between [Enter Start Date] And [Enter End Date]) AND (([Table 9 Funding].Project)="NETWORKS")) ORDER BY [Table 3 Daily Activity].[Week Commencing];
My problem is that the Surname is displaying for each record.. what I mean is that I want the surname to be grouped so that I get the SUMofHours summed correctly?
Here is my problem, im working on an old 97 database that is being used by a program and sometimes errors occur through the use of the program and certain tables are not updated properly, so instead of doing this manually I want to be able to query this database through SQL and then later down the line put these SQL queries into a program so it will be more user friendly to edit the database.
So when I try and test one of my queries out, access tells me that I cant make changes to the database because it was created in an earlier version. Now I know i can update the database, but if I were to do that the program that is using the database wont work!
So im a little stuck now, will I have to keep editing the database the manual way or is there another option here? Im wondering (for any java heads out there) If I were to write a java program that would query the database and edit the data inside would that work or would I get back an error?
Ok, I have a DB of client contacts. I'm drawing the values for a query from a form using [Forms]![Sort]![Contact] in criteria and using the parameter field to give values to each. The query works fine for state, contact and industry- all in conjunction with each other. Where I am having problems is:
1) When I input less then all 3 working parameter values in the referenced form the query will not run. I think i need an SQL string of some sort- doing an if, then relationship, but I don't know how to do this.
2) I don't just have 3 values I want to reference, I have 9 total. On the form one of the problems is that a field is chosen by the end-user using check boxes (allowing only one check between three values, with no default: current client, potential client, not applicable are the choices). However, in the table these are referenced by the values of 1, 2 or 3. Another of my fields is check boxes, but referenced by yes, no. How do I correspond the field values in text to numerical values in the table? (and the yes/no prbly the same I will assume)
3) I have "sectors" as a field option in my Sort form (again the drop down menu to select). There is one field for the choice, however, in my data table I have 4 fields corresponding all back to that one field in the form through the query. This doesn't seem to work at all. The sector choices, in my original Input form, are 8, chosen and recorded into the table using 4 consecutive drop downs. They are the same 8, so sometimes a sector like Agriculture could end up in the Sector 1, or Sector 2, etc field in the table. Some can also be left blank. So in summary of this dilemma: 4 drop downs in input form to 4 data table fields, all being queried through one field on the Sort form, which is tied to my query through a command button, having specific criteria reference text in the form.
Is there a way of querying for an astricks? Some data in one of my tables has data that looks like this "A*" or "B*" or "C*", but most of the records are just "A" or "B" or "C"...I just need a query that I can pull out those records. Is there a way of doing this in a query?
OK, I work for a political party. I work with voter history information. Normally, I am doing queries in order to bring up particular voters, and eliminate others.
Example - the basic table I work with contains the names, addresses, affilitations, and voting histories of everyone in my county. I normally query to bring up voters of a particular party, or people who voted in a particular election or number of elections or both. I more or less sort voters out, but in a positive manner, by creating tables with voters who did a particular thing.
For example: out of all the voters, I just want Democrats who voted in two of the past three primaries. I would then get a table that contained just those voters and no others.
Today, though, I was asked to create a table that would exclude voters based on their behavior, exluding voters who did a particular thing but showing everyone else.
Example, I have a request for all of the voters in the county, except those who voted in the 2004 and 2002 primaries. I know how to create a table with those voters and no others, but I don't know how to query for everyone but those voters. Can anyone help?
I am getting really wound up with this. It should be so simple!
I need to create a query on a field (actualdateofvisit) and group it by the month to give me a count of all those visits (hence the catchy field name) which occurred each month since the beginning of the project. In other words I want to see:
Month Count of Visit Jan 43 Feb 54 Mar 78
and so on.
I have tried everything I can think of but cannot work out how to do this? I need to do it in a query NOT a report as I want to port it to Crystal reports to display there.
I am think of something like:
field: Actual Date of vist table: visits total: Group By Criteria: "Month"
I have a query that is supposed to get a time value from a field and enter a string into a new field based on the time it gets.
this is what i have. Its my VERY first query ever and so i'm not sure of the syntax. I doesnt work though so i'm assuming something must be wrong. Can anyone help?
