Relationships between Tables

[hjs980]

I'm having a problem with the relationships between the tables in my
database. Table 1 contains records of inventory being removed (gear, size,
date, removed by), table 2 contains the cost of each type of inventory we
carry (gear, size, cost). I want to be able to look up the cost for each type
and size of gear. (When gear1=gear2 and size1=size2, report cost2).

I am trying to create a summary report that sorts the removals by gear and
size, then looks up the cost. My report is finding the information from Table
1 correctly, but the information from Table 2 isn't linking the size. I am
getting records repeated for each size in Table 2. For example I want it to
read Belt - Large - Dec. 3 - Tom - $34 (cost of large best) and I am getting
Belt - Large - Dec. 3 - Tom - $30 (cost of small belt) then Belt - Large -
Dec. 3 - Tom - $32 (cost of medium belt) and so on.

I could use any suggestions on how my relationships should look or how I can
query the single cost I need in each group. I'm very new to databases, so
please be kind.

Thank you.

 

[John Spencer]

It appears that you only have the relationship set up based on gear.

If you are using the query design view,
Drag from Gear to Gear to get one join line and then Drag from Size to Size to
get a second join line.

That may fix the problem. If that doesn't work, switch to SQL view and copy
the SQL statement and post it.

John Spencer
Access MVP 2002-2005, 2007-2008
The Hilltop Institute
University of Maryland Baltimore County

 

[Jeff Boyce]

Post the SQL statement of your query.

Regards

Jeff Boyce
Microsoft Office/Access MVP