CODEWITHSUNDEEP
formsms-access
user2614690
user2614690

Reputation: 1

Using a query within a form - Microsoft access

Hi I have a form that has a text box in the header and when the user enters the farmer Code into the text box the relevant record is displayed.

I am using the following criteria in the query within my Form Cotton12:

Like [Forms]![Cotton12].[2012]

Now I have 2 tables Table Cotton12 and Table Cotton11. They contain a database of farmers, from the year 2012 and 2011. Sometimes the data is the same sometimes it is not. For example Farmers are given farmer codes, however due to incorrect capturing of data different farmers have been given different codes throught over the years.

What I would like to do is in the same form, have the user enter in a farmer code and data from the 2012 table come up and the data from 2011 table come up so they can be compared and if that code is not in one of the tables the fields relating to that table stay blank.

For E.g. the form might look like this.

2012
Farmer Name
Acreage
Yield Estimate
2011
Farmer Name
Acreage
Yield Estimate

Your help would be much appreciated.

Upvotes: 0

Views: 165

Answers (1)

Johnny Bones
Johnny Bones

Reputation: 8404

There are 2 potential ways to do this.

The more complex way is to create 2 sub-forms, and link them both to the main form via Farmer Code.

The easier way would be to just use a Join query to bring in both 2011 and 2012 data into the query, and then filter the exact same way you're doing it now.

However, I agree with Hans Up that the data should really all be in one table and a FarmYear field should be added to differentiate.

Upvotes: 1

Related Questions