Need to summarize categories of table...even null values

[Jester00130]

Fairly new at this compared to you guys...but here goes. Have database built which tracks hours and expenses. Tables are in place. Also even built Form for easier data entry. Problem is in creating a report I need to report for each separate project every category of hours types and expense types showing how much was billed (DSum?)for the month under each separate category....catch is every category must appear under each project heading even if no work was done that month under a given category...it must show up anyway and report "0" total for that category. For example the report must have group RCA Building Project, and below it must show eg...Category Installed Windows $200.00 (say this is the total for say 2 separate window installs) then the next category might be Install Doors (total for install doors may be zero because none billed that month) must show up anyway and read as "0". If I did no work in a given month all categories must still appear in the report and have a "0" totally listed. In the reports I've been able to set up if there was no billing for a given category for that month the category doesn't even show up. Any suggestions greatly appreciated. I'd even pay for an hour or two of someones time to look at my database and build the report...the database is small and only few fake records in it now. Anybody...I'm so close I think to being set up and I'm stalled dead in the water.

 

[aleb]

Post db
Free of charge

 

[Jester00130]

Really could use the help...thanks

Need report showing and summing every work category under every project....showing all work and expense categories even if no work done or expense paid under all the categories...must show "0"s where no work under a given category or expense under a given expense category...preferably with a date parameter on report so can report by month....
Know you ya said you'd do it free... Thanks...but if ya help me I'll send ya some cash...money order to ya...just to say thanks...I really got in over my head here...

 

[Jester00130]

Example

Report By Project Category Charges Summaries

Bill Cycle: 01/01/2004 to 01/31/2004

Project: Jenson Home Lot #23

Plumbing $ 0.00
Carpentry $100.00
Masonary $ 0.00
Electrical $ 75.50
Plumbing Supplies $ 0.00
Carpentry Supplies $ 0.00
Masonary Supplies $ 125.50
(as above for each category of billing category table...hope example helps. I know I'm not good at explaining this)

Total this Project: $301.00

Must reset page count following end of project so next project begins as page one again...bills will sometimes go to different contractors et cetera so each project must start at page one

 

[Ukraine82]

Try using the crosstab query. Take a look at a link I posted about crosstab query. I would like to help you out, but I can't open any databases right now.

http://www.access-programmers.co.uk/forums/showthread.php?t=72821

hth,

Michael

 

[aleb]

Try this one.
I could not force to change the numbering of the pages after every project.
Also I am not sure how you make your calculation... of the total billed amount I mean you have a number of fields like tax ... do you include it in the calcs? So I didn't make any assumption and just totaled the "Slip total" field.

As u can see there is a way of playing with the data. Run the output1 query. It will give you the pivot table view. It works just like in excell, you can play with it to get accustomed. That is where you can also view the details by different period types ( like weeks, month day etc ) and display only needed information unlike the report which is set. Let me know if that is what you needed or you need anything else.
It is trully free of charge

 

[Mile-O]

Why are you storing the Biller's full name as a field when Forename and Surname are, for database design, more appropriate. And, since you have the biller's full name then you have no need to store the initials. Also, there is no table for the Biller Status meaning you are repeating records of unnecessary text and, your primary keys leave a lot to be desired as - the more records you get - the slower your database will become as it tries to index fields with text values.

You contractors table has a first name field (where's the consistency) but no salutation field. Why not create salutation field instead of adding Mr. to the contact's first name.

 

[Jester00130]

Thanks for the start. Now just need totals on each category.

Report lists every category even if no record listed in group for the category. So thanks. Still need a total for each category...maybe after it lists each category. Any ideas to help there and the form will be great. Anyway thank you...You've been alot of help.

Michael