how do i do a look up query??

hi, i have 2 sets of data (2 tables) and i want to know how to do a query where i can match the two?
basically this is what i want to do (i know its probably very easy but i am a novice so bare with me)

i want to take both tables..

do a look up query, to filter out data , basically i want it to look at this one table (a third table that will have certain data) then go back and pull anything that matches that table from the other two and create a new table with the results


table one contains orders ABC and table to contains orders DEF , well in the look up table there are orders EBA, so when i run the query i want it to look up table 3 and anything thats in there (in this case EBA) return it from the other two tables in a new table

god this is confusing, im sure this is VERY simple im just over explaining.

Post your answer or comment

comments powered by Disqus
I made a report that pulls data from many tables and, for each list in my report, Access asks me for criteria in a box. How do I set it up so that it only asks for one criteria (since all are the same - customer number) and assumes it is the same for all the other criteria?

Hi Experts:
I am a newbie to Access & VBA.

I have a strange problem. I am writing a database for a political candidate
in a ward with 24 precincts. There are over 13,000 residents in a table.
The candidate wants to make seperate sets of mailing labels via precincts
(about 400 residents per precinct). I have to put the precinct number in the
header (not the mailing labels) and give the candidate the option to choose
which avery label number. I chosed to used a query that will received the
precinct number from a combobox and a text box to give the avery label number
to the mail merge module.

The database is a secure database with a workgroup file (mdw). The database
is in Microsoft Access 2000 format (mdb) and runs on Microsoft Access 2003
from Microsoft Office 2003.

In addition, I am running the dialog form from the switchboard. (The problem
only happens from the switchboard after the user logs in)

In the code under opendatasource if I used the

connection:="Query MyFormQuery"
SQLStatement:="SELECT * FROM [MyFormQuery]"

