How to restrict cartesian product query results

[bd1048]

Hello All,

This is my first posting in this forum. So look forward to benefit from your knowledge.

What I am trying to do is:

I have two tables, 1. tblDates and 2. tblRetes

Now tblDates fields as below,

ID Dates
1 01/01/2015
2 02/01/2015
3 03/01/2015 .... all the dates in the world.

and tblRates fields as below,

ID EmpCode EffictiveDate PayRate
1 001 02/01/2015 $25.00
2 001 05/01/2015 $30.00
3 001 09/01/2015 $35.00 ..... etc

Now I am trying to create a query that will give me results like below

ID EmpCode Dates PayRate
1 001 02/01/2015 $25.00
2 001 03/01/2015 $25.00
3 001 04/01/2015 $25.00
4 001 05/01/2015 $30.00
5 001 06/01/2015 $30.00
6 001 07/01/2015 $30.00
7 001 08/01/2015 $30.00
8 001 09/01/2015 $35.00
9 001 10/01/2015 $35.00



Now I can create a Cartesian product to give me all the combination but not sure how to restrict these to get what I want.

My ultimate goal is to create a query where I can get the daily hours from a table and multiply with rate for that day to determine a daily cost for a particular employee to get the total cost for any given period.

I am open to any suggestions to get to what I what and does not have to be this way. Please note I have worked with Excel VBA but near zero knowledge in access VBA.

Thanks in Advance
MH

[orange]

Why would you need a table of All DATES? As long as you have the EffectiveDate and Rate for Employees, you should be able to get the info you seek.

The logic would be a long these lines

Given Empcode X and a DayInvolved,
you are trying to find the Rate for that EmpCode on that day.

You have the tblRates with EmpCode, EffectiveDate and Rate

So, you want the Rate from tblRate record for Empcode X that is the
Maximum(EffectiveDate) less than the DateInvolved.


The EmpCode's rate will be the same between effectiveDates, and changes on the next effectiveDate.

I don't see the purpose, nor need, for the tblDates.

[bd1048]

Thanks Orange for your reply. I like your idea and as I said I am open to any idea that will work.

I get what you are saying but how do I go about doing this? could you please elaborate a bit more. (e.g are we talking about creating a query? write up codes? etc.)

[CJ_London]

this sounds like homework - this thread is asking pretty much the same thing and the answer is the same

https://www.accessforums.net/queries...ery-53657.html

[orange]

I agree with Ajax -sounds like homework.
Show us some of your work, and ask specific questions if you need help.
Don't overlook Google/bing to help find information.

To prevent Cartesian Product you have to have a relationship between your tables.

[bd1048]

Thank you Ajax and Orange. Actually this is not a homework for me. I work as an accountant and a beginner access user. There are different reports I need to do on excel that takes ages and accuracy is hard to achieve too sometimes. That's why I am trying to move away form excel for some of my reporting that involves large amount of data to make life easy.

I can see straight away how easy it is in access to do this particular task. what would have taken me hours in excel and with lot of complicated formulas and macros can be done in access with some very simple codes/querys (of course if I know them!!!).

I was trying to use Cartisian Product because one of the video used it to expand date ranges to individual dates. I don't know if this is the best way to do it.

I have followed Ajax's instruction and found exactly what I was looking for. I am excited and will do a bit more experiment with my project and let you know where I am at. And next time I will try to upload db if I can figure out how to upload.

[orange]

Regarding the info in post #2.
The importance of records in the tblRates is that only change or milestone events are recorded.

If Whett Willy has a rate of $25.00 on Jan 20/2015, you don't have to have a record for Jan 23, or Mar 7, you use the current or latest rate until the rate changes. So if Whett gets a raise to rate $32.50 on June 15, 2015, you would use his latest rate for calculations beginning Jun 15, 2015.

You would use some code and/or queries to do the calculations.

Database and table design is an important first step to get a structure that supports your business requirements.
It is important to determine and record the logic of the calculations etc that you will be using. The next step is to translate that logic into coding constructs.

See this post for some links to additional information.