Access Table Configuration

Question

I'm fairly new to access and I need some insight on the way I'm setting up two tables. I am in the business of tracking equipment procurements. I have one table that has the main unit, quantity, make/model information along with some other purchasing details. We have unique numbers associated with every main unit. So say we want to order a T.V. that number is and always will be 12345. Well, now we are required to track accessories individually and I am thinking it would be best to have a separate Accessories table. The problem I'm running into though is since there can be different numbers of accessories and different quantities it's throwing my numbers off. When I run a query or report it shows multiple records for the main unit.

I want the query to list the rows separately like this.

 ID    Desc    Qty    Type
12345   TV      3   Main Unit 
12345   Cable   3   Accessory 
12345   Mount   2   Accessory

Instead it lists the first line two times so it looks like there is a total of 6 units.

12345   TV   3   Accessory   Cable   3 
12345   TV   3   Accessory   Mount   2

It might be a simple fix and I’m just having a brain fart or maybe I should put the accessories in the main table, I'm not sure. Any advice/insight would be greatly appreciated.

Can you edit your post to add the field names to the sample data? I'm sure it's just a join issue, but I can't tell what you're joining wrong without knowing the field names. — Johnny Bones, Oct 22 '13 at 19:22
Taking what knowledge I have of the A/V field, I know that not every accessory is exclusive to every TV. Also, every TV has more than one accessory, so you actually have a Many-to-Many relationship working here. Please edit your post and add the SQL of what you've tried to it. I still can't figure out how you got your first sample dataset, so I can't help you get to your desired dataset. — Johnny Bones, Oct 23 '13 at 15:14

duffymo · Answer 1 · 2013-10-22T17:50:39.440

0

If there are many accessories for every unit in the main table, you'll need a many-to-one relationship between the two.

Add a foreign key column to the accessory table and set it to the primary key of the item in the main table that it belongs to.

create table main (
    id identity, 
    primary key(id)
);

create table accessory (
    id identity,
    parent_id int,
    primary key(id),
    foreign key(parent_id) references main
);

So now you create rows in main, each with their own primary key id. Let's call them main(id). You'll have associated accesory rows with primary and foreign key values. Let's call them accessory(id, parent_id).

INSERT row into main gives you main(1).

INSERT three rows into accessory that are associated with that first row in main gives you accessory(1, 1), accessory(2, 1), accessory(3, 1).

Bring them together using a JOIN:

select *
from accessory
join main
on accessory.parent_id = main.id

I think you'll have a problem with Access because it's designed for non-technical people who don't know relational databases or SQL well. If you're using the UI to create tables you'll have to add the association foreign key properly or it won't work.

edited Oct 22 '13 at 17:50

answered Oct 22 '13 at 16:00

duffymo

305,152
44
369
561

I tried doing that, but it keeps saying indeterminate. Is that because both of the tables have autonumbers as the primary key? – user2891566 Oct 22 '13 at 16:07
No, it suggests that you have primary keys on both, as you should, but you didn't create the foreign key column on accessories and populate it with the main primary key value. I call that foreign key column parent_id in the example. There'd also be an id primary key on accessory. – duffymo Oct 22 '13 at 16:20
I have the main id number in both tables, but since there is a possiblity that the main id can be in there multiple times it will not let me assign it as the primary key. – user2891566 Oct 22 '13 at 17:03
The main id is NOT a primary key in the accessory table, only in main. You don't understand SQL or tables well. – duffymo Oct 22 '13 at 17:14
I apologize ahead of time for asking silly questions, but how can the main id be a primary key if there are duplicates? The t.v. (12345) will be ordered more than one time therefore, they will be duplicated in the main table. Access will not let me create the one-to-many relationship. – user2891566 Oct 22 '13 at 17:25
Main id should be unique in the main table. Accesory id is unique in accessory table, but parent id foreign key is not. See explanation above. – duffymo Oct 22 '13 at 17:48

score 0 · Accepted Answer · answered Oct 23 '13 at 15:22

As I stated above, you're actually dealing with a Many-to-Many relationship here. As a result, you're going to need a "crosswalk" table. Here's how you would set it up:

1) TV table will have TVID (Primary key, unique), TV Name, Quantity, Manufacturer, etc...

2) Accessory table will have AccessoryID (Primary key, unique), Accessory Name, Quantity, Manufacturer, etc...

3) Crosswalk table will have TVID and AccessoryID with no Primary key. You enter in every TVID and AccessoryID combination you can possibly have in here. Example:

TVID         AccessoryID
12345         666
12345         777
12345         888
55555         777
55555         888

Say 12345 is TV1 and 55555 is TV 2. Now you know TV1 can use Accessory numbers 666, 777 and 888, while TV2 can use Accessory numbers 777 and 888.

One-To-Many from the TV table to the Crosswalk table. One-to-Many from the Accessory table to the Crosswalk table.

Boo-YAH! That should solve your problems. If you can't figure out how to get your queries working after that, let me know and I'll see if I can guide you the rest of the way.

Access Table Configuration

2 Answers2