VERY basic report question

[therockfrog]

I have been tasked with creating an access program for my fishing company....I know the basics of table creation but have a quick report question.



I have 4 tables.

1. Fisherman information....one record for each fisherman.
2. Fish ticket information....multiple fish tickets for some/all of the fishermen
3. Payment information.....multiple check payments for some/all of the fishermen
4. Expenses. Multiple purchases by the fishermen for gas, food, etc...could be for some/all of the fishermen.


I need to pull all the info from the 2-4 tables into a Settlement report for the fishermen. The info needs to be all together...fish ticket info listed first, then expenses, then payments.

Do I do this by:

A. Creating a big query from all the tables and then create some kind of report from that.
B. Create 4 separate reports from the tables and then make the 3 date tables sub reports and have a bottom line final calculated amount at the bottom.
C. Create a report directly from the fields in all those reports but I cannot figure out all the joins, inners and outers...I tried this way but I could not get the outer joins to work correctly..not sure which tables to join which ways.

I do have a primary key in the main report....that is the fishermen's vendor code. That code is in every other table so they can get linked that way.

I am just so confused as to the best way to create a settlement sheet with info from the 3 data tables.

I have to show everyone at work on Monday how far I've gotten getting this going so I'm a bit stressed right now! Thanks for any help at all!!!!

[ItsMe]

The query comes first. So aim for option A. If you can not create a single query for your report that kinda comes close to your goal, then consider option B.

If you kinda get close to a solution for option A, consider creating additional queries and join said queries to your main query.

[therockfrog]

Ok...I have another question on the overall query. I made a query and all the info comes into the query but when I look at the line items, there will be say, a line for the fish tickets but at the end, for the fields from the payment table, those numbers are duplicated on each line that the fisherman has a fish ticket record. I don't know why I'm having way too many issues figuring this out.....maybe it has to do with the joins......it seems like it should be so simple and I'm sure it is.

[ItsMe]

Sounds like you may be getting a Cartesian effect where multiple instances of a single record are being returned. I did not read the entire reference linked here but I am sure he knows what he is talking about.
http://rogersaccessblog.blogspot.com...ian-joins.html

It may be a fact that your data structure could be better "Normalized". It may be, especially when running reports, this is the only way to get the job done (using a Cartesian Join). You can eliminate the effect by creating a separate query using the Cartesian join. Include as few fields as possible yet, include a field that can be used as a join to your main query.

Turn this separate, small, query into a totals query. Study the results to see if you need to add or subtract fields. The objective is to have the Cartesian join, a field that will work well for a JOIN, and enough fields to work well with the "GROUP BY" statement created by the "Totals" option in the query builder, nothing more.


Probably more than you want to deal with but after you build that first totals query it will begin to make sense.