Return values from two tables

[hunterfan48]

I've posted my relationship screen so you can see what I'm working with.

I want to return the price from my transactions table for the value '1' in the ID field in this transactions table.

That would be my first field in the query. The next one would be to do a sum of the sellprice field in my 'inventory' table where the buyingID matches a value of '1'.

Any ideas on how to accomplish this? So far, I'm not getting the query to return anything on the relationship.

Thanks!

 

[hunterfan48]

Any ideas?

 

[Old Man Devin]

Should be fairly easy. Make a query with Transactions and Inventory as the tables. Have your 'price' field from transactions be one field, and the selling price from inventory be the other. Then activate 'totals' in the ribbon. New options for the query fields appear. You can then set the price to be 'group by' and the selling price to be 'sum'.

Then if you only want to see it where buying id=1 just add buyingid as another field in the query (possibly with 'Show' not ticked' and in the critiera box write "=1" (without quotes).

 

[hunterfan48]

When I do the query design and add my two tables, it automatically pulls over my relationships that I've shown in my original post. Why are no values returned when I leave the relationships there?

 

[Old Man Devin]

When I do the query design and add my two tables, it automatically pulls over my relationships that I've shown in my original post. Why are no values returned when I leave the relationships there?

This would suggest that there aren't any corresponding records between the two tables, e.g. no transactions exist have have the inventory ID of any of your current inventory. So I guess you could check to make sure that the tables actually have some kind of shared information (called a 'Foreign Key') so that the relationship can be properly used.