Calculate total from two tables

[jmhicsupt]

I have two tables and I would like to calculate the total amount of sales from one of the table.

In MainTable:
Customer (maintable.customer)

In SubTable:
Customer (subtable.customer)
Product (subtable.product)

In my query, I am limiting the products to "oranges" and "apples". However I would like the total of all of the products in one of the fields so I can see how much the customer is spending overall.

This query is the recordsource for a report, so my query has "Oranges" or "Apples" as the criteria because that is all that I want displayed in my report. But I would like to also include the total value of the customer.

Is there a way to do this?

Thanks in advance.

 

[fneily]

You have a many-to-many relationship between customers and products. So:
You will have three tables. tblCustomer, tblProduct, tblTransaction. The tblTransaction is called a "junction" table. It will look like this:
TransactionID
CustomerID
ProductID
DateOfTransaction
Amount

tblProduct will have
ProductID
ProductName
Cost

Then is a query, you will connect tblCustomer to tblTransaction through CustomerID, connect tblProduct to tblTransaction through ProductID.
In your query grid, you'll bring down CustomerName, and ProductName. You'll have your test for Apple or Orange under ProductName.
You'll create another field called TotalCost and place the expression [Amount]*[Cost]. So TotalCost:[Amount]*[Cost]
Then click on the Totals button to add another row called Total. You'll have GroupBy for CustomerName and ProductName and under Totalcost change it to sum. Run the query and you'll have your totals for each customer by product.

You must understand Normalization and relationships to create Relational databases such as Access.

 

[fneily]

Just reread your question. Let me do a slight change. Create the query as stated but do not click the Total button. So your query will have CustomerName, ProductName and a new field TotalCost:[Amount]*[Cost] with Apple or Orange as criteria for ProductName.
Now create a Crosstab Query following the wizard. In the first screen, select queries and select the above query. In the next screen for row headings select CustomerName. The next screen for column headings select ProductName and finally select TotalCost and Sum. Run the query and you'll get a nice comparison of each customer, their individual product total and an overall total for each customer.

 

[jmhicsupt]

Thanks for the quick response. I was not totally clear. In my report, I only want to display the products apples and oranges. However, the customer may also have grapes and plums. So I want to get the total value of the customer in the query.

Apples = $1.00
Oranges = $1.00
Grapes = $1.00
Plums = $1.00

Query would show

Products
Apples
Oranges
Total value of customer = $4.00

Hope this makes sense.

Thanks again.

 

[fneily]

What you want is more confusing then the original question. However, again create a query as before but without any criteria. So you have CustomerName, ProductName and TotalCost. TotalCost is again [Amount]*[Cost].

Using the report wizard, use this query as the source. The wizard will ask if you want any grouping. Select CustomerName. Then sort on ProductName and click the summary button to select Sum for TotalCost. Finish the wizard. You should see a report Grouped on Customer then their products with their TotalCost and a sum for each Customer and also a Grand Total for All customers.

Now switch to Design view. Bring up the property sheet box. Click on the DETAIL bar. Click on the Event tab of the property sheet. Click the box next to OnFormat. Click the button with the three dots and select Code Builder. Copy the following:

Private Sub Detail_Format(Cancel As Integer, FormatCount As Integer)
If (Me![ProductID] = "Orange") Or (Me![ProductID] = "Apple") Then
Me![ProductID].Visible = True
Me![TotalCost].Visible = True
Else
Me![ProductID].Visible = False
Me![TotalCost].Visible = False
End If
End Sub

Substitute your field names, obviously.
Now your report will show each customer, the products Orange and Apples and their totals and a sum the shows the Overall Total for each customer. Just like what you asked for.