Query returns two records for each row

[Cloonalt]

I don't know if this is enough information for anybody to help, but I hope so.

I have a query that looks at a date that an employee took as a vacation day. I look at that date and return "vacation year" depending on what begindate and enddate it fell between.

I'm trying to do that with this selection criteria in a query:

VacationYear: IIf([Date_off] Between [vacationstartyear] And [vacationendyear],[vacationyear])

It produces the correct data and an additional blank record for each row. And I don't know why.

I'd really appreciate any help.

 

[mibeach7]

Go to desing view of the the query. Then select the leftmost toolbar icon, next to the save. then drop it down and select SQL view. Copy and past this into this thread so we can analyze it. Otherwise we are just guessing.

 

[Cloonalt]

Here goes.

SELECT tblTransaction.TransactionID, tblTransaction.EmployeeID, PTOType.PTOTypeID, tblEmployee.fname, tblEmployee.lname, tblTransaction.Date_Off, PTOType.PTOType, tblTransaction.Transaction_Date, Format([Date_Off],"yyyy") AS [Year], tblTransaction.Hours_Used, tblTransaction.archived, tblEmployee.StartDate, IIf([Date_off] Between [vacationstartyear] And [vacationendyear],[vacationyears]) AS VacationYear
FROM (PTOType INNER JOIN (tblEmployee INNER JOIN tblTransaction ON tblEmployee.EmployeeID = tblTransaction.EmployeeID) ON PTOType.PTOTypeID = tblTransaction.Transaction_Type) INNER JOIN qryVacationYears ON tblEmployee.EmployeeID = qryVacationYears.EmployeeID
GROUP BY tblTransaction.TransactionID, tblTransaction.EmployeeID, PTOType.PTOTypeID, tblEmployee.fname, tblEmployee.lname, tblTransaction.Date_Off, PTOType.PTOType, tblTransaction.Transaction_Date, Format([Date_Off],"yyyy"), tblTransaction.Hours_Used, tblTransaction.archived, tblEmployee.StartDate, IIf([Date_off] Between [vacationstartyear] And [vacationendyear],[vacationyears])
ORDER BY tblTransaction.TransactionID, tblTransaction.EmployeeID, PTOType.PTOTypeID;

 

[TheAceMan1]

How are ya FSmith37 . . . . .

I see a number of errors on 1st sight. You have two IIf statements that are in complete. The False parts have to be included!

Despite that, I question so many Group By values. I don't see why ya did'nt get more that just the additional blanks.

My best suggestion would be to [blue]repost[/blue] in the [purple]Microsoft: Access Queries and JET SQL Forum[/purple]. The Query/SQL Guru's are there.

Be sure to include your SQL.

See Ya! . . . . . .

 

[Cloonalt]

Thanks, I'll take your suggestion