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 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'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.
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?
Is it possible to create a query that looks over two different databases for information. i.e I have two departments at work that have seperate databases and I need to be able to search for data in both of them on the same query.
I'm using a combobox (in a subform) which supplies some search criteria. I'm using the query Like [Forms]![Employee]![Course Subform].[Form].[Combo12] & "*" but its simply not working.
I hope someone can help with this query I am having difficulty with. The table I am querying has several expiration dates and Yes/No Fields Listed below is what I need and the query needs to return the name and data if only one or all of the following applies
Name "A Date" is before today "B Date" is before today "C Date" is before today "D Date" is before today "A Yes/No" = No "B Yes/No" = No "C Yes/No" = No
In other words, if all dates are = to today or later and Yes/No Fields are = Yes - I don't need them in the results.
I have tried this several ways using AND and OR but either way my results are ALL the records. not just the records that meet any or all of the above criteria. Maybe it's too much to ask? :o)
Hey.I know you can create queries in access to find fields that have a value between 3 and 7 in them for example but how would you create a query that retreives fields with values between 2 dates for example between 12/10/07 and 11/01/08Btw the dates are inday/month/year
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 apologize in advance if this question is completely obtuse, but I'll pose it to the group nonetheless.
My task is to analyze accruals versus actuals for the past quarter (i.e., July-September). What is of particular interest are the variances between the accrued and actual amounts. I have several fields of data, but the common field is Shipping Unit (SU). My initial plan is to upload the tables with the accrued and actual records separately and query SU's to pull in all instances in which there are matching accrued and actual SU's. This is where it gets a bit fuzzy for me. I would export the resulting queries into Excel, unless there is a means to produce the analysis directly from Access.
I haven't used Access in ages, so I don't recall how I might best proceed. Please advise with your thoughts. :confused:
Sub Service ID (primary key) Sub Service Main Service ID (foreign key)
one-to-many relationship
I would like to query the tables, so you search on the Sub Service and it returns all of the Main Services it come under. I would like this query to run off an Input Box that will allow me to type in the Sub Service or a word.
I have managed to get the Input Box, by putting the following code into the criteria :InputBox («prompt», «title», «default», «xpos», «ypos»). But when I type something in the search fails. I have chopped and changed the fields I search on, but can't get it quite right.
Can anyone help me on what fields to choose and what I need to set in the criteria to get the search running properly from the input box?
Also is there any way to program Access or VB to return the results in a message box e.g. **** is a Sub Service of ***** - listing all main services it appears under?
OK, coming up with a title for this one was a little difficult. Almost as difficult as the problem I'm facing ;)
Here's the basic premise. The database is used to register people for a weekend convention. Part of the registration allows the person to request a specific roommate. In an ideal world, this would be relatively simple--match the roommate to the person, and you're done!
Did I mention they're teenagers?
Here's what happens. In all of the following examples, you are given only the ID number of a person to start with (because referencing any column but the bound one in a combo box from a query seems impossible, despite what Microsoft has to say (http://office.microsoft.com/en-us/assistance/HA011730581033.aspx)), and must dump all relevant names and ID's into a temp table. Example 1:
In this case, we have two people that have requested Matt Brown as their roommate. Based solely on the ID Number of any given person above I need to dump all three names into a temp table to be displayed to the user, so he can pick who will go where. Physically, the first two people would be roomed together, and the 3rd would be roomed in a single room nearby.
Similar to the last, but we've added a fourth person that has requested the 3rd person. Doing it the manul way, we'd room the first two together, and the last two together, in rooms next to each other. Unfortunately, we may not find the last two until we're two floors away, and end up having to shuffle people all over the place to get it to work. Now, we could be jerks, and just say, 'sorry, you screwed up, your problem, not ours'...except facing a crying 14-year-old girl upset because she's not with her friends isn't exactly fun. This is why I want to find all four people immediately after rooming any one of the above.
Example 3: IDFirstLastRFirstRLast 1MattBrownLukeWilson 5LukeWilsonPhilHeart 9PhilHeartCraigJohnson 72CraigJohnsonMattBrown
Ah, the circle of life. Person 1 requests person 2, person 2 requests person 3, 3 request 4, 4 requests 1. This is surprisingly common. And annoying. Physical results would be the same as the previous example.
So to summarize, I start with person 1. Add him to the temp table. Then find the person he requested as a roommate. Add him to the table. Then look at the roommate's requested roommate, compare it to the temp table. If the person is there (ie, a direct match), then stop. If he isn't there, then query the database. Not there? Stop. Found another hit? Add him to the table. Search again. Then, display them all in a list box allowing the user to pick and choose who goes where.
I don't hope to automate the whole thing. That's not the goal. There are a variety of other factors that go into rooming that could never be automated, and require a personal touch. All I want to do is make it easier to find duplicates to streamline the existing manual procedure.
