query date range problems

[lisagippe]

I have a query that pulls from 3 tables, tblNames (Name & Supervisor Name) tblPayroll (start & end times) and tblSchedule (scheduled Start & End times).

In the query there is a date range criteria in the date field. The various calculations in the query work fine except it returns 2 records for each date. So for the week of 1/8-1/14 it will have 2-1/8 2-1/9 etc.

There are no multiple records in any of the tables. Any idea what could be causing this? When I link the dates in the query it will only return 1/8's data.

 

[SkipVought]

Hi,

What is the Data Type of the Date/Time fields?

I'm guessing its not Date/Time.

Skip,
[sub]
[red][/red]
[/sub]

 

[lisagippe]

Actually they are both in the Date/Time format - Medium Date in fact.

 

[lespaul]

can you post the SQL of the query?

Leslie

Anything worth doing is a lot more difficult than it's worth - Unknown Induhvidual

Essential reading for database developers:
The Fundamentals of Relational Database Design
Understanding SQL Joins

 

[lisagippe]

here ya go

SELECT [tbl_CSV_name-payroll].Name, [tbl_CSV_name-payroll].Supervisor, tbl_CSV_payroll.Date, tbl_daily_schedule.[Start Time], tbl_CSV_payroll.LogIn, [tbl_daily_schedule]![Start Time]-[tbl_CSV_payroll]![LogIn] AS [Start Variance]
FROM tbl_daily_schedule INNER JOIN (tbl_CSV_payroll INNER JOIN [tbl_CSV_name-payroll] ON tbl_CSV_payroll.Name = [tbl_CSV_name-payroll].Name) ON tbl_daily_schedule.Name = [tbl_CSV_name-payroll].Name
WHERE ((([tbl_CSV_name-payroll].Name)=[Forms]![Schedule Adherence]![cboAgent]) AND ((tbl_CSV_payroll.Date) Between [Forms]![Schedule Adherence]![cboStartDate] And [Forms]![Schedule Adherence]![cboEndDate]));

 

[SkipVought]

Convert the TEXT from your form to a real date...

...
DateValue([Forms]![Schedule Adherence]![cboStartDate]) And DateValue([Forms]![Schedule Adherence]![cboEndDate])));

Skip,
[sub]
[red][/red]
[/sub]

 

[lisagippe]

Skip, tried it still getting duplicate results.

 

[SkipVought]

Sorry, I've been addressing the wrong issue.

Seems like you have a one to many in your join.

FROM        tbl_daily_schedule 
INNER JOIN (tbl_CSV_payroll 
INNER JOIN [tbl_CSV_name-payroll]
   ON tbl_CSV_payroll.Name    = [tbl_CSV_name-payroll].Name)
   ON tbl_daily_schedule.Name = [tbl_CSV_name-payroll].Name

You might try using SELECT DISTINCT...

Again, I'm sorry I took you down a rabbit trail.

Skip,
[sub]
[red][/red]
[/sub]

 

[lisagippe]

Skip your a genious! It worked until I tried to add more calculated fields, then it doubles the returns again...what am I doing wrong?

SELECT DISTINCT [tbl_CSV_name-payroll].Name, [tbl_CSV_name-payroll].Supervisor, tbl_CSV_payroll.Date, tbl_daily_schedule.[Start Time], tbl_CSV_payroll.LogIn, [tbl_daily_schedule]![Start Time]-[tbl_CSV_payroll]![LogIn] AS [Start Variance], tbl_daily_schedule.[Break 1], tbl_CSV_payroll.[Break 1], Abs([tbl_daily_schedule]![Break 1]-[tbl_CSV_payroll]![Break 1]) AS Break1Var, IIf([Break1Var]<0.00417,0,([Break1Var]-0.00417)) AS [Break1%], [tbl_CSV_payroll]![Break 1 End]-[tbl_CSV_payroll]![Break 1] AS [Break1 Dur], tbl_daily_schedule.Lunch, tbl_CSV_payroll.Lunch, Abs([tbl_daily_schedule]![Lunch]-[tbl_CSV_payroll]![Lunch]) AS [Lunch Var], IIf([Lunch Var]<0.00417,0,([Lunch Var]-0.00417)) AS [Lunch%], [tbl_CSV_payroll]![Lunch End]-[tbl_CSV_payroll]![Lunch] AS [Lunch Dur], tbl_daily_schedule.[Break 2], tbl_CSV_payroll.[Break 2], Abs([tbl_daily_schedule]![Break 2]-[tbl_CSV_payroll]![Break 2]) AS Break2Var, IIf([Break2Var]<0.00417,0,([Break2Var]-0.00417)) AS [Break2%], [tbl_CSV_payroll]![Break 2]-[tbl_CSV_payroll]![Break 2 End] AS [Break 2 Dur], tbl_daily_schedule.[End Day], tbl_CSV_payroll.LogOut, Abs([tbl_daily_schedule]![End Day]-[tbl_CSV_payroll]![LogOut]) AS EndVar, IIf([EndVar]<0.00417,0,([EndVar]-0.00417)) AS [End%]