I get a login prompt (which I do not want, because the user will already be
logged in on the database). In addition, I get a parameter prompt for the
precinct (but I have already chosed the precinct from the dialog form's

To get around from having to be prompt by the login box I chosed to change
the connection to:
.OpenDataSource _
Name:=CurrentDb.Name, _
ConfirmConversions:=False, _
PasswordDocument:="", _
PasswordTemplate:="", _
WritePasswordDocument:="", _
WritePasswordTemplate:="", _
Revert:=False, _
Format:=0, _
LinkToSource:=True, _
AddToRecentFiles:=False, _
ReadOnly:=True, _
Connection:="DSN=MS Access Database;DBQ=" & CurrentDb.Name & ";"
& _

"SystemDB=C:WorkGroupSecurity1.mdw;UID=UserName; PWD=userPassword;FIL=RedISAM;", _
SQLStatement:="SELECT * FROM [ResidentTable]", _
SQLStatement1:="", _

But now I cannot used my form query!!!
In addition, when I am using my form query I get results back within one
minute but even when I figured out how to used the SQLStatement to get the
precincts using a variable named "UserPrecinct" it took 30 whole minutes.
And, the precinct number had to be put in the mailing label for it to work.
The candidate do not want the precinct number inside the mailing labels (but
he wants it in the header).

My question:
How do I get my form query to work from the switchboard on a secure
workgroup database, without being prompt with a login box and a parameter box?

Note: If I do not run the form query from the switchboard everything works
just find. Only when I run it from the switchboard that I have problems.

The subroutines header in the module is:
Public Sub MakeLabels(HeadingTitle As String, UserPrecinct As String,
AveryNumber As String, MyFormQuery As String)
Please help me the best you can.
I am stumped!

Thank you


Here's a brief run-down of my situation:
I work for a company which cleans and repairs rail cars and I've already set up a table and corresponding form to collect the number of cars we ship out and the number of repeat shoppers we get each day. We have an incentive plan where for each car we ship out, we are creditted $30. The incentive plan also states that every year, we are allowed to have up to five repeat shoppers without being penalized; we are then charged $300 for every repeat shopper beyond that point (ie - the sixth repeat shopper and so on for that year). The formula I've been using to calculate the running sum for the repeat shopper penalty in my queries is: "([Sum Of Sum Of Repeat Shoppers]-5)*-300".
I'm having difficulty setting up a query for a report which will:
- list the number of cars shipped and the number of repeat shopper for each month based on a starting date and end date I specify
- give me the amount of money generated each month based on the above-mentioned incentive plan guidelines
- give me a final total of the profit/loss generated
I've tried setting this up several times already but haven't been successful. One of the problems I'm running into is that if I enter date parameters where fewer than five repeat shoppers are counted, we are creditted $300 per car less than five total. How do I set up a formula that subtracts $300 from our profits only if there are more that 5 repeat shoppers within the date parameters entered?


How do I get my append query working so it doesnt append duplicates onto an existing table? Currently if I run the append query I get multiples of the same data?


I made a report that pulls data from many tables and, for each list in my report, Access asks me for criteria in a box. How do I set it up so that it only asks for one criteria (since all are the same - customer number) and assumes it is the same for all the other criteria?

I'm trying to figure out how to make a look up field to include specific items I've referred to an office book I have and searched online just not understanding the instructions and keep getting error messages

I have 3 tables: Projects, Systems, Issues.

There is a many-to-many relationship between projects and systems. One project has many issues. Crucially, each issue belongs to one project. Incidentally, each issue belongs to one system.

My problems revolve around how to structure tables in a sensible way.

Presumably I need to set up another table to record the relationships between projects and systems. Lets call it ProjectsSystems.

Question 1: how do I ensure that a user can't duplicate project-system combinations?

Question 2: should the schema in the Issues table link to Systems table, or to the ProjectsSystems table?

Question 3: how do I set things up so that when the user creates an issue, he can only select systems that are in the ProjectsSystems table for the particular project to which the system relates?

I have imported a number of tables into my access db, and i'm now writing a program that needs to look up values from these tables.

for example,

Firstly sorry if the wall of text is abit well walley

I am trying to design a querie which lets you select multiple criteria from a drop down combo box (job skill) so when it runs you are promted to enter skill1, skill2, etc and then shows a table of people with all the skills selected.

i am abit rusty with my SQL (tho i am rebrushing up on it with the many books i seem to ahev baught oevr the years) so imusing the query builder in access.
once i set up the fields i want, i am using the parameter [enter skill here] and it works fine, the problem is if i want rmeo then one skill, i have tried using [text] & [text] but it only asks once for the skill as before.
I have also tried using it in diff columns with the same results

does anybody have any ideas as to how to do this using a look up combo box, (or as this is just a prototype any other sugestions)
I have looked on google breifly but the results can be vague or unrelated, so if u have a way to point i dont mind doing the research myself

or if its SQL statement thats ok also

If you need more info please say

Thsi is not a school project its for a job (there current database software isnt rly compatable with this side branch of the company, so i have been asked to make a quick access database to handle it)

Thanks for any help you can give


I have several year-to-date crosstab queries that look like:



I've developed a multiple UNION statement that combines these crosstab queries into a single query that looks like:



In addition to a query with detail rows, I also need to create a summary querie with single rows containing the totals from each of the above queries, which should look like:


I'm assuming that this would probably be done by creating individual summary queries such as...



...and then doing a UNION to combine them to get the qryTypeSum shown above.

However, if there's a way to bypass the individual summary queries and go straight to the qryTypeSum, it would certainly be even better.


Unfortunately, I can't seem to create one-row queries containing only summary data, from the crosstab queries (qryGrubType, qryFoobType, et al), on which to make the qryTypeSum UNION query.

Any and all suggestions would be appreciated.



Say for example I have the following 2 database tables, the first one contains the old employee data, and has the fields shown below:
and another one containing the new employee data with similar fields but instead of oldEmployeeID, it is showing the newEmployeeID.
During the conversion process, something were messed up and instead of putting in the original hiring date of the workers into the new employee database, the conversion date was put in, which, depending on the mood of HR ladies, could be any date, and at the same time, of course, new employee join the company, and we assume their hiring dates were entered correctly. On top of that, there are some employee who were terminated before the conversion took place but we still need to keep a record of that.
And I created a third table, say, emplyeeAll with similar fields to the employee data tables.
So here is what I need to do: if the firstName, lastName and DateOfBirth in the old employee data table and the new employee data table matches, I would assume they are the same employee, hence I would put the information for the employee obtained from the new employee data table to the employeeAll table, with the Hiring Date changed to the Hiring Date of the old employee data table (and do not copy the record from the old employee table to prevent duplicates), otherwise, I would simply copy and paste the data in new and old employee table to my employeeAll table.
I know this is really confusing, but...well...hope you know what I am saying...
So is it possible to do this by using Queries? Would Cross-Table query help? How should I build the query?
Or is it actually easier to do all these by writing out the SQL statement directly? If so, how should the statement looks like?

Thanks a lot!


Because my tables were already large I split them up by environment to speed up data maintenence and retrieval. Thus I have table names which contain the environment as part of the table name (ex: tbl_TEST_MyTable, tbl_PROD_MyTable). Once the user sets the environment they want to use (by clicking on an option button) I update the form's recordsource to the appropriate table. My form has 5 subforms within it.

Even with the split loading the form was taking a long time because some of the queries the subforms are based on are joining tables so that they can be linked (parent/child) to the form. I created indexes to speed up the data access which helped a little but it was still running too slow. So, I attempted to change the subform's queries to only join the necessary data by re-creating the queries each time the user changes the form's current record.

For example, what I had originally was taking too long:

SELECT DISTINCT tb.*, pt.CollId, pt.ProgName, pt.QueryNo
  FROM tbl_TEST_SysTables AS tb INNER JOIN tbl_TEST_Plan_Table AS pt
    ON tb.Name = pt.TName
ORDER BY tb.Name;

and the parent/child link fields are CollId, ProgName, and QueryNo.

This is the change I made:

SELECT DISTINCT tb.*, pt.CollId, pt.ProgName, pt.QueryNo
  FROM tbl_TEST_SysTables AS tb INNER JOIN tbl_TEST_Plan_Table AS pt
    ON tb.Name = pt.TName
  AND pt.ProgName = 'PROGRAM1
  AND pt.QueryNo = 123
ORDER BY tb.Name;

where the pt fields are changed dynamically each time the user navigates to the next record in the form. This resulted in much faster access time; however I discovered that the form was always displaying the query that existed before the form was opened. I have not been able to figure out how to get the subforms to display the latest created query.

While debugging I discovered that the form's OnCurrent event was being executed 3 times before the form is opened. I don't know what I'm doing that's causing this.

I also discovered that the subform events are executed before the form's events, so I think that's why the latest query isn't being used. I attempted to requery the subform in the form's OnCurrent and OnOpen event, but to no avail.

I am trying to avoid using the DLOOKUP function due to the impacts it has on performance (load times). This is especially prevalent on a not-so-fast-network connection. I have a form based off a query that itself is sourced from a table of work orders. There is another table that houses contact referenceID's which is related to the table of work orders. There is then a reference table of names that correspond to the referencID's on the table of contacts. Here is how it looks:

*Work Order Tbl...........*Contacts Tbl....................*Contact Reference Tbl
WorkOrderID (PK)-------WorkOrderID
Project Status..............ContactID(FK)---------------ContactID(PK)
Project type.............................................. ...........Contact Type
Project Name.............................................. .........Contact FirstName

I created a query that shows the Lastname of the contact of a specific Contact Type related to the WorkOrder. When I try to show the value of this field on the Form I just get #Name?. How do I refer to a field on a query that is not the source of the current form? Thanks!

Hello All,

I'm using the code below to filter a query "live":

'Purpose:   Build up the criteria string form the non-blank search boxes, and apply to the form's Filter.
    'Notes:     1. We tack " AND " on the end of each condition so you can easily add more search boxes; _
                        we remove the trailing " AND " at the end.
    '           2. The date range works like this: _
                        Both dates      = only dates between (both inclusive. _
                        Start date only = all dates from this one onwards; _
                        End date only   = all dates up to (and including this one).
    Dim strWhere As String                  'The criteria string.
    Dim lngLen As Long                      'Length of the criteria string to append to.
    Const conJetDate = "#mm/dd/yyyy#"   'The format expected for dates in a JET query string.
    'Look at each search box, and build up the criteria string from the non-blank ones.
    'Text field example. Use quotes around the value in the string.
    If Not IsNull(Me.txtEnteredForFilter) Then
        strWhere = strWhere & "([txtEnteredFor] = """ & Me.txtEnteredForFilter & """) AND "
    End If
    'Number field example. Do not add the extra quotes.
    If Not IsNull(Me.fkPlatformIDFilter) Then
        strWhere = strWhere & "([fkPlatformID] = " & Me.fkPlatformIDFilter & ") AND "
    End If
       'Number field example. Do not add the extra quotes.
    If Not IsNull(Me.fkPolicyIDFilter) Then
        strWhere = strWhere & "([fkPolicyID] = " & Me.fkPolicyIDFilter & ") AND "
    End If
       'Number field example. Do not add the extra quotes.
    If Not IsNull(Me.fkStatusIDFilter) Then
        strWhere = strWhere & "([fkStatusID] = " & Me.fkStatusIDFilter & ") AND "
    End If
       'Date field example. Use the format string to add the # delimiters and get the right international format.
    If Not IsNull(Me.DateEnteredFilter) Then
        strWhere = strWhere & "([DateEntered] >= " & Format(Me.DateEnteredFilter, conJetDate) & ") AND "
    End If
    'Another date field example. Use "less than the next day" since this field has times as well as dates.
    If Not IsNull(Me.DateRemovedFilter) Then   'Less than the next day.
        strWhere = strWhere & "([DateRemoved] < " & Format(Me.DateRemovedFilter + 1, conJetDate) & ") AND "
    End If
    'Chop off the trailing " AND ", and use the string as the form's Filter.
    'See if the string has more than 5 characters (a trailng " AND ") to remove.
    lngLen = Len(strWhere) - 5
    If lngLen

I am new to access.

How do I create a from that can up date data linked to multiple tables.

I'm using Access and Excel 2007

I know how to import an Excel spreadsheet as a table.

I have several supplier price lists in Excel.

I want to keep my vendor price lists up to date.

When one of my vendors tell me that a price has changed on a particular item, I figure that I could have a form that I could use to enter the changes.

I believe the form would look like:

Field: "Vendor" (drop down list to choose from. Name of the Supplier price lists) Required.
Field: "OEM" (Key Field found in each table) Required.
Field: "Brand" (Field found in each table) Not required.
Field: "Price" (Field found in each table) Required.

OEM would be the unique key field.

If I enter the Vendor name and then the OEM number it would show if there is already that number in the Vendor price list and I could make changes.
Or I could enter new data in that vendor price list.

Thanks for your help.


how do i perform a Count(Distinct) query for 2 different column values using GROUP BY

I have this "source" table like this....

|001 | 001 | 002 | 12.00 |
|001 | 002 | 002 | 15.00 |
|005 | 005 | 009 | 9.00 |

I need a group by query with distinct for ms access 2007...

to make the following...aggregate query

|001 | 2 | 1 | 27.00 |
|005 | 1 | 1 | 9.00 |

Any help would be so grateful...

I looked at this suggestion but I am not sure its applicable:

I have created a report using a Query as the record source. Within the query, the user must enter a 'start date' and 'end date'. The query executes and the report displays the proper data. The user now wants the 'start date' and 'end date' to display in the report heading. How do I do this. Thanks for any/all help.

How do I write a macro that opens up the single form data upon AfterUpdate?

For example:
My first entry field is called ID# on the form. So, supposedly I am entering
a new data entry, as soon as I entered the information for ID# (which is
AfterUpdate), I would like to open up the file that will having matching ID#.
So, how do I write a macro that will open up a form that already contains the
ID#? If it does not that ID# already in the database, then I don't want that
macro to run.

Please help, thank you very much in advance.

I want to pull up an outlook send message window, the address having been taken off the access form .... how do i do this. I have looked up code but it looks complicated is it? Also unsure how to call a module once i have written or in my case copied it!!!!! aaaaahhhhh!

I i open a query using

With DoCmd
.SetWarnings False
.OpenQuery "myQuery"
.SetWarnings True
End With

How do I close it when I'm done

I tried

.close acquery "myQuery" according to the example, but the results are still open.

Also, while it is open, haow can I refer to the results in order to change or work with them?

How do i pass the parameter of a query in VBA?

My line is: cldDetail.SourceObject = "Query.qry_CDs", but the query requires a parameter (customer number).

I have a table with a number of fields and many records and I need to sum up the values of cells of a column that meets certain criteria (eg. column1=column2, iif(column3=0)) and then sum it up to become a value under a new column in a new table.
I need to make a number of these new summation values to make up a new table of two rows and a number of columns (depending on the number of summed values i need)
How do i do this with VBA code? or any other method (but i need this to be run when one clicks on a button on a form)
Thank you!!

I have a personal financial database that keep track of my accounts in three different countries, the US, Korea, and Australia.

I want to be able to display a report (or subreport, rather) of my accounts for each currency.

Korean won (KRW) do not use the "#,##0.00" system, just "#,###" (no decimal places, so when looking at reports of holdings by currency, the won comes up with decimals.

Additionally, I cannot use a dollar sign in the number format, because it will appear in front of the Korean won figures as well.

How do I conditionally change the number format?

Ideally, I would like the number format to exist at the table level, so that I can bring it up in both forms and reports.

I think this will require a macro; while I have no problem writing macros in Excel, Access VBA is new to me.

Any and all help/suggestions are greatly appreciated. Thanks!

I have a database with all of my contacts... I have a report that shows my contacts in alphabetical order and I have a form for adding new contacts. I was wondering if there is a way to have a report or query that will look up a contact by typing in the person or the company name.. Is this possible and if so.. how do I do this?

Not finding an answer? Try a Google search.