Query Question

[Guest]

I have an expense database that includes tables for
Receipts (0001, 0002, 0003), MethodofPayment (Cash, Company Charge,
Mastercard), Mastercard (statementdate), Mastercard details (MC Reference,
date, amount).

I have first run a query to separate only the mastercard entries (ReceiptID,
MethodofPayment=Mastercard).

I have tried to use this query to return a query that has only the receipts
in this query and the MC Reference, but have thus far been unsuccessful.

Can anyone help me?

 

[Guest]

It depends on how you relate your tables - what is the primary key for each
table and what do you use to link to it - e.g. is the "MC Reference" matched
to the receipt number? Please post more details about the tables and their
links.

 

[Guest]

Obviously, this is where I get confused


Receipts - pk ReceiptID
Method of Payment - pk MethodofPaymentID
MasterCard - pk MasterCardID
MasterCard Details - pk MasterCardDetailsID

The first Query tblMaster and tblReceipt and the common field of
MethodofPaymentID and only include rows where the joined filed from both
tables are equal.

When you say "is the 'MC Reference' matched to the receipt number?", in the
tblMastercardDetails, I have included "ReceiptID" and when I try and
construct the query, I have included the tblReceipt with the
tblMastercardDetails and joined the ReceiptID in both tables, but I don't get
the result that I need.

I have tried all three of the different joins to see if I get the
information I need, but none of them seem to work.

I get confused as to where these two tables should have a link. I have
tried using different primary keys to link these tables (i.e.,
methodofpaymentID, receiptID) but nothing seems to work.

What I am trying to accomplish is that I need to account for every
MasterCard receipt and make sure there is a corresponding record on the
mastercard account.

Your help is truly appreciated.

 

[Guest]

Thanks for the extra info.

Let me restate your problem to make sure I have this straight: your Receipts
Table has a field for method of payment, and your are using the Method of
Payment table as a list of choices for that. So you enter all your receipts
and method of payment. Then when you get a Mastercard statement, you enter
the info from that into your Mastercard table with each line item going into
the MastercardDetails Table. So you want to make sure there is a match
between the receipts you entered and the Mastercard statement. And you have
a field for ReceiptID in the MastercardDetails table so you can match it with
your receipts in the Receipts Table.

If that is the case, here is an important question: How does your database
know which receipt goes with which MastercardDetails item? I assume that is
what you are trying to do, but a query cannot match them until the receiptID
gets entered into your MastercardDetails table. So somehow you need to get
that receiptID into your table. How are you doing this? Are you sure you
have them matched? If not, there is nothing for the query to use to make the
join. You would need to set up a way to crossmatch your records first (for
example, make a form with your mastercard statements and use a combobox to
show all your reciepts for mastercard; use the combobox to select the receipt
and use this to fill in the receiptID for the mastercard record - post again
if you need help to set something like that up).

If the receiptID is in there correctly, then joining the MastercardDetails
to the Receipts table by ReceiptID should work, and specfying "All records
from MastercardDetails and only those from Receipts where the joined fields
are equal" should give you all your mastercard records and show you the
receipt info: if there is a receipt missing, the receipt info would be blank
for that record. This would help you find items on your Mastercard list that
do not have receipts - and you could narrow down your query results to only
the unmatched records by setting the criteria for one of the Receipt fields -
one that should always have a value, like Receipt#, to be Null. Showing the
Null records shows where one is missing.

Doing the opposite (All from Receipts and only those from MastercardDetails
where the joined fields are equal) would give the opposite - all your
receipts and where there is a matched Mastercard record the info would show,
if it is missing the receipt would show but the Mastercard info would be
blank. So this (if you set the criteria to show only the ones where payment
type is Mastercard) helps find reciepts that have no corresponding mastercard
record. You can use the same method as above to find records where the
mastercard info is Null.

 

[Guest]

Thank you so much for your time and attention. I realize now that I was
looking for something to do all my work for me without seeing the big
picture. Right now, I have a form with a combo box that lists all of the
receipt numbers. I guess I was looking for an easy way for them to magically
link themselves. Thanks so much!

 

[Guest]

Wish computers could be that smart! The computer does not really know how to
match them up, although you could perhaps have it do more of the work if you
can filter your combobox by date of receipt and amount. You can't guarantee
that there might not be duplicates (same date, same amount on 2 different
purchases) or that there might be inaccuracies (you could enter a receipt
amount wrong, or the date on your receipt might not match the date on the
Mastercard statement if it was processed on a different day) but it would at
least narrow down the choices you need to look through to find the matching
receipt.

To do this you could create a query that selects from your receipts based on
payment method = Mastercard, payment date = date from your form and payment
amount = amount from your form. Use that query as the control source for
your combobox.