You are here:

Using MS Access/RE: Connecting multiple transactions to one receipt

Advertisement


Question
QUESTION: Hi Julie,

I have created a database form that has a receipt number, categories(rental property) combobox then I have it synchronized with a subcategories(location) combobox depending on which category you choose. Each grouping (categories and subcategories) has an amount field. The user can enter up to four transactions on one form and I have a field called total that calculates  each amount to get the sum. My question is how do I document each transaction on one receipt in a table, so for reporting the user can document who and how customers are renting a certain property and how much they are profiting for each property being rented?

ANSWER: Hi Brittany,

Many thanks for the question.

Is the form and combo boxes linked to a table or are they unbound?  

If they are unbound, i.e. does not link at all to a table, have you done too much work, to create a table, with fields to store the values of the combo boxes and recreate your form to link to this table?

If you could let me know re the above questions, I will be in a better position to help you.




---------- FOLLOW-UP ----------

QUESTION: RE: Connecting multiple transactions to one receipt

Hi Julie,

Thank you for the response.

Yes my form, frmReceiptEntry, is bound to a table called tblReceipt. The the fields are:

Date_Updated
ReceiptNumber-primary key

CategoryName
SubCategoryName
Amount

CategoryName2
SubCategoryName2
Amount2

CategoryName3
SubCategoryName3
Amount3

CategoryName3
SubCategoryName3
Amount3

CategoryName4
SubCategoryName4
Amount4

Payee
Total
Payment
Check #
AmountPaid
Change Due
Employee Initials


The data does save to my table under the receipt number as one record, but I would like it to show each transaction individually. For example: if someone rented 3 places(categories) the receipt number would show up four times under my table like:
receipt number 41
CategoryName
SubCategoryName
Amount
Payee
Payment
Check #
Remarks
Total
Employee Initials
Amount Paid

receipt number 41
CategoryName2
SubCategoryName2
Amount2
Payee
Payment
Check #
Remarks
Total
Employee Initials
Amount Paid

receipt number 41
CategoryName3
SubCategoryName3
Amount3
Payee
Payment
Check #
Remarks
Total
Employee Initials
Amount Paid

My purpose is to create reports to track how many people are renting a certain category or how much each category is receiving during the month or who is renting what category etc.

I hope this kind of explains it in more detail.

Thank you, Brittany

Answer
Hi Brittany,

What you need to create is a query called a Union Select Query.

To do this:

Create a normal query, with the fields:

receipt number 41
CategoryName
SubCategoryName
Amount
Payee
Payment
Check #
Remarks
Total
Employee Initials
Amount Paid

Then change from Design View to SQL view (In Access 2007 and later, Design tab > View > SQL View)

The code will look something like this

Select tblReceipt.ID, tblReceipt.Category, tblReceipt.SubCategoryName etc
FROM tblReceipt

Copy and paste, these two rows 2 more times, and add in Union to the beginning of the Select Statement, and change the SubCategoryName to SubCategory2 etc., and remove the semi colon, on all except for the last one.

Your SQL should now look like this:

Select tblReceipt.ID, tblReceipt.Category, tblReceipt.SubCategoryName etc
FROM tblReceipt

Union Select tblReceipt.ID, tblReceipt.Category, tblReceipt.SubCategoryName2 etc
FROM tblReceipt

Union Select tblReceipt.ID, tblReceipt.Category, tblReceipt.SubCategoryName3 etc
FROM tblReceipt;

When this query is run, your information should appear as you need.

Hopefully I have understood your question. Good Luck  

Using MS Access

All Answers


Answers by Expert:


Ask Experts

Volunteer


Julie Misson

Expertise

My area of expertise is Microsoft Access 2000-2007. I have been building microsoft databases for the past twelve years. I would be competent in answering questions from novice Access 2000 users to the more advanced including VBA. If I cannot help, I am more than likely be able to point you to websites that can.

Experience

I have a Microsoft Access business where I build database for small businesses. These are usually one off designs, where there is no off the shelf software avaliable to meet the business needs. I also teach Microsft Access to beginners. I am the owner of the www.simply-access.com website.

Education/Credentials
Self taught in Microsoft Access. Have done some units in normalisation and SQL, but most of what I know I have learnt from books originally and more recently the Internet.

©2016 About.com. All rights reserved.