What type of query do I need for this...

[hrprabhu]

Attached is a small database. There are two tables. How do I design a query to show the output as in the xls spread sheet. If you can not open the spreadsheet please open the txt file.

The actual database contains 160000 lines in "tblH42"

The database in 2003 format.

[MMcCarthy]

Most of the experts won't risk opening an unknown file. To expedite getting an answer to your question can I suggest that you post the meta data of the two tables and a list of the fields you want in the output file along with details of any calculations.

Here is an example of how to post table MetaData :
Table Name=tblBooking s

[i]Field; Type; IndexInfo[/i]
StudentID; Autonumber; PK
Family; String; FK
Name; String
University; String; FK
Mark; Numeric
LastAttendance; Date/Time

[hrprabhu]

What ....cont..

[B]tblH42[/B]
thisTableID1 	Autonumber pk
FullName	Text
StartDate	Date/Time
EndDate		Date/Time

[B]Data in this table[/B]
ID	Full Name		Start Date	End Date
1	Prabhu, Raghu		31-Jul-03	31-Aug-10
2	Smith, John		02-Jun-05	15-Nov-06
3	Harrison, Christopher	01-May-08	11-Nov-09

[B]tblAllDedRate[/B]
thisTableID2	Autonumber pk
commDate	Date/Time
cseDate		Date/Time
DailyRate	Currency

[B]Data in this table[/B]
ID	Comm. Date	Cse. Date	Daily Rate
1	31-Jul-03	05-May-04	$40.00
2	06-May-04	03-Nov-04	$41.00
3	04-Nov-04	01-Jun-05	$42.64
4	02-Jun-05	08-Mar-06	$43.49
5	09-Mar-06	15-Nov-06	$44.14
6	16-Nov-06	08-Aug-07	$45.99
7	09-Aug-07	30-Apr-08	$47.28
8	01-May-08	04-Feb-09	$48.60
9	05-Feb-09	11-Nov-09	$49.96
10	12-Nov-09	31-Aug-10	$51.71

[B]output required[/B]
Full Name		Start Date	End Date	Days	Rate	To Be Paid
Harrison, Christopher	01-May-08	04-Feb-09	280	48.00	$13,440.00
Harrison, Christopher	05-Feb-09	11-Nov-09	280	49.96	$13,988.80
Prabhu, Raghu		31-Jul-03	05-May-04	280	40.00	$11,200.00
Prabhu, Raghu		06-May-04	03-Nov-04	182	41.00	$7,462.00
Prabhu, Raghu		04-Nov-04	01-Jun-05	210	42.64	$8,954.40
Prabhu, Raghu		02-Jun-05	08-Mar-06	280	43.49	$12,177.20
Prabhu, Raghu		09-Mar-06	15-Nov-06	252	44.14	$11,123.28
Prabhu, Raghu		16-Nov-06	08-Aug-07	266	45.99	$12,233.34
Prabhu, Raghu		09-Aug-07	30-Apr-08	266	47.28	$12,576.48
Prabhu, Raghu		01-May-08	04-Feb-09	280	48.60	$13,608.00
Prabhu, Raghu		05-Feb-09	11-Nov-09	280	49.96	$13,988.80
Prabhu, Raghu		12-Nov-09	31-Aug-10	293	51.71	$15,151.03
Smith, John		02-Jun-05	08-Mar-06	280	43.49	$12,177.20
Smith, John		09-Mar-06	15-Nov-06	252	44.14	$11,123.28

Thanks

[captainB]

is this a Microsoft Access 2003 database
also, the first row in the output you provided has a rate that is missing from tblAllDedRate (48.00, should be 48.60?) is that a typo?

[captainB]

I didn't have MS Access installed, so this query is in a mysql database. The SQL is pretty basic, but you may need to check for compatibility.
For instance, the "to_date" function is in mysql only, but there should be something similar in Access, look at this:

http://office.microsoft.com/en-us/access/HP010984861033.aspx

here's the query:

select 
  b.fullname as 'Full Name', 
  a.commDate as 'Start Date', 
  a.cseDate as 'End Date',
  ((to_days(a.cseDate) - to_days(a.commDate))+1) as 'Days',
  a.dailyrate as 'Rate',
  (((to_days(a.cseDate) - to_days(a.commDate))+1) * a.dailyrate) as 'To Be Paid'
  
from tblAllDedRate a, tblH42 b
where a.commDate >= b.startdate 
and a.cseDate <= b.enddate 
order by 1,2

hope this helps!

[MMcCarthy]

The Access sql version of captainB's query is as follows:

select 
  b.fullname as 'Full Name', 
  a.commDate as 'Start Date', 
  a.cseDate as 'End Date',
  DateDiff("d",Format(a.cseDate,"dd mmmm yyyy"),Format(a.commDate,"dd mmmm yyyy"))+1) as 'Days',
  a.dailyrate as 'Rate',
  DateDiff("d",Format(a.cseDate,"dd mmmm yyyy"),Format(a.commDate,"dd mmmm yyyy"))+1) * a.dailyrate) as 'To Be Paid'
 
from tblAllDedRate a, tblH42 b
where a.commDate >= b.startdate 
and a.cseDate <= b.enddate 
order by b.fullname,a.commDate

You should note that the reason we add one to the DateDiff result is that it excludes the start date in the function. The format on the dates is required because otherwise Access can default to US date format.

[hrprabhu]

overlapping period is causing problem

Many thanks to all who replied. To Msquared the problem is as follows....

It is ok if the member has worked in only one date period when the rate does not change. I can get the query which is straight forward

for example
16-Nov-06 08-Aug-07 $45.99
between 16Nov06 and 08Aug07 the rate of pay per day is $45.99

but if the member works over two or more pay rate periods for example

16-Nov-06 08-Aug-07 $45.99
09-Aug-07 30-Apr-08 $47.28

he has to be paid at the rate of $45.99 for the period 16Nov06 and 08Aug07 and $47.28 for the period between 09Aug07 and 30Apr08 I have to have two lines in the out put.

If I do it manually in Excel I have thousands of line I have to copy and paste etc.

Thanks for your help.

I don't have any experience with Mysql.

Cheers

[captainB]

Many thanks to all who replied. To Msquared the problem is as follows....

It is ok if the member has worked in only one date period when the rate does not change. I can get the query which is straight forward

for example
16-Nov-06 08-Aug-07 $45.99
between 16Nov06 and 08Aug07 the rate of pay per day is $45.99

but if the member works over two or more pay rate periods for example

16-Nov-06 08-Aug-07 $45.99
09-Aug-07 30-Apr-08 $47.28

he has to be paid at the rate of $45.99 for the period 16Nov06 and 08Aug07 and $47.28 for the period between 09Aug07 and 30Apr08 I have to have two lines in the out put.

If I do it manually in Excel I have thousands of line I have to copy and paste etc.

Thanks for your help.

I don't have any experience with Mysql.

Cheers

um, am I missing something? I thought the query provided to you in the replies solved the problem.
Did you try it out on your Access database?

[hrprabhu]

Tried..

Hi the following is the query I tried and is not giving the out put I require

SELECT tblH42.FullName , tblH42.StartDat e, tblH42.EndDate, [EndDate]-[StartDate]+1 AS Days, tblAllDedRate.d ailyRate, [Days]*[dailyRate] AS PayOwing
FROM tblAllDedRate, tblH42
WHERE (((tblH42.Start Date)>=[StartDate] And (tblH42.StartDa te)<=[cseDate])) OR (((tblH42.EndDa te)>=[StartDate] And (tblH42.EndDate )<=[cseDate]));


Any suggestions?

********Correct output needed***********
Full Name		Start Date	End Date	Days	Rate	To Be Paid	
Harrison, Christopher	01-May-08	04-Feb-09	280	48.00	$13,440.00
Harrison, Christopher	05-Feb-09	11-Nov-09	280	49.96	$13,988.80
Prabhu, Raghu		31-Jul-03	05-May-04	280	40.00	$11,200.00
Prabhu, Raghu		06-May-04	03-Nov-04	182	41.00	$7,462.00
Prabhu, Raghu		04-Nov-04	01-Jun-05	210	42.64	$8,954.40
Prabhu, Raghu		02-Jun-05	08-Mar-06	280	43.49	$12,177.20
Prabhu, Raghu		09-Mar-06	15-Nov-06	252	44.14	$11,123.28
Prabhu, Raghu		16-Nov-06	08-Aug-07	266	45.99	$12,233.34
Prabhu, Raghu		09-Aug-07	30-Apr-08	266	47.28	$12,576.48
Prabhu, Raghu		01-May-08	04-Feb-09	280	48.60	$13,608.00
Prabhu, Raghu		05-Feb-09	11-Nov-09	280	49.96	$13,988.80
Prabhu, Raghu		12-Nov-09	31-Aug-10	293	51.71	$15,151.03
Smith, John		02-Jun-05	08-Mar-06	280	43.49	$12,177.20
Smith, John		09-Mar-06	15-Nov-06	252	44.14	$11,123.28
Jain, Sanjay		01-Jul-08	04-Feb-09	219	48.60	$10,643.40
Jain, Sanjay		05-Feb-09	30-Jun-09	146	49.96	$7,294.16	

********Getting incorrect output***********

Full Name		Start Date	End Date	Days	Daily Rate	PayOwing
Harrison, Christopher	01-May-08	11-Nov-09	560	$48.60	27216
Harrison, Christopher	01-May-08	11-Nov-09	560	$51.71	28957.6
Harrison, Christopher	01-May-08	11-Nov-09	560	$49.96	27977.6
Jain, Sanjay		01-Jul-08	30-Jun-09	365	$51.71	18874.15
Jain, Sanjay		01-Jul-08	30-Jun-09	365	$48.60	17739
Jain, Sanjay		01-Jul-08	30-Jun-09	365	$49.96	18235.4
Prabhu, Raghu		31-Jul-03	31-Aug-10	2589	$42.64	110394.96
Prabhu, Raghu		31-Jul-03	31-Aug-10	2589	$48.60	125825.4
Prabhu, Raghu		31-Jul-03	31-Aug-10	2589	$51.71	133877.19
Prabhu, Raghu		31-Jul-03	31-Aug-10	2589	$44.14	114278.46
Prabhu, Raghu		31-Jul-03	31-Aug-10	2589	$41.00	106149
Prabhu, Raghu		31-Jul-03	31-Aug-10	2589	$43.49	112595.61
Prabhu, Raghu		31-Jul-03	31-Aug-10	2589	$40.00	103560
Prabhu, Raghu		31-Jul-03	31-Aug-10	2589	$47.28	122407.92
Prabhu, Raghu		31-Jul-03	31-Aug-10	2589	$45.99	119068.11
Prabhu, Raghu		31-Jul-03	31-Aug-10	2589	$49.96	129346.44
Smith, John		02-Jun-05	15-Nov-06	532	$43.49	23136.68
Smith, John		02-Jun-05	15-Nov-06	532	$44.14	23482.48
Smith, John		02-Jun-05	15-Nov-06	532	$45.99	24466.68
Smith, John		02-Jun-05	15-Nov-06	532	$48.60	25855.2
Smith, John		02-Jun-05	15-Nov-06	532	$49.96	26578.72
Smith, John		02-Jun-05	15-Nov-06	532	$51.71	27509.72
Smith, John		02-Jun-05	15-Nov-06	532	$47.28	25152.96

[MMcCarthy]

Can you run the following query (slight variation on CaptainB's) without making any changes and tell me what results you get. As I mentioned in previous post you have to impose format on dates in Access. I suspect your query is reading the dates in US format.

SELECT b.fullname as 'Full Name', a.commDate as 'Start Date', a.cseDate as 'End Date', 
DateDiff("d",Format(b.EndDate,"dd mmmm yyyy"),Format(b.StartDate,"dd mmmm yyyy"))+1) as 'Days', 
a.dailyrate as 'Rate', 
DateDiff("d",Format(b.EndDate,"dd mmmm yyyy"),Format(b.StartDate,"dd mmmm yyyy"))+1) * a.dailyrate) as 'PayOwing'
FROM tblAllDedRate a, tblH42 b 
WHERE a.commDate >= b.startdate
AND a.cseDate <= b.enddate  
ORDER BY b.fullname,a.commDate, a,dailyrate

[hrprabhu]

Have been able to sort out this problem

Please name the first query “QryInitialQuer y”

*************** *code for QryInitialQuery *************

SELECT IIf([commDate]<[tblH42.StartDate] And [EndDate]<[cseDate],[commDate],[tblH42.StartDate]) AS sdate, tblH42.FullName, tblH42.StartDate, tblH42.EndDate, tblAllDedRate.commDate, tblAllDedRate.cseDate, IIf([sdate]>[cseDate],1,IIf([commDate]>[EndDate],2,3)) AS Flag, tblAllDedRate.dailyRate, IIf([sdate]>[commDate],[sdate],[commDate]) AS s2dte, IIf([cseDate]<[EndDate] Or [cseDate]=[EndDate],[cseDate],[EndDate]) AS edate
FROM tblH42, tblAllDedRate;

*************** *code for QryInitialQuery *************

*************** *code for qryOutPutRequir ed*************

SELECT QryInitialQuery.s2dte, IIf([cseDate]<[EndDate] Or [cseDate]=[EndDate],[cseDate],[EndDate]) AS edate, QryInitialQuery.FullName, QryInitialQuery.dailyRate, [edate]-[s2dte]+1 AS Days, [Days]*[dailyRate] AS AmountPaid
FROM QryInitialQuery
WHERE (((QryInitialQuery.Flag)=3));

*************** *code for qryOutPutRequir ed*************