How to join tables in Ma access

[gerryis2000]

Hi, i have two tables that i wish to join. from the items table and purchases table, i use a query to list all the purchases for the day. then using sum i get all the amount used to purchase.my problem lies with getting a report that show purchases list with item name on each row. e.g 'cocaloa with its totals' and 'fanta' with its totals on the second row of the report.so how do i use sql to join cocacola and fanta total-purchases tables?

[patjones]

Can you provide more information about each of the tables? I'm thinking table names, as well as the names of the important columns, and maybe a few lines of sample data from each table. Thanks.

Pat

[gerryis2000]

Thanks for your concern, i really apreciate. here are the tables.one is 'Fanta-openingstock' and the other is 'Coke-openingstock'. all this tables were created from aquery called purchases. fields in 'Fanta table' are 1.[Item ID] 2.Name 3. [units-purchased] 4. [purchase price].
and 'coke-openingstock' has the folloing, 1.[Item ID ]2.[Name ]3.[Units-purchased] 4. [Purchase price]. i want after joining the two tables, opening stock for each should appear like SUM of '[units-purchased]*[purchase price]' as [opening stock]. i kindly hope u will understand my concept though i,m really a newbie in these. regards.

[FishVal]

1. Do you mean table join or table union?
2. Why do you have two separate tables?

[patjones]

As FishVal says, because these two tables have exactly the same data, they can be combined into one table.

As for the SUM that you want to do, are you saying that you want a single sum for Fanta, and a single sum for Coke? This is a fairly straightforward matter of calculating [Units-purchased]*[Purchase price] for each record, then summing over each record for Fanta and Coke respectively. This can be done nicely with SQL once we clarify the situation with your tables as FishVal suggests.

Pat

[FishVal]

Just to add to what Pat has said:

This combined table should have an additional field indicating whether a record is for Coke or Fanta. This field should be a FK of a master table. To get an idea of how data should be stored in relational database please read Database Normalization and Table Structures article.

[gerryis2000]

Thanks again fishval and Pat. this is the cenario here, the two tables mentioned are made out of queries not real tables. otherwise cocacola and fanta belong to products table. what i want to achieve at the end of the day is totals for each product sold. below in the first sample is the query result for daily sales details. the second sample is where i was trieng to sum [units sold]*[unit sale price] to get totals but it results in error message. kindly assist. regards gerry.


SELECT [DAILY SALES].[ITEM ID], ITEMS.Name, [DAILY SALES].[UNITS SOLD], [DAILY SALES].[SALE PRICE], [DAILY SALES].[SOLD BY], [DAILY SALES].[DATE OF SALE], [DAILY SALES].[REVC ID]
FROM [DAILY SALES] INNER JOIN ITEMS ON [DAILY SALES].[ITEM ID]=ITEMS.[Item ID]
WHERE ((([DAILY SALES].[ITEM ID]) Like [ENTER ITEM CODE:] & "*") AND (([DAILY SALES].[DATE OF SALE]) Like [ENTER SALE DATE:] & "*"));




SELECT [DAILY SALES].[ITEM ID], ITEMS.Name, [DAILY SALES].[UNITS SOLD], [DAILY SALES].[SALE PRICE], [DAILY SALES].[SOLD BY], [DAILY SALES].[DATE OF SALE], [DAILY SALES].[REVC ID], SUM([UNITS SOLD]*[DAILY SALES].[SALE PRICE])
FROM [DAILY SALES] INNER JOIN ITEMS ON [DAILY SALES].[ITEM ID] = ITEMS.[Item ID]
WHERE ((([DAILY SALES].[ITEM ID]) Like [ENTER ITEM CODE:] & "*") AND (([DAILY SALES].[DATE OF SALE]) Like [ENTER SALE DATE:] & "*"));

[patjones]

So, to be clear, the first one runs, but the second one errors out?

I'm also still not clear on exactly what you're looking for. Do you just want a total for each item, or a total broken down by date, or a total broken down by who made the sales?

In the most general terms, it looks like you will want to use a GROUP BY in this query in order to get the proper sums that you are looking for. But I would need you to be more specific about what you want before I could advise you. Perhaps you can post a little sample of the sort of output you want...

Pat