1 Form linking to multiple tables

[jwright77]

Hello all,

I need some help, I haven't used Access for about 13 years...and I have a new task at hand. My work asked me to design a Db and I am already lost.

The question:
I have an Excel Spread sheet that data is hand written on, then the data is going to be typed into Access. So I created a Form that looks just like the excel spread sheet to make it easier to enter the data.



I attached a SS of the Access showing the form and the tables I created. Basically I wanted to make it easier to run reports, so I created a Table for each location. (CCHD, GCHD, PCHD ect..) And under each table I have FPAppointments, FPVisits ect.. and I wanted to be able to enter data into the Form row by row, just like an excel spread sheet and then to update records in the db.(Sorry its office 2013 preview, might look a little different)


I was also told that you can only use 1 table per form? If that isn't true, how can I get each field in the form to recognize it's respective table?

Thanks for the help!

[June7]

Don't use multiple identical tables. Use one table with a field for the location code. Apply filter criteria for reports.

[jwright77]

But I want to be able to enter data in all the fields at one time.

[June7]

Don't understand. Why could you not enter data into all fields for a single table?

Want to provide db for analysis? Follow instructions at bottom of my post.

[ssanfu]

Welcome to the forums.

First thing you must understand is that Access is not Excel. There might be a form set up as a datagrid that LOOKs like a spreadsheet, but it is NOT a spreadsheet.

I am stealing a post from Rod from 8/8/2012:

Rule zero!

"The time spent analysing and planning your project before you even 'put finger to keyboard' is repaid fourfold later in the development."

So, what should be done in this planning stage?

Define what your project should do. This is usually a list of the main reports and their content. Sometimes projects do things other than report so define these if appropriate.
Define your first thoughts on how the information is to be entered, by whom, how frequently.
Is this envisaged as a single-user project (you can have multiple stand-alone copies) or is it a multi-user project with shared data?
Analyse your data. If you are inexperienced or if the project is large then I would recommend producing an Entity Relationship Diagram (ERD) before attempting to design a relational database. The ERD will help you further understand your data.
Only then produce a first draft database design - either on paper or use Access to draw it nicely. Test the design by walking through 1, 2 and 3 above to answer how the design fulfils the requirements.
Now you're ready to start.




PS Read up on and adopt a naming convention (suggest Reddick) and a set of standards (not familiar with any published ones - just follow what you like of other people's work) from the start.

Read up on normalization. There are many sites that will help. YouTube has some videos.
Here is one by Crystal: http://www.accessmvp.com/Strive4Peace/

Be aware of reserved words: http://allenbrowne.com/AppIssueBadWord.html

"The Ten Commandments of Access" : http://access.mvps.org/access/tencommandments.htm
"The Evils of Lookup Fields in Tables": http://access.mvps.org/access/lookupfields.htm

Instead of a table for each site, I would start out with:

table name: SITES
fieldName         type
SitesID            Autonumber   PK
SiteName         Text

table name: Main  (or whatever you want to call it)
fieldName         type
MainID             Automunber  PK
SitesID_FK       Number - Long    (Link to table SITES)
EntryDate        Date/Time
FPAppointed     Number - Integer
FPVisits           Number - Integer
STDAppointed  Number - Integer
STDVisits         Number - Integer
ImmuAppointed  Number - Integer
ImmuKept         Number - Integer
TBKept            Number - Integer
VitalRec          Number - Integer (? not sure of the data type)

[jwright77]

Thanks for the quick replies. And yes when I get a chance here in a little bit I will upload the db. I know it's not a spread sheet, but there is no way to make it act like one to a certain point? Add data hit tab go to the next field add data tab ect.. Then in the end click a button and all of the data gets stored in it's perspective tables?

I just need to make it simple and easy.

[June7]

That could be done with unbound form and code to save each 'row' to appropriate table but why? With a single table and bound form data entry can be done without code.

I took another look at the image and guess really no point to upload that db. If the eight location tables are identical in field structure, then should be one table.

[ssanfu]

Looking at it in a different manner: with 8 tables you will have 8 forms for data entry with 8 form queries, 8 reports with 8 report queries and a couple of union queries (not update-able) for reports to get totals across the 8 tables. A nightmare to maintain....

With one main table, ....well, you get the idea.

I know it's not a spread sheet, but there is no way to make it act like one to a certain point? Add data hit tab go to the next field add data tab ect.. Then in the end click a button and all of the data gets stored in it's perspective tables?

You can make Access act like a spread sheet to a certain point. In the image you posted, if the fields were in one table, you could enter the data and hit tab to move to the next control, no problem. But if there were 8 tables, you would need unbound controls and VBA code. I once helped someone in a winery set up an unbound form (approx 160 unbound controls on one form) for data entry. Even with looping, there was a lot of code.

[June7]

Yes, 8 of everything or even more code to manage 1 query, 1 form, 1 report to serve 8 tables. It can be done but I bet you have better things to do with your time than learn how to do something the worst way.

[jwright77]

Thanks all for the help. So I will create one large table and give each it's own specific identifying name. ie CCHDFPAppointed, SCHDFPAppointed ect..

Thanks again for all your help!

[jwright77]

Ok that worked great, I have the form working correctly now and one single table with all the data I need. Thanks!

[June7]

What do you mean by 'give each it's own specific identifying name'? Each field? You have fields called CCHDFPAppointed, SCHDFPAppointed, etc? That is not what we suggested. The suggestion is for a field called LocationCode and the values will be CCHDFP, SCHDFP, etc. Further normalization could also mean a field called AppointmentType with values of STD, FP, Immu, etc.

[orange]

As others have tried, I recommend you research Normalization. To make a database simple and easy, you have to design it - and the key to that is normalization.
Also, you should read this article on Data Base Principles
http://forums.aspfree.com/attachment...achmentid=4712