Question How do I do this in Access..too complicated for a better title!!

[CarysW]

After getting this far in Excel, see post here: http://www.access-programmers.co.uk/forums/showthread.php?t=178846

I now have
Key Item
1010 1
1010 5
1010 7
1010 9


and also another worksheet with:
Key(for Access) Key(store)
12 1010
13 1010
14 1010


What I need is:
Key(a) Key(s) Item
12 1010 1
12 1010 5
12 1010 7
12 1010 9
13 1010 1
13 1010 5
13 1010 7
13 1010 9
14 1010 1
14 1010 5
14 1010 7
14 1010 9

But for a couple of thousand Key(a)s

Does that make any sense to anyone?

 

[Atomic Shrimp]

Does the key change to anything other than 1010 further down in the data? (if so, I assume you want to use it as a key - if not, you don't need it at all and can just do a cartesian join).

Assuming you want to join on the key...

• Import the sheets as tables in Access
• Create a query including both tables
• Click and drag the key field from one table and drop it on top of the corresponding field in the other table. (A line representing the join will appear)
• Double click the field names you want to see in your query results
• Run the query

it really should be that simple.

 

[CarysW]

Does the key change to anything other than 1010 further down in the data? (if so, I assume you want to use it as a key - if not, you don't need it at all and can just do a cartesian join).

Assuming you want to join on the key...

• Import the sheets as tables in Access
• Create a query including both tables
• Click and drag the key field from one table and drop it on top of the corresponding field in the other table. (A line representing the join will appear)
• Double click the field names you want to see in your query results
• Run the query

it really should be that simple.

The 1010 type key does change(I have about 900 of them!), but for the Junction table I am trying to create(the end goal) it is not needed, but it is needed in order to know which stores the Access key(12,13,14 type numbers) belong to for the moment. One I have assigned the brands(item keys) to each Access key I wont need to store key but I'm guessing Access will need the store key to know which brand keys to put next to which Access keys......confused yet? I am!

 

[CarysW]

Does the key change to anything other than 1010 further down in the data? (if so, I assume you want to use it as a key - if not, you don't need it at all and can just do a cartesian join).

Assuming you want to join on the key...

• Import the sheets as tables in Access
• Create a query including both tables
• Click and drag the key field from one table and drop it on top of the corresponding field in the other table. (A line representing the join will appear)
• Double click the field names you want to see in your query results
• Run the query

it really should be that simple.

I LOVE YOU!!!!

Thankyou!