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

[raghuprabhu]

Hi All,

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.

Thank you all very much for your help in advance.

Cheers

 

[JamesMcS]

Well, to start with you need common fields in both tables so you can link them together. Then in the query you want to calculate the number of days using datediff, and multiply by rate.

 

[raghuprabhu]

Hi, Please look at the database. The following is the query I tried and is not giving the out put I require.

SELECT tblH42.FullName, tblH42.StartDate, tblH42.EndDate, [EndDate]-[StartDate]+1 AS Days, tblAllDedRate.dailyRate, [Days]*[dailyRate] AS PayOwing
FROM tblAllDedRate, tblH42
WHERE (((tblH42.StartDate)>=[StartDate] And (tblH42.StartDate)<=[cseDate])) OR (((tblH42.EndDate)>=[StartDate] And (tblH42.EndDate)<=[cseDate]));


Any suggestions?
********Correct output needed as follows***********

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 as follows***********

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

 

[JamesMcS]

You're not using the totals function in the query designer. Group by name and see how it changes the query.

 

[raghuprabhu]

Hi All,

Sorted it out. Here is the solution.

Please name the first query “QryInitialQuery”

****************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 qryOutPutRequired*************

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 qryOutPutRequired*************
Cheers