Multiple tables, multiple forms and unique data = problems!

[pedroak]

I'm by no means an Access expert, so I warn you in advance if my question provokes a plethora of others from yourselves... here I go:

I am trying to represent a spreadsheet in MS Access 2000, in order to better order/report/query the data etc.

The spreadsheet has multiple different types of data, but it all relates to a budget for ONE activity.

I have created multiple tables to represent each type of cost related to the activity.

I wanted to create a user friendly form to enter the data, so ran a query into several of the tables and then created a form from that data - HOWEVER, there were so many fields I couldn't get all of them on one form.... So I created another query/form/etc...

The problem I have is: one "set" of data (for each activity/job) is made up of information from all of the tables that is entered by the user and doesn't relate to another job/activity...

I now have problems with relationships accross multiple forms too.. I thought I could create one Autonumber unique field in one table and then relate it 1:1 with all the other Unique ID fields set to "number" in the other tables. I was hoping that every time a new record was started, it would create a new entry for all of the other tables... The problem being that I have added an extra twist of having multiple forms...

Can I link this much data together, while binding each record in each table to each other in a unique way and then use a form to input it all?!

Make sense? I hope it does to somebody!

Thanks in advance..

Pete

 

[boblarson]

It makes sense to me what you did, but it doesn't make sense to do so in a relational database system. Excel is not Access and Access is not Excel. They have two totally different ways that you have to think.

I think the first thing up is for you to take a look at how to normalize your data. Then you can continue on. But, if you don't understand that, then we'll just be spinning our wheels in trying to get this straightened out.

See here for a couple of good tutorials on normalization:
http://www.accessmvp.com/strive4peace/Access_Basics_Crystal_080113_Chapter_03.pdf
http://support.microsoft.com/default.aspx/kb/283878

 

[pedroak]

Thanks Bob.

There is some very useful information in that article. Has made me "go back to the drawing board". My problem I can see now is I was trying to build an Access database in an Excel frame of mind... obvious really, just requires thinking a bit more laterally...

Thanks for the links

Pete

 

[DevastatioN]

One additional thing, if you find that you can't fit all the controls on a single form, you can use a "Tab Control" on the form. The form would then have many "tabs" that can be switched back and forth, with different controls on each.

You can even do the groupings in the tabs much like you would group things in Excel, but as Bob said it should be normalized properly first.