FROM tbl_daily_schedule INNER JOIN (tbl_CSV_payroll INNER JOIN [tbl_CSV_name-payroll] ON tbl_CSV_payroll.Name = [tbl_CSV_name-payroll].Name) ON tbl_daily_schedule.Name = [tbl_CSV_name-payroll].Name
WHERE ((([tbl_CSV_name-payroll].Name)=[Forms]![Schedule Adherence]![cboAgent]) AND ((tbl_CSV_payroll.Date) Between [Forms]![Schedule Adherence]![cboStartDate] And [Forms]![Schedule Adherence]![cboEndDate]));

 

[SkipVought]

In your join, you have a one to many relationship it seems.

you might try aggregating using a MAX or MIN where appropriate.

Also, formatting your query might help you to understand what's happening a little better...

SELECT DISTINCT
  [NAM].Name
, [NAM].Supervisor
, PAY.Date
, SCH.[Start Time]
, PAY.LogIn
, [SCH]![Start Time]-[PAY]![LogIn]                       AS [Start Variance]
, SCH.[Break 1]
, PAY.[Break 1]
, Abs([SCH]![Break 1]-[PAY]![Break 1])                   AS Break1Var
, IIf([Break1Var]<0.00417,0,([Break1Var]-0.00417))       AS [Break1%]
, [PAY]![Break 1 End]-[PAY]![Break 1]                    AS [Break1 Dur]
, SCH.Lunch, PAY.Lunch
, Abs([SCH]![Lunch]-[PAY]![Lunch])                       AS [Lunch Var]
, IIf([Lunch Var]<0.00417,0,([Lunch Var]-0.00417))       AS [Lunch%]
, [PAY]![Lunch End]-[PAY]![Lunch]                        AS [Lunch Dur]
, SCH.[Break 2]
, PAY.[Break 2]
, Abs([SCH]![Break 2]-[PAY]![Break 2])                   AS Break2Var
, IIf([Break2Var]<0.00417,0,([Break2Var]-0.00417))       AS [Break2%]
, [PAY]![Break 2]-[PAY]![Break 2 End]                    AS [Break 2 Dur]
, SCH.[End Day]
, PAY.LogOut
, Abs([SCH]![End Day]-[PAY]![LogOut])                    AS EndVar
, IIf([EndVar]<0.00417,0,([EndVar]-0.00417))             AS [End%]

FROM tbl_daily_schedule           SCH
INNER JOIN (tbl_CSV_payroll       PAY
INNER JOIN [tbl_CSV_name-payroll] NAM
   ON PAY.Name = [NAM].Name)
   ON SCH.Name = [NAM].Name

WHERE ((([NAM].Name)=[Forms]![Schedule Adherence]![cboAgent]) AND ((PAY.Date) Between [Forms]![Schedule Adherence]![cboStartDate] And [Forms]![Schedule Adherence]![cboEndDate]));

Skip,
[sub]
[red][/red]
[/sub]

 

[lisagippe]

Well I now have a more pressing issue to deal with. My queries dont seem to be working, at all. I have not made any changes to tables or their relationships but even when I create a very simple query I am getting no results returned.

At first I though I must have accidentally altered my original query so I went and created another, when that didnt work I created the simple one and still nothing. Any ideas what might cause a db to just not work like that?