MS Access Query

Question

I am creating a query based on two tables to return a value.

table1(field1) = table2(field1) then return table1(field2)

Basically I'm comparing a field in table one that has the same value as a field in table 2. When it does then it needs to return the corresponding value from a second field in table 1.

I've attempted to use DLookup but it doesn't seem to be reliable. It works but if I leave the app and relaunch it doesn't always work.

Answer 1

Hello

You can use a DLookup function in the Control Source property of the County field in your form to automatically fill it with the corresponding county from the ZipCodeTbl table.

Here is an example of the DLookup function you can use:

=DLookup("County","ZipCodeTbl","Zip='" & ContactTbl.ZipCode & "'")

This DLookup function will look up the County field in the ZipCodeTbl table where the Zip field matches the ZipCode entered in the ContactTbl form.

You can enter this DLookup function in the Control Source property of the County field in the form.

To access the Control Source property, open your form in design view, select the County field, and then go to the Properties window. In the Properties window, look for the Control Source property and enter the DLookup function in the textbox.

Alternatively, you can also use a JOIN statement in your form's query, which is more efficient as it returns only the necessary data.

For example,

SELECT ContactTbl.ZipCode, ZipCodeTbl.County FROM ContactTbl JOIN ZipCodeTbl ON ContactTbl.ZipCode = ZipCodeTbl.Zip

This query will join the two tables on the ZipCode column and return the ZipCode, County columns from both tables.

The above SQL statement should be placed in the Record Source property of the Form

This way the form will automatically fill the County field with the corresponding county from the ZipCodeTbl table based on the ZipCode entered in the ContactTbl form.

I hope this information helps.

Regards,

Abdal

Answer 2

I'm not quite clear where I am supposed to enter the Select statement. Does it go in the query? If so, where?

Thanks.

Answer 3

Now I want to autofill a field on my form using the data entered in the form (Zip Code) and pull the (County) from a second table.

In my form I have: ContactTbl.ZipCode and ContactTbl.County

I have a second table ZipCodeTbl.Zip and ZipCodeTbl.County

I want to enter in the ZipCode in my form and have it automatically fill the County on my form from my second table

This is where I tried to use DLookup and it didn't do the job consistently.

Answer 4

Hello

I am Abdal and I would be glad to help you with your question.

You can use a JOIN statement to combine the two tables based on the matching field, then select the field you want to return.

Here is an example:

SELECT table1.field2 FROM table1 JOIN table2 ON table1.field1 = table2.field1

This query will join the two tables on the field1 column and return the field2 column from table1.

It's good to know that DLookup is not the most efficient way to query data, especially when working with large data sets. Using a join statement is a more efficient and reliable way to query data from multiple tables.

I hope this information helps.

Regards,

Abdal

Answer 5

You are off to a good start! First, your Table1 should have Field1 defined as its Primary Key. You can then create a new Query; add Table1 and Table2 to the query design window. Drag Field1 from Table1 on to the desired linking field (Field1?) in Table2. Then drag the second field (the one you want to display) from Table2 to the top row of the grid below the tables window You can (and should) show multiple fields in the grid - Field1, and whatever other fields you want to see. You can then change the View from Query Design to (say) Datasheet to view the results; or (better) save the Query and create a Form to view and edit the data.