How to collect data from multiple tables for a given month

[hardhitter06]

Hi All,

Access 2003.

I have 8 tables and 8 corresponding input forms created already for my database.

At the end of each month, I need to total each field in each table for that given month and display it in a report.

For example...Form A feeds Table A and contains Field A. I need to total field A for the month of April and display this total on ReportMain. Form B feeds Table B and contains Field A. I need to total field A for the month of April and display this second total on ReportMain. I have A LOT of fields so there is going to be a lot of calculating (I've never done anything like this before).

So my question is...how will I make this work? I know I am going to have to drive my query by a date search since I am only totaling records for a given month.

With my limited experience, I think I would start by creating an additional form where I have a textbox to enter in the month and date (or maybe 2 text boxes). I would then go into my query and link this text box under each of my date fields for each table?? Then I would use a command button to run the query.

Up to this point, if I ran the search I would receive all the records for a given month. How would I then add them up and display the total? I'm lost right now and don't know which direction to go.

Please help! Thank you

 

[hardhitter06]

This would take a lot of work, but should I have set up the database as one giant table. And given the type of form (because it is specific to the user) i just pick what fields from the table I want that person to be able to input to?

Note: I have about 200 fields in these 8 forms. Edit: Which is why i broke them into several tables.

The reason I am doubting myself is because for each form there is a unique date field...And I'm unsure if that is going to make this search + calculation too difficult..

 

[DCrake]

What are you going to do in 2 years time? have 24 table and 24 forms?

Your system sounds very denormalised. You should only have one table to store the episodic data. You can create queries to differentiate between field contents.

 

[hardhitter06]

I guess thanks for that DCrake? Maybe next time you can avoid being an ass when trying to help someone on the forum.

If I knew what I was doing, I wouldn't be on the forum in the first place.

Here's what I need and maybe someone can shed light on the best possible way to set this up.

I have 4 types of users that will input into this database. For simplicity, we will call them users A, B , C and D.

Each user has their own unique fields to enter. I thought it would be best to split these up into different tables and forms and then run a query search to retrive all records for each table in a given month, total each field and then display them in one report.

Note: None of the users will calculate their fields together (in other words User A fields have nothing to do with user B fields)...I just need to show each tables totals for the month in one place.

Apparently this isn't the best way to do this? So what is?

 

[DCrake]

If you counting apples and pears together you may want to look at union queries. As long at the number of fields are the same and the types of fields are the same then you can use this approach. Are you saying that there is no commonality between each users data. Or are the 4 subsets of data all children of the same parent?

 

[hardhitter06]

It's hard for me to describe since I do not know all of the terminology.

None of the tables have anything to do with each other. I just need to show the totals of the tables all in the same place (one Form/Report).

None of the tables have the same amount of fields and a user may not fill in every field per record but I have all the fields defaulted to 0 (number fields) or $0.00 (currency fields).

So at the end of the month...Table A for Users A will need to be totaled. There may be 5 records within that month.

Table B for users B might have 15 records which need to be totaled and so on and so forth.

So what I'm trying to figure out is:

1. Do I use one query with all the tables and fields grouped together? For each table I have a unique name Date field so I'm not sure how I would go about doing that if that was the case.

2. Once I can figure out how to pull all of the records from each table in a given month search, how do I calculate each field to display the total?

DCrake thank you for being patient and I appreciate your help especially since you're the only one trying to guide me through this.

Please let me know what you think is best and if you want me to post my database so you can get a better understanding of what I'm talking about.

 

[DCrake]

It may be better if you can supply a working example of your mdb.

 

[hardhitter06]

I put a few notes on my dashboard/menu screen. Thank you!

Note: There is no searching in this database other than the monthly report. So input forms and the one search....once I figure out the search and calculation my database will be completed.

 

[DCrake]

I am about to leave the office, however I have had a quick look at it and it seems to me that the tables are in pairs. Don't know why you needed to split them surely the value of the cost would identify the type. Will look further tomorrow.

 

[hardhitter06]

Yeah your right, but sometimes the users will only need to enter data for one or the other so I decided to keep it simple and eliminate both sets of fields on one form/table. I don't give my office too much credit.

Again, even though they are paired...their values have nothing to do with each other, the totals just need to be shown together if that makes sense.

I appreciate it, look forward to hearing from you tomorrow. Enjoy your day.

 

[hardhitter06]

still seeking help...thank you

 

[Subcancel]

Ok.. I just took a look at your DB and DCrake is right your DB is very denormalized. Yet lets try to figure out what you want to do with having to rebuild your tables.

So let me get this right in my head.

You want to pull all 6 tables together in one query. then from that calculate all the currency fields by adding them to a total. Where it is based on a month/Year.

 

[hardhitter06]

I have about 8 tables.

But I want to calculate each currency and number field per table.

Yes, it is based on the month and year so all of March, maybe 5 records were entered, for each field in that table, i need those 5 numbers/values to be added and the total to be displayed...as you can imagine, there is a lot of fields and calculations going on and I've never done anything like this.

Thank you for the response.

 

[highandwild]

At the end of each month, I need to total each field in each table for that given month and display it in a report.

Could you not just have a report containing a number of sub reports and then you would not need to create the one query.

 

[hardhitter06]

I'm not sure how to do something like that?

Could you explain to me the difference between what I was asking for and what you recommended??

Your way might be best, I'm just not familar with what that is/or accomplishes?