automatically fill a table using a list from another table

[cbkebasen]

I have a table being filled everyday that contains the following:

ID
EntryDate
Ward_ID (linked to the Wards table)
Census


At present, the data encoder manually chooses the ward via drop-down list to identify the ward, and fill the census field. Since we have 20 wards, we find this exercise eating our precious time (since we have tons more of data to enter aside from the census). I'm wondering if there's a way to automatically list all the wards in the table in a specific date, so that the encoder would just proceed in filling the census per ward. I'm not sure if this can be achieved through the use of forms.

One idea thrown was to design the table like an excel sheet (each ward has its own column). Another idea was to make individual tables per ward and make a default value for the ward_id. However I think these two are not the right directions.

any help?

 

[pr2-eugin]

Hello cbkebasen, while reading your post, I came across a few important things you need to sort out before moving into form design.

At present, the data encoder manually chooses the ward via drop-down list to identify the ward, and fill the census field.

This suggests that the Field Ward as a Lookup to another table, you need to remove Lookups in Table level. They will cause a huge problem when moving on to Form design and Queries in future. The Evil's of Lookup explained !

Since we have 20 wards, we find this exercise eating our precious time (since we have tons more of data to enter aside from the census). I'm wondering if there's a way to automatically list all the wards in the table in a specific date, so that the encoder would just proceed in filling the census per ward. I'm not sure if this can be achieved through the use of forms.

This can be achieved through a Form, in my mind I am thinking of a bit of VBA Code, but I am sure someone will have a better option. So is it a scenario where you have to enter the information for all wards everyday? Or someday you might not enter the information for some specific ward? These are some questions you might want to think it over !

One idea thrown was to design the table like an excel sheet (each ward has its own column).

Very bad idea ! Scrap that immediately. The current structure you have is the right one, stick with it.

Another idea was to make individual tables per ward and make a default value for the ward_id.

Again not the best idea, so simple calculation in your head 20 wards = 20 tables, what happens you have to add another 5 wards? Add 5 more tables? This is excessive/unwanted overhead. So stick to the one you have.

If there are no suggestion from other posters regarding Data Entry I will share my thoughts ! Good Luck !

 

[cbkebasen]

This suggests that the Field Ward as a Lookup to another table, you need to They will cause a huge problem when moving on to Form design and Queries in future. The Evil's of Lookup explained !

Thank you for the advice. We will correct this one ASAP.

This can be achieved through a Form, in my mind I am thinking of a bit of VBA Code, but I am sure someone will have a better option. So is it a scenario where you have to enter the information for all wards everyday? Or someday you might not enter the information for some specific ward? These are some questions you might want to think it over !

Yes, information will be entered for all the wards everyday (even if the census is "0"). Maybe good to start with what you have in mind?

 

[Mihail]

I don't know what a "ward" is. Google Translate didn't help me here.
But, based on the structure of your DB, you should have a form based on table "Wards" with a subform based on table ... Census (?)

This will allow you to select, one time, the "ward" and to input all the "census" for that "ward"

PS
I not fully agree with Paul's regarding uses of LookUp(s).
While Paul is right regarding to the issues that could came, I know from experience that this is the easiest way to manage things as beginner.
In time, after you will gain more skills and your DB will become more and more complicated,, the Paul's advice will become a "must".

 

[pr2-eugin]

I not fully agree with Paul's regarding uses of LookUp(s).

My mom taught me "Prevention is better than Cure !"

 

[cbkebasen]

I don't know what a "ward" is. Google Translate didn't help me here.
But, based on the structure of your DB, you should have a form based on table "Wards" with a subform based on table ... Census (?)

This will allow you to select, one time, the "ward" and to input all the "census" for that "ward"

Thank you for your reply. No offense meant, but this unfortunately is pretty similar to what we are doing right now in our form (the data encoder selects the ward, then puts the census for that ward; then moves on to select the next ward, and put the census for this ward... until 20 wards have been filled up). we're thinking of a way if we can, on a specific date, show all the wards that we have so that all that is left to do is just to put the census. that way we believe that it will make the data encoding faster.


PS
By the way, "Ward" also means section or department.

 

[jdraw]

It might be clearer to readers if you could give a plain English description of the process along with some examples of ward, census...

How does the encoder get the data for data entry? Any specific order to records?

 

[Mihail]

Thank you for your reply. No offense meant, but this unfortunately is pretty similar to what we are doing right now in our form (the data encoder selects the ward, then puts the census for that ward; then moves on to select the next ward, and put the census for this ward... until 20 wards have been filled up). we're thinking of a way if we can, on a specific date, show all the wards that we have so that all that is left to do is just to put the census. that way we believe that it will make the data encoding faster.


PS
By the way, "Ward" also means section or department.

It is doable, but hard for my English to explain.
Anyway, I'll try.

Create a query that include the Ward table and the Census table.
In the EntryDate Criteria set a parameter, in order to input, somehow, the date you need. (the best is to use, as criteria, the value from a form's control.

Based on this query, create a form and use this form as subform in the Ward form.

I know. It is hard to understand, but give me your DB and I'll show you (Access 2003 or 2007)

 

[pr2-eugin]

So this will give you some basic idea ! Check the attachment.

What I have done is,
Created two tables,
* WardInfo - wardID (PK), wardName
* CensusData - autoID (PK), wardID (FK), dateOfEntry, censusData

Created a Form to get the date you are trying to add the information for in a text box, by default will hold Today's date.

When you click the button, an Append Query is run to the table CensusData. Adding the date you selected for the number of Wards, specified in the WardInfo table. So if you selected 30/01/2014 and the ward table contains 3 records. The CensusData will hold 3 new records with the same date and different WardID and empty CensusData.

Then you will be redirected to a Form, that will list the newly added records. You can just simply enter the census information.

If you are trying to add records when the data already exists, then a pop up will ask you if you wish to 'Edit' the census information. I have also added a ComboBox, that will list all the dates for which entries have been made. Changing the information, will filter the form accordingly, so you can continue to edit the data from there too.

*** DISCLAIMER - This is a general idea, if someone has a better option, please try it out before resorting to this. Also the code can be made a little bit robust, I have not added any error handlers, this will be just a general idea ! ***

 

[cbkebasen]

Tried this one and the idea works as we envisioned it. Thank you so much! You just saved tons of our time!

 

[pr2-eugin]

No problem ! Good luck !