Multiple Queries 1 report Query Calculationis (1 Viewer)

[JahJr]

I have a db with just 1 table. The Headings in the table are as follows:
ID
Business Date
Food Sales
Liquor Sales
Beer Sales
Daiquiri Sales
Wine Sales
T-Shirt Sales
Tax
Comp
Disc
Visa
American Express
Discover
Master Card
Cash Paid Out
Cash Paid In
Total Cash
Donation
Gift Certificates
Open Drawer
Close Drawer
Cash Tips
Credit Card Tips Taken In
Net Sales Entered
Miscellaneous Hold Back
Number of Bartenders
Bartender 1
Bartender 2
Bartender 3
Bartender 4
Bartender 5
Open
Close
Bartender 1 Tips
Bartender 2 Tips
Bartender 3 Tips
Bartender 4 Tips
Bartender 5 Tips
Cook Tips
Item Discounts

I have a form where the user can select a start date and an end date. This form will run a Query and open a report that will show the totals for these columns. Snipit of the SQL for that Query

SELECT Sum([Funkys Sales].[Food Sales]) AS [Total Food Sales], Sum([Funkys Sales].[Liquor Sales]) AS [Total Liquor Sales], Sum([Funkys Sales].[Beer Sales]) AS [Total Beer Sales], Sum([Funkys Sales].[Daiquiri Sales]) AS [Total Daiquiri Sales], Sum([Funkys Sales].[Wine Sales]) AS [Total Wine Sales], Sum([Funkys Sales].[T-Shirt Sales]) AS [Total T-Shirt Sales], Sum([Funkys Sales].Tax) AS [Total Tax], Sum([Funkys Sales].Comp) AS [Total Comp]
FROM [Funkys Sales]
WHERE ((([Funkys Sales].[Business Date]) Between [Forms]![BetweenDates].[startDate] And [Forms]![BetweenDates].[endDate]));

I need help with how to add the following calculations to the query or should i just use another query. I could see how using another query would be easy but I cant figure out how to have 1 report that gets data from 2 different querries. The report is built and I really dont want to have to redo it.

Calculations that I need help with

1. ((Close Drawer-Open Drawer)-(Donation+Credit Card Tips Taken in)) = Total Net Cash
2. (Total Total Cash - Total Net Cash) = Over Short
3. ((Food Sales+Liquor Sales+Beer Sales+Daiquiri Sales+Wine Sales+T-Shirt Sales)-(Comp+Disc+Visa+American Express+Discover+Master Card+Cash Paid Out-Cash Paid In+ Over Short+Donation+Gift Certificates))=Proof
4.((Food Sales+Liquor Sales+Beer Sales+Daiquiri Sales+Wine Sales+T-Shirt Sales)-(Comp+Disc))*.09= Sales Tax
5.(Tax-Sales Tax)=Tax Proof
6.(+Over Short+Tax Proof)=Cash Over Short
7. (Total Net Cash *-1) = -Total Net Cash

 

[Alansidman]

Looking at your table layout, it appears that you have a normalization issue. Once you have your table normalized, your queries will become easier to manage and the results you are looking for easier to create. Your current layout resembles more of a spreadsheet mindset versus a RDBMS. They are not the same.

Suggest you read this on database design and normalization. It is about a 15 minute or less read.

http://www.deeptraining.com/litwin/dbdesign/FundamentalsOfRelationalDatabaseDesign.aspx

 

[JahJr]

I read the article and it makes since. I could see how I could split this into 3 tables with an additional look-up table. I can't manage to wrap my head around how to break it down past that point. Like the article said it is more "Art" and I must say I am not the most creative person.

Currently I keep all of this data in a excel spread sheet. At the end of the night my manager goes to our POS system and prints a daily sales sheet. He then opens up a excel sheet and keys in the numbers from the POS system and then punches in his data: Open Amount, Close Amount, Cash Tips, who opened, Who closed, who worked, and how much they each made individually in tips. At the end he clicks email and the sheet comes to me. I then go into the POS system and take all of the sales numbers: Food Sales, Liquor Sales, Beer Sales, Tax Collected, Visa Charges, AmEx Charges, Comp .... and key them in to my spread sheet along with the data from the spread sheet that was emailed to me. My current workbook has a sheet for each month and a reports sheet at the end.

For the DB I could see having the Following Tables
tblPOS
tblOpenCloseInfo
tblBartenders ---Lookuptable with Employee information
tblBartenderTips

tblPOS
Date
Food
Liquor
Beer
Daiquiri
Wine
T-Shirts
Tax
Comp
Disc
Visa
Amex
Discover
MasterCard
Cash PO
Cash PI
Total Cash
Donation
Gift Certificates
Credit Card Tips

tblOpenCloseInfo
Date
OpenDrawer
CloseDrawer
CashTips
Open Bartender
Close Bartender
Number of Bartenders



tblBartenders
ID
Bartender 1
Bartender 2
Bartender 3

tblBartenderTips
Date
ID
Amount

Hopefully this is enough info for someone to point me in a better direction for the normalization. I'm also thinking about using the Date as the Primary Key? What are you thoughts on this?

Thank you in advance

 

[JahJr]

Moved discussion to tables:
http://www.access-programmers.co.uk/forums/showthread.php?p=1228328#post1228328