Using a query within a form - Microsoft access

Question

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.

Note this would be simpler if the data from both years was consolidated into a single table which includes a field to store the data year. — HansUp
– HansUp, Commented Jul 24, 2013 at 15:49
Since you are showing so little data, just copy the form and point it to Cotton11, open both forms side-by-side. — basdwarf
– basdwarf, Commented Jul 24, 2013 at 16:33

Johnny Bones · Accepted Answer · 2013-08-02 19:11:48Z

1

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.

answered Aug 2, 2013 at 19:11

Johnny Bones

8,5027 gold badges61 silver badges127 bronze badges

Sign up to request clarification or add additional context in comments.

Collectives™ on Stack Overflow

Using a query within a form - Microsoft access

1 Answer 1

Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

1 Answer 1

Comments

Your Answer

Sign up or log in

Post as a guest

Related