Merging 2 columns in 2 dif tables; sorted by a shared column

[BexodusMT]

I'm rather new to Access and kind of flying by the seat of my pants. I know how to create very basic update queries, so any answer given I'd LOVE if you could dumb it down a bit. I'm a kinetic learner...

I have 2 tables. InvLocs and Locations...
I need to get the location cells into the InvLocs table... but I need what is filled into the InvLocs to be sorted/filtered/relative to what's in a shared column by both tables.

Locations table goes as such:
Location - location #s (ie. 001.01.00001)
Descr - Common location name

InvLocs table goes as such:
Inv_no - internal #
Location - <column I'm trying to fill)
Descr - Common location name; rows can have duplicate location names.

The reason I need it sorted/filtered/etc... is that Inv Locs can appear like this:

Inv_no Location Descr
1 a-1
2 a-1
3 a-2
4 a-2
5 a-5

Can any one help me through this?

 

[fneily]

Have you read:
Fundamentals of Relational Database Design

http://r937.com/relational.html

Tables must be normalized. You'll have the same data, such as Descr, in two tables.

Not quite sure of your table structures. Is it
tblLocations
LocationID Primary Key
Description

tblInvLoc
Inv_No Primary Key
LocationID Foreign Key
Description

Maybe post your table structures with a clearer explanation.

 

[BexodusMT]

Thank you for the link Fneily. That link explained a bit of gray area to me.

To clarify my tables:

tblLocations
ID Primary Key location specific
LocationID Foreign Key (maybe?)
Description

tblInvLoc
Inv_No Primary Key inventory specific
LocationID Foreign Key (blank but needs locationID from tbllocations here... but relative to the tblInvLoc's Description)
Description

Does that help any?

 

[fneily]

Hmm. Is my thinking correct:
You have a location which can have many inventory items.
AND you have a specific inventory item that can be in many locations.

Example:
Building A has a metal table and chalkboard.

And a metal table can be in many buildings.
A chalkboard can be in many buildings also.

Or am I misunderstanding the situation?

 

[BexodusMT]

Pretty close.

I have a warehouse with multiple bins

there can be multiple inventory items per bin...

This access database is for an import into another program. So I had to format the location entries in Locations a specific way and now i need to tie those entries to bin name (aka LocName and Descr)...

 

[fneily]

So locations are bins.
tblLocations
LocationID Primary Key (001.01.00001, etc.)
DescriptionOfBin

tblInvItems
InvItemID Primary Key
DescriptionOfItem

A third table is needed, called a junction table.
tblLocItems
LIID PrimaryKey
LocationID ForeignKey
InvItemID ForeignKey

So:
tblLocation ---> tblLocItems <---- tblInvItems

Them main tables are connected to the junction table through their primary keys to the corresponding foreign key in the junction table.

So to get the name of a bin and its' items, use the connection shown, in a query. LocationID connects to LocationID, InvItemID connects to InvItemsID. Bring down
DescriptionOfBin, DescriptionOfItem. Run the query and you'll have a list of Bins and their Items.