Using a Query Within a Form

[Gambit17]

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 thank you

 

[plog]

You need to take 5 steps back, forget about forms for a while and properly structure your data.

Field and Table names should not contain any specific information about the data they hold. That 2011 and 2012 information shouldn't be in the tables' names, but in a field within a table. Instead of a table for every year, you should have 1 table and then a field that tells you which year the data in that specific record is for.

Next, to fix the issue of farmer's having multiple codes, you need a Farmer's table which has information about unique farmer's (FarmerID, FarmerFirstName, FarmerLasName, etc.) and a FarmerCode table that sorts out the multiple codes issue.

For example, let's say Farmer Jones has 3 codes and Farmer Smith has 1 code. This is what those 2 tables would look like:

Farmers
FarmerID, FarmerFirstName, FarmerLastName
1, Dan, Jones
2, Jim, Smith

FarmerCodes
FarmerID, Code
1, F167
1, F178
2, F921
1, F367

 

[Gambit17]

Hi Plog,

I really appreciate your reply and I think your suggestion of having the year as a field is a good one.

I would like your advice, if you have the time, on my database.

I have just arrived in Uganda and have been tasked with converting what used to be an Excel/paper database of about 12000 rural farmers in to an access one or just a more efficient excel one. I have chosen excel just because I thought it would handle the data better.

So here is how the system works. You have a field officer, who is given a field officer code, underneath him you have multiple lead farmers who again is given a lead farmer code and beneath him you have multiple actual farmers who are also given a code.

Every year registration forms set out to the field and filled out with all this data. Now the people filling out these forms aren't the most literate so name aren't spelt correctly and someone who was given the code 1 might be given the code 27 the next year. It is not a perfect system I know, but it is beyond my control.

So now as I speak hundreds of registration forms are coming in from the field. In the past, last years excel database would have been scrapped and this years database would have been entered in manually from scratch. You can see why this is terribly inefficient. So I have imported the previous excel spreadsheets into access as table cotton12 and cotton11 my goal being if I can search for the farmer code and find corresponding records then these can be added to the current years database. If the code can not be found then I will just have to enter in the data manually.

So yeah if you have any suggestions that would be great.

P.S I can't search for farmer names as they have about 50% chance of being spelt the same way they were last year.

Again thanks so much

 

[Gambit17]

Sorry I meant I have chosen access as the database program to use

 

[plog]

The way to do this is to create the proper structure regardless of what your data looks like now. Then you force your exisiting data into the structure--this might be painful because its not currently structured in the same manner as your database, but once you have it in there you will be able to reap the benefits of it.

Read up on normalization(http://en.wikipedia.org/wiki/Database_normalization), spend a week or two working on your structure and get it right.

 

[highandwild]

My personal view is that I think that you should get the data into the state that you would like it to be in. That is, each farmer only has one code and the name of the farmer is always spelt the same. This may involve lots of work initially but only a one off exercise and it will make the rest of your work so much easier and something approaching 'normal'.
It will also make asking for help on here easier.

When finished, the name of the farmer should only ever be in one record of one table and you link to this table using the farmer code when you want to display the name.

You should be able to accomplish this with some update queries. Save a copy of the pre-changes data in a seperate database.

You could have a farmers table and a registration table linked by farmer code. The registration will have one record for each farmer for each year.

You can keep a record of the multiple codes for the farmers but it does not need to be used within the system that you are developing but for reference.

If data entry is out of your control as you say then try and bring it within your control. This may involve having a data quarantine area whereby each record entered goes through a checking process before entering the live system.