Date Range Problem

[gargara]

Hi guys,
I have some questions about my data base, and I will be very glad if someone can help me.
So the deal is like that – I have a bunch of tables which records information about payments, and the tables are totally the same, but they are recording information about different type of clients. Every payment in the tables has a date, and at the end of the month I have made a date range query which shows me the sum of payments for every client for the date range which I put. So there is no problem with the separated tables, but now I have to make a query that returns the sum of payments for all the customers from the bunch of tables and it has to be date range query too.
The real problem is that every separated table has date fields for the payments, and I don’t know how to combine them in one column so I can use this column for my date range query. I need to build some expression which looks up the values from all the different tables and use that column in my query for my date range(just think this will work) or I need to perform a total change to the data base which I am working on for the last 2 months and consist more then 200 objects L
My query , which returns the sum of payments of all clients looks like this:

TABLES(queries)​

qry SumOfPaymentsBeginners qrySumOfPaymentsAdvanced
FullName FullName
SumOfPaymentsBeginners SumOfPaymentsAdvanced
MaxDate MaxDate

Query​

SumOfPaymentsBeginners / SumOfPaymentsAdvanced / …..and so on…. / Date? /

 

[MSAccessRookie]

Hi guys,
I have some questions about my data base, and I will be very glad if someone can help me.
So the deal is like that – I have a bunch of tables which records information about payments, and the tables are totally the same, but they are recording information about different type of clients. Every payment in the tables has a date, and at the end of the month I have made a date range query which shows me the sum of payments for every client for the date range which I put. So there is no problem with the separated tables, but now I have to make a query that returns the sum of payments for all the customers from the bunch of tables and it has to be date range query too.
The real problem is that every separated table has date fields for the payments, and I don’t know how to combine them in one column so I can use this column for my date range query. I need to build some expression which looks up the values from all the different tables and use that column in my query for my date range(just think this will work) or I need to perform a total change to the data base which I am working on for the last 2 months and consist more then 200 objects L
My query , which returns the sum of payments of all clients looks like this:

TABLES(queries)​

qry SumOfPaymentsBeginners qrySumOfPaymentsAdvanced
FullName FullName
SumOfPaymentsBeginners SumOfPaymentsAdvanced
MaxDate MaxDate

Query​

SumOfPaymentsBeginners / SumOfPaymentsAdvanced / …..and so on…. / Date? /

After I read your post, I was sure that you were easily able to handle any one set of data, since you said that you have "no problem with the separated tables". Instead your issue is that "I don’t know how to combine them in one column".


It would seem that if you "have a bunch of tables which records information about payments, and the tables are totally the same", then the database is not normalized. If the tables are the same, then they could be combined into a single table with two additional columns:

1. AutoNumber for Table Reference
2. Client Type

In order to use the new Table, the Queries and other Code that uses the Table would have to be changed, but this modification would make the result that you want much easier to obtain, and be better in the long run. This is also an efficient approach, since any new Client Type only requires a new entry in the Table, and there would be no need for modification to the Queries involved in this process.

In the event that you do not want to change the code, there is still the possibility of building a Union Query of all results from all tables, and then using that Query as a Sub-Query of a Totals Query that gives you your results. This is not the most efficient approach, since any new Client Type requires a new Table, and therefore also required modification to the Queries involved in this process.

 

[gargara]

Dear Rookie, thanks for the quick and detailed answer. I wish I can put all my tables in one L , but this turn to be impossible , since I have already done that. The design of my data base has to be that way in order that everything can work fine for the future users of it, as my boss want to.
So the deal is that I work in a Students Course Foundation, and we give lessons to students from 1st to 7th grade, as well there is courses for grownups for Microsoft, about 50 of them and there is another very big part of the Foundation which is Center for Professional Training, which is giving another enormous amount of courses to a giant amount of people. So as you see there is about 500 courses, that we gave, so I decided to divide the base to 7 modules in order every module to take care of his clients , payments ,courses and hours taken by the clients. Except that we have teachers , which are another part of the problem , because at the end of the month we have to pay them depending on the hours, and most importantly we have different types of payment politics for the different types of classes , as well as hours ……….. and so on and on….

That is why I already and up with a “Union Query of all results from all tables, and then using that Query as a Sub-Query of a Totals Query that gives you your results” , but this Sub-Query of a Totals Query I need to be Date Range Sub-Query of a Totals Query, for which I need some kind of expression that combines several Date fields from different tables in order that this column of my Sub-Query of a Totals Query to be used for Date range Criteria.

Here is a link to see it: [FONT=&quot]http://img253.imageshack.us/img253/5295/daterange.jpg[/FONT]

 

[MSAccessRookie]

Dear Rookie, thanks for the quick and detailed answer. I wish I can put all my tables in one L , but this turn to be impossible , since I have already done that. The design of my data base has to be that way in order that everything can work fine for the future users of it, as my boss want to.
So the deal is that I work in a Students Course Foundation, and we give lessons to students from 1st to 7th grade, as well there is courses for grownups for Microsoft, about 50 of them and there is another very big part of the Foundation which is Center for Professional Training, which is giving another enormous amount of courses to a giant amount of people. So as you see there is about 500 courses, that we gave, so I decided to divide the base to 7 modules in order every module to take care of his clients , payments ,courses and hours taken by the clients. Except that we have teachers , which are another part of the problem , because at the end of the month we have to pay them depending on the hours, and most importantly we have different types of payment politics for the different types of classes , as well as hours ……….. and so on and on….

That is why I already and up with a “Union Query of all results from all tables, and then using that Query as a Sub-Query of a Totals Query that gives you your results” , but this Sub-Query of a Totals Query I need to be Date Range Sub-Query of a Totals Query, for which I need some kind of expression that combines several Date fields from different tables in order that this column of my Sub-Query of a Totals Query to be used for Date range Criteria.

Here is a link to see it: [FONT=&quot]http://img253.imageshack.us/img253/5295/daterange.jpg[/FONT]

Thanks for the Image. It puts things into better perspective. After viewing the image, I am convinced of the following:

• The Database is NOT normalized, and normalization will make things much easier for use and support.
• The Five tblHours Tables could and should easily be merged into a single table that has an extra Field for theGrade ID.
• The number of the queries, Forms, and Probably Reports as well, could also be reduced by a factor of five, after the above tables have been normalized.

Normalization would not affect the desires of your Management. As a rule, Database Design should not be controlled by Business Managers or Business Analysts. Their most effective rold is to define the Business needs for the APplication. The Program Designer then should build a model that best suits the need. Remember, just because a Database is normalized, does not mean that the Forms and reports cannot look like Spreadsheets.

 

[DCrake]

However to get over your immediate issue as mentioned you will need to create a union query of your five grades tables using Teach and date beginners fields

Select Teacher, DateBeginners, "Table 1" As Source From Table1
Union Select All Teacher, DateBeginners, "Table 2" As Source From Table2
etc

Then introduce this query to you main query and use this as your filter date column

I have added a further field (Source) to the query so you can see that data is coming from all tables.

 

[gargara]

Thank’s again for the answer Rookie.
I know that in first sight the base does not look normalized, but it is not a matter of Grade, that makes the need of several tables, but another problem , and it is the courses. You see , I have already told you that the portfolio of courses that the organization offers is very large , around 500 , and when the administrator needs to appoint new course for a customer he picks up one from a fall down menu in SigningIn Form . So when I combine all the tables in one , then the courses in that fall down menu becomes too many and it is very uncomfortable for the administrator to find the right one , that’s why I divided the Courses in different tables, and the other things too. Now I see that this may not be the smartest thing to do , but I have no time to redo the base again