Report in Access

[acura123]

Casino Gambling Net Activities

For the Year Ended 2023

Jan

Feb

Mar

Apr

May

Jun

Jul

Aug

Sep

Oct

Nov

Dec

Grand Total

Bally

(1,000.00)​

(1,000.00)​

Borgata

-​

-​

Caesars

-​

-​

Hardrock

(5,000.00)​

(7,000.00)​

(12,000.00)​

Harrahs

-​

-​

Ocean

(3,000.00)​

(2,000.00)​

(5,000.00)​

Resorts

-​

-​

Tropicanna

-​

-​

-​

-​

Total Win/Loss

(9,000.00)​

(9,000.00)​

(18,000.00)​

-​

Trip Expense

-​

No Trips

8​

7​

4​

8​

6​

5​

8​

7​

4​

3​

8​

8​

76.00​

Net Trip Expense

(528.00)​

(462.00)​

(264.00)​

(528.00)​

(396.00)​

(330.00)​

(528.00)​

(462.00)​

(264.00)​

(198.00)​

(528.00)​

(528.00)​

(5,016.00)​

-​

Amenities

-​

Food

623.00​

623.00​

Cashback

400.00​

400.00​

Shows

-​

-​

Gifts

60.00​

60.00​

Clubs

2,000.00​

2,000.00​

Rooms

-​

Events

-​

Net Total

3,083.00​

3,083.00​

-​

Grand Total

(6,445.00)​

(9,462.00)​

(264.00)​

(528.00)​

(396.00)​

(330.00)​

(528.00)​

(462.00)​

(264.00)​

(198.00)​

(528.00)​

(528.00)​

(19,933.00)​

I'm trying to make a report in access that shows my total Monthly trip activity from the Casinos. I think I want to make a cross tab query, but it only holds 3 field items. I want the report to look like above? How do I do this? Do I use multiple crosstab sub reports?? I'm stumped

 

[Micron]

You can base a report on a crosstab query but you have to manually define every field in the query properties. However, this is not something you do if you're adding something like months on a regular basis (12 months/columns/fields today, 13 next month, 14 after that and so on). CT queries for reports are for when you can have anything from (e.g.) one to 10 columns/fields, but not always all of them. So you define all 10 in the properties and it works even when some of them lack data. If you don't do that, then when you create the query/report and the next time you run it a query field is missing data, it fails.

What you show is your desired output and not the source data, yes? So I suspect your underlying issue is that you designed your tables the same way people design spreadsheets, and that is always a bad thing. If that is the case, you will continue to have such problems. You might want to research db normalization to see if you can figure out if your tables design is correct. Or maybe post a sample of table data.

 

[acura123]

I have one table that has all my trip data. Casino, W/L, Etc, All the fields listed above. I have another table with the names of the clubs for a dropdown list within the form. All of the info is in the Casino trips table. Not sure how to get this into a report like shown above? Am I overcomplicating the problem?

 

[Micron]

I have one table that has all my trip data. Casino, W/L, Etc, All the fields listed above.

Then you have under-simplified it - or to put it another way, it is as I've said. Your design was made with a spreadsheet approach, which is just wrong.

You can research solutions for reports based on crosstab queries for this issue, and see how far any solution gets you. I think not far, especially if you want to add columns like months on an ongoing basis.

 

[acura123]

May I ask what would be the correct way to display this info in access?

 

[Micron]

as per my previous post

You might want to research db normalization to see if you can figure out if your tables design is correct.

Here's a start, but it is just for normalization and doesn't include info on a host of other topics that make for good db design. If you want those I can post them if you're going to stick with Access for the job at hand.
Normalization Parts I, II, III, IV, and V

What Is Normalization, Part I

Thoughts, opinions, samples, tips, and tricks about Microsoft Access

rogersaccessblog.blogspot.com

and/or

Database Normalization – Holowczak.com Tutorials

holowczak.com

 

[acura123]

as per my previous post

Here's a start, but it is just for normalization and doesn't include info on a host of other topics that make for good db design. If you want those I can post them if you're going to stick with Access for the job at hand.
Normalization Parts I, II, III, IV, and V

What Is Normalization, Part I

Thoughts, opinions, samples, tips, and tricks about Microsoft Access

rogersaccessblog.blogspot.com

and/or

Database Normalization – Holowczak.com Tutorials

holowczak.com

You are correct my data is not normalized. I'm thinking I need the following tables. One Listing the clubs, one for the comp categories, One for the Trip visits with visit date, casino and w/l. I'm not sure how to setup the tables? Any recommendations? I don't think I'm suppose to have a redundant Visit date in multiple tables correct?

 

[Micron]

Your suggestion was pretty close I'd say. Based on your data example I think maybe like this:

tblCasinos (one record per casino)
CasinoIDpk - autonumber
CasinoName
Address, etc.

tblVisits (one record per visit)
VisitIDpk - autonumber
CasinoIDfk - long, CasinoIDpk value from tblCasinos
VistDate
etc.

tblExpenseType
ExpenseIDpk (autonumber)
ExpType (record for each type e.g. food, room...)
ExpDesc (optional description field)

tblVisitExp (this is a junction table, which you didn't suggest but answers your valid question). So 1 record for each expense for a visit.
VisitExpID - autonumber
VisitIDfk - long, VisitIDpk value from tblVisits
ExpenseIDfk - long, ExpenseIDpk value from tblExpenseType

The junction table will have a lot of records, but that's the way this sort of many to many relationships are done. Worry about the volume records when it gets to one or two million. Word of advice - do not store images in tables and maybe look at the rest of these:

Naming conventions
- General: Commonly used naming conventions
- MS Access Naming Conventions

What not to use in names
- Microsoft Access tips: Problem names and reserved words in Access

About Auto Numbers
- General: Use Autonumbers properly

The evils of lookup fields - The Access Web - The Evils of Lookup Fields in Tables
Table and PK design tips - Microsoft Access Tables, Primary Key Tips and Techniques
About calculated table fields - http://allenbrowne.com/casu-14.html
About Multi Value Fields - https://www.isladogs.co.uk/multivalued-fields/index.html

Last/First/DLast/DFirst Explained
- https://codekabinett.com/rdumps.php?Lang=2&targetDoc=dfirst-dlast-sorted-result-set

 

[Micron]

One of the most important concepts to understand is that a table is about an entity, the fields are about its attributes. If something in a table does not apply to/describe the "thing" it does not belong there.