Frustrated

[skydiver]

Access 2010. I've re-written a database in version 2010 to mimic the old 2003 version, but this time I've normalized it. I have a total of 12 tables (so far). I'm using one form for all 12 tables. I have a one-to-one relationship from one table to the other eleven. I've input dummy data and everything looks beautiful.

I've copied the new database (we'll call it "Main") and renamed it "Main1". So, now there are two exact databases w/different names. I wanted to separate them in order to import data into one and be on the safe side. Okay, I've managed to successfully import data from the 2003 version (via excel) into the 2010 "Main1" database. All tables, field names, data types, and data line up perfectly. In my "Main" database w/the dummy data...I can see the related data, however in the "Main1" database I don't see the relationships working like they should. What am I missing???

:banghead:

 

[MarkK]

If you're using one form for all 12 tables then I doubt you have your database normalized. If you have 12 tables that contain the same type of data, you can see that you'll need to define 11 more relationships than you normally would for each other single table in your DB.

What are the names and structures of your 12 tables?

 

[DavidAtWork]

"I have a one-to-one relationship from one table to the other eleven"
Does that imply that you have one data table and 11 tables acting as Lookups, if so how have you designed your lookup tables, does the PK data type match the FK's in the main table
David

 

[skydiver]

Let me see if I can break this down as easy as possible. Currently, we have 3 ferry companies sailing throughout the year (all sail more than once a day throughout most of the year). One boat can have many sailings, on these sailings we categorize passengers, vehicles, and any special processes one can fall into. According to our procedures, passengers fall into 1 of 4 categories (i.e. A, B, C, or D). In addition to falling into 1 of 4 categories, any one passenger may/may not go through a special process. There are 10 special processes a passenger can undergo (all separate tables). So, in a nutshell...I have a Sailings table (parent table) connected to 11 other child tables. I'm using a primary key in my sailings table that corresponds as the foreign key in the other tables. Is this a bad setup? How would you arrange this? Thanks for your patience as I'm new to relationships.

 

[MarkK]

You want to avoid a case where you have tables like ProposedOrder, PendingOrder, CurrentOrder, FilledOrder, ProblemOrder. What you want is one Order table that has a status field.

Do you have 11 tables with the exact same structure that differ by status, or differ by only one or two dimensions?

 

[DavidAtWork]

"I'm using a primary key in my sailings table that corresponds as the foreign key in the other tables. Is this a bad setup?"
If as I suspect the child tables are only acting as lookups, then the PK in each lookup table should link to the FK in the parent table. Your parent table design would be like:
sailingID, autonumber PK
sailing_desc, text 100
vehicle_type, text 100, FK to vehicle types table
ferry_company, Long, FK to ferry companies table
passenger_type, text 5, FK to passenger type table
spec_process, int, FK to special process table
plus all other fields

David

 

[skydiver]

Thank you for responding. All of my tables (with the exception of a few other fields) are designed to collect numbers (statistics).

Lagbolt - all of my tables basicly have the same structure and are set up to collect a number, but are categorized by different situations.

David - Can you elaborate on "lookup tables?" I might be getting confused with terminology here (i.e. "Lookup Wizard" in Data Type). I'm chewing on your idea of your parent table setup. Remember, there are 11 different processes a passenger can undergo. It's possible that one passenger can undergo a variety of processes during the same sailing. This is where I have separated each process (different tables) that collect a number.

Thanks for your patience!

 

[StarGrabber]

Surely not exactly what you are looking for... but just a suggestion.

From my point of view you should put as much as possible into "tblSailingProcessPassanger" and not in 11 separate tables.

 

[skydiver]

Interesting setup StarGrabber. Let me study this a bit. I appreciate your response and time. Thank you!