Query tables w/ same key but different detail info.

[uivandal]

I am trying to create a query that joins information from two different tables that have the same primary key but have different detail information. I'm sure it is some easy answer but I'm not getting it....
The tables contain different sales information but I want to join them to create billings for buyers and receipts for the customers. (Basically an auction type scenerio.)

Table 1 = "tblBlockSale" and contains the following fields:

BuyerID
CustomerID
Tag#
Weight
BidAmount
TotalAmount

Table 2 = "tblAddOnDonations" and contains the following fields:

BuyerID
CustomerID
AddOnDonationAmount

I want the query to show all records in Table 1 and all records in Table 2
and to be able to report all the records from Table 1 (if any) and all of the records from Table 2 (if any) by Buyer and show an overall total at the bottom. I want to also be able to do the same for the customer.

Thank you for your help. I really appreciate this site and all of the help it has provided!

I have another question and I don't know how to create a new post so I'm going to ask it here....sorry if this is bad posting etiquette...

FLEXIBLE FORMS QUESTION:

I am trying to set up a flexible form and not hard coding information.
I want to be able to allow the user to enter fee information:
tblFees

Fee1 Fee1Description Fee1Amt
Fee2 Fee2Description Fee2Amt
etc. and they can continue to add as many fees as they need.

Then I need to allow them to say how these fees are applied:

SaleItemType1 might need Fee1, Fee2, Fee3 and no other fees
SaleItemType2 might need all of the fees
SaleItemType3 might just need Fee1
etc. for as many SaleItemTypes they may have.

So after applying the fees as they have noted generically for the SaleItems then they may need to go to specific record and make changes just for that record. So for example, Record1 might be a SaleItemType3 that was generically given Fee1 but this specific record needs Fee1 and Fee3. Record25 might be a SaleItem2 that generically got all of the fees but for this record, they want to remove Fee4.

Hope that make sense and that someone can help me. And again, sorry for the posting of two different questions in one post....

UIVandalPatti

 

[PNGBill]

Dealing with the query question.
What is the Unique Record Field for tblBlockSale ?? Tag# ??

Why do you have BuyerID and CustomerID ?? arn't they the same??

Will any records in tblAddOnDonations be the same record as tblBlockSale or are they all unique. ie 500 records in one table and 250 in the other and you want a result containing 750 records ??

If this is the case a simple way to achieve this is to use a Union Query.
But, I note you want a Total for Buyer and a Total for Customer yet the table holds both ??

You often have a Transaction Table that will hold all sales records and this would just have say ContactID instead of BuyerID and CustomerID.

In another Table you would have all your contacts and one field would either have the word Buyer or Customer.

Your query could then total all the activities and produce a total for all contactsID's that were Buyers and all that were Customers.

You may find your database has a few fundamental issues to resolve.

 

[uivandal]

Dear PNGBill

Thanks for replying.
1. Yes...tag# is the unique key for tblBlockSale
2. The customer is selling the Tag# item and a Buyer is buying it.
3. The records in tblAddOnDonations and tblBlockSale are unique and I want the scenerio you described (500 + 250 = 750) and then I want to report by buyer with totals and I want to report by customer (or seller) with totals. (I want to bill the buyer for their total block purchases and/or add-on donation amounts they made and I want to send a report and check to the customer (seller) for their items purchased and/or any add-on amount the buyer wants to donate).

So I believe I need to look at a Union Query....? I will look it up but in the meantime, in case I can't figure it out, could you give me a Union Query scenario for my situation?

Thank you so much. Also, any thoughts on my other question would be greatly appreciated. When I send the customer their check, I need to deduct fees and wanted to set it up to be flexible.

 

[PNGBill]

Try this:

SELECT BuyerID, CustomerID, Tag#, 0 AS AddOnDonationAmount
FROM tblBlockSale
UNION ALL
Select BuyerID, CustomerID, 0 AS Tag#, AddOnDonationAmount
From tblAddOnDonations;

This is just like adding two sql's together. Important issue is that All Fields must be the same which is why in the first part we have included AddOnDonationAmount but set the value as zero and in the 2nd part Tag# is included but again, the value is zero. You may be able to set this to Null by just having nothing before the "As" - try it and see.

The Union Query will get your Combined Records dataset.

From this you can have other queries or Reports using Grouping and Sorting to get the Totals you seek.

 

[uivandal]

Thank you! That worked! You're my hero for the week!

Do you have any ideas on my other question?

Should I create another post for that question?

Thank you for your help (everyone!)....this is a great site.

UIVandal

 

[PNGBill]

On The Fees Issue you should have some Set Fee names which may or may not have set amounts or set percentages.
in theory, even if you were to have hundreds of Fee options, they should be identified first.
If this issue is still work in progress then just be prepared to make changes as you develop your database.

You could have a Command Button on your Sales form that will allow the Operator to Add Fees.
If you have a way to identify, for each product, the maximum fee possibilities, then they could all be displayed and a Command Button or Check box is clicked against each Fee to set if it is charged or not.

When completed another Button could bring up a Form that shows the Fees Charged with details and again, a command Button or Check Box will allow a fee to be Edited or Deleted.

Apart from a Magic Wand, it is really up to you to nut out your fees as best you can and then try and ask a specific question on say:
Table Structure of your Fees - Separate Fees Table etc.
Help with the Forms and any Code and Buttons to make it happen.

This issue could be posted in Tables, Queries, Forms and vba depending on your question.
Sometimes questions and answers spill over but best to try and get the best suited Forum first.

Trust this assists