database creation

Topic

New Reply

I’m stumped. Maybe I’ve lost my mind, but I cannot figure out how to design a database for the attached. My coworker has 5 people who visit our rec sites daily, several times throughout the day. Each person carries these data entry forms with them and hand writes the numbers of visitors, cars, etc. at each location for each visit that day (they may visit each site 2 – 5 times a day.. could be more).

At the end of the day, all the sheets are gathered and one person manually averages the numbers for the day and enters the average number on a master sheet. Then the boss has to take each of these master daily sheets and manually enter the numbers into another spreadsheet where he can make graphs to show trends.

What he wants is a database that will store these numbers so he can run reports. He wants the data entry screen to look pretty much like what it does now (the attached spreadsheet). He doesn’t want drop downs to pick sites since they visit every site every day.

He said it’s fine if they still have to manually calculate the average and then only enter the daily average for each site.

He wants to be able to run reports showing the number of visitors for each site daily, weekly, monthly, etc.

Originally I thought we should keep this in Excel, but I’m not sure how to set it up so these non-computer savvy people can enter their daily numbers and easily run reports to show what the boss wants to show.

Any ideas?

Reply | Quote

Replies

Is it possible that the people might carry a laptop and record the info into a spreadsheet (or a database) as they make the visits? If not, then I would continue to use the same paper form for data collection. Then the person who does the data entry would need a form to do data entry from each paper form. I would start with a database design that records each line on the form when there is data recorded – and add who the person was who took the counts. You probably want a lookup table for the people, as well as one for the sites, so I would start with three tables – unless you are likely to add additional kinds of counts in the future. In that case you might want to normalize so that you have a counts table with one count field and another table that defines the different kind of counts (people, cars, boats, etc.). Hope that’s enough to get you started.

Reply | Quote

Thanks, Wendell. No, they can’t take a laptop. I already asked that.

The boss doesn’t want dropdowns in the data entry form. He wants it to look just like the spreadsheet. He said they enter something in every cell, and he doesn’t want them to have to pick a location from a drop down. Or a count item (Vehicles, ORV, etc).

This is where I’m stuck. How do set up the tables?

Instead of a field called Site (where I would normally either pick a site or type in the site name), do I list each site as a field and set the property to a number?

This was my first stab, but he didn’t like it. And, truthfully, I don’t either.

Reply | Quote

Well, first an observation about your boss – in Access you are not dealing with a spreadsheet, and making an Access form look like a spreadsheet is nearly always a disaster. If you are going to make it work like a spreadsheet, then don’t move it to Access. One of the powers of Access is to improve the accuracy of data entry, both by using data validation rules (i.e. a date can’t be in the future, and it shouldn’t be many days in the past), and by restricting data to the appropriate entries using a look-up table and a combo box. The data entry form should be designed to minimize the effort of entering data, and maximize the accuracy of the data. Excel doesn’t do a very good job of that without lots of programming and complexity. For example, in the current spreadsheet, you don’t have any way to check and make sure the proper cell got choosen for entering the count.

That said, it is quite easy to turn data in access into a report that looks exactly like a spreadsheet, or to even export data out to Excel in exactly the format the boss wants. And it is straightforward to do averages, sums, distributions and basic statistical analyis, as well as charts showing trends and other demographic data in Access without doing data entry over and over and doing hand calculations. Actually, I think your table design is pretty close to what you need – I would add information about the time a count was taken – you could use the Date field and a Clock/Calendar control to do that. I would also add the name (actually a pointer to a CounterName table) and also record who entered the data, and when. And I would add a field to explain any substantial deviation in counts – i.e. blizzard (I know they don’t happen very often in your area), or a huge heat wave, or….

I’ll give it a bit more thought and see what other improvements could be made.

Reply | Quote

I agree 100%. That’s why I was stumped when Access doesn’t act like Excel. He really doesn’t like the drop downs. He is hung up on having a data entry screen like the spreadsheet. I thought that this was a bad idea, and having you agree gives me the confidence to talk to him again and explain that he can’t have it this way. If you have any other ideas to help compromise with him, I’d appreciate hearing them.

Thanks for your suggestions. I really appreciate it.

Reply | Quote

I talked him into dropdowns! Now I am having issues getting the thing to work so we have 1 form that has the date at the top, subform for the sites for that date, and subform on the sites for the counts at that site that day.

I’m attaching a zip.
I know the relationships are weird. I have been switching them around trying to get it to work. Now I’ve confused myself.
Please, can you take a look at the form called Data Entry. That’s what i want it to look like, but as it is now, I cann’t create new records for new dates. I can create new sites for each day, but not new dates.

Thank you!!

Reply | Quote

I talked him into dropdowns! Now I am having issues getting the thing to work so we have 1 form that has the date at the top, subform for the sites for that date, and subform on the sites for the counts at that site that day.

I’m attaching a zip.
I know the relationships are weird. I have been switching them around trying to get it to work. Now I’ve confused myself.
Please, can you take a look at the form called Data Entry. That’s what i want it to look like, but as it is now, I cann’t create new records for new dates. I can create new sites for each day, but not new dates.

Thank you!!

I have included your database that i have changed in the following:
1. I decided to introduce a table named dataInput in lieu of you table dates. I deleted the Lookup properties of 2 fields, i also changed the field names so i could follow the relationships.
2. Look at the relationships window now, notice the 1 to many relationships.
3. Changed the 1 query you have which i dont think was used.
4. Changed the Data entry form.

Post back if you have any questions.

Reply | Quote

Thank you, Patt. I showed your suggestion to the user and he doesn’t like it. He said data entry will take a very long time with this format. He liked the format I used in the original because he didn’t have to enter the date with every screen and he could pick the site and do that site’s data entry all at one time. I explained to him that it may look fine, but it doesn’t work. Can we take another look at the data entry form I had and see if there is any way to use something like that… or something where the user can enter the date and site and then enter the counts all on one screen?

Thank you so much!

Reply | Quote

This post has been cross posted in another forum , where an alternative solution has been posted.

Reply | Quote

Thank you all for your help. As John posted, I posted this question on another forum where they found the solution I needed. I appreciate the time and thought you gave to helping me.

Reply | Quote