Strange results

[Shanin]

I keep getting variable results from the following query.


SELECT tktimcrd.employee, Trim([name]) & ", " & Trim([first_name]) AS
EmployeeName, (Sum(IIf([pay_type] Not In ("P","S","Y","Z"),[hours]))) AS
Regular, (Sum(IIf([pay_type] In ("P","Y"),[hours]))) AS Personal,
(Sum(IIf([pay_type] In ("S","Z"),[hours]))) AS Sick, Sum(tktimcrd.hours) AS
SumOfhours
FROM py_emplo INNER JOIN tktimcrd ON py_emplo.employee = tktimcrd.employee
WHERE (((tktimcrd.work_date) Between [Start Date] And [End Date]))
GROUP BY tktimcrd.employee, Trim([name]) & ", " & Trim([first_name])
WITH OWNERACCESS OPTION;


The results never seem to be the same. Sometimes it'll add a few hours to
one employee, the next time to someone else, and then sometimes it will just
mix names, for example, say I have a steve smith and a john doe, everynow and
then it will give some hours to an employee named steve doe. I'm not sure if
this has to do since it's pulling the data from a foxpro database and the
field name for last name is "name" which if I'm remembering is a reserved
word in Access. Any help would be appreciated.

 

[Jerry Whittle]

Is either py_emplo.employee or tktimcrd.employee the primary key fields for
their tables? If not you could have a many-to-many join.

I noticed that there isn't an ORDER BY clause. Could it be that the records
aren't sorted the same way each time and you are seeing different records?

Is there different pay types than "P","S","Y","Z" ?

Speaking of which, is pay_type null in any records?

Does tktimcrd.work_date have times with the dates? If so you might be
missing data from the [End Date]. Try this to see:

SELECT tktimcrd.work_date,
Format(tktimcrd.work_date, "mm/dd/yyyy hh:nn") As WithTime
FROM tktimcrd
WHERE tktimcrd.work_date <> CLng(tktimcrd.work_date) ;

 

[Klatuu]

Light. Strong. Cheap. Pick two.

For bicycles, I will select 1 and 2
For women, it would be 1 and 3

Now I need to go pray.
--
Dave Hargis, Microsoft Access MVP

Is either py_emplo.employee or tktimcrd.employee the primary key fields for
their tables? If not you could have a many-to-many join.

I noticed that there isn't an ORDER BY clause. Could it be that the records
aren't sorted the same way each time and you are seeing different records?

Is there different pay types than "P","S","Y","Z" ?

Speaking of which, is pay_type null in any records?

Does tktimcrd.work_date have times with the dates? If so you might be
missing data from the [End Date]. Try this to see:

SELECT tktimcrd.work_date,
Format(tktimcrd.work_date, "mm/dd/yyyy hh:nn") As WithTime
FROM tktimcrd
WHERE tktimcrd.work_date <> CLng(tktimcrd.work_date) ;
--
Jerry Whittle, Microsoft Access MVP
Light. Strong. Cheap. Pick two. Keith Bontrager - Bicycle Builder.

I keep getting variable results from the following query.


SELECT tktimcrd.employee, Trim([name]) & ", " & Trim([first_name]) AS
EmployeeName, (Sum(IIf([pay_type] Not In ("P","S","Y","Z"),[hours]))) AS
Regular, (Sum(IIf([pay_type] In ("P","Y"),[hours]))) AS Personal,
(Sum(IIf([pay_type] In ("S","Z"),[hours]))) AS Sick, Sum(tktimcrd.hours) AS
SumOfhours
FROM py_emplo INNER JOIN tktimcrd ON py_emplo.employee = tktimcrd.employee
WHERE (((tktimcrd.work_date) Between [Start Date] And [End Date]))
GROUP BY tktimcrd.employee, Trim([name]) & ", " & Trim([first_name])
WITH OWNERACCESS OPTION;


The results never seem to be the same. Sometimes it'll add a few hours to
one employee, the next time to someone else, and then sometimes it will just
mix names, for example, say I have a steve smith and a john doe, everynow and
then it will give some hours to an employee named steve doe. I'm not sure if
this has to do since it's pulling the data from a foxpro database and the
field name for last name is "name" which if I'm remembering is a reserved
word in Access. Any help would be appreciated.

 

[Shanin]

Both those tables are from FoxPro, which I didn't make so I couldn't tell you
since that doesn't show in Access, but from looking at the table structure
that is the constant between tables is the employee field and that is the
only field we cannot change in that database so it should be the primary key.

I tried adding an order by EmployeeName and am still getting the odd thing
of it adding someones first name to someone else's last name. The table
py-emplo is all basic employee information, name, address, wage, etc. The
table tktimecrd is that person's punch in and out's, it only stores that
information with the field employee as being the only link to link it to
other tables to pull up the person's name. The field employee is just 00-
followed by the first 7 digits of the employee's last name. As I said,
that's the only field that is the same between the two tables so it must be
their primary key.

There are different pay types, and I checked and none were null, it defaults
to regular which is pay type "R".

It doesn't look like there are times when looking at the table, just the
date. I did that query and it pulled up blank as well.

Is either py_emplo.employee or tktimcrd.employee the primary key fields for
their tables? If not you could have a many-to-many join.

I noticed that there isn't an ORDER BY clause. Could it be that the records
aren't sorted the same way each time and you are seeing different records?

Is there different pay types than "P","S","Y","Z" ?

Speaking of which, is pay_type null in any records?

Does tktimcrd.work_date have times with the dates? If so you might be
missing data from the [End Date]. Try this to see:

SELECT tktimcrd.work_date,
Format(tktimcrd.work_date, "mm/dd/yyyy hh:nn") As WithTime
FROM tktimcrd
WHERE tktimcrd.work_date <> CLng(tktimcrd.work_date) ;
--
Jerry Whittle, Microsoft Access MVP
Light. Strong. Cheap. Pick two. Keith Bontrager - Bicycle Builder.

I keep getting variable results from the following query.


SELECT tktimcrd.employee, Trim([name]) & ", " & Trim([first_name]) AS
EmployeeName, (Sum(IIf([pay_type] Not In ("P","S","Y","Z"),[hours]))) AS
Regular, (Sum(IIf([pay_type] In ("P","Y"),[hours]))) AS Personal,
(Sum(IIf([pay_type] In ("S","Z"),[hours]))) AS Sick, Sum(tktimcrd.hours) AS
SumOfhours
FROM py_emplo INNER JOIN tktimcrd ON py_emplo.employee = tktimcrd.employee
WHERE (((tktimcrd.work_date) Between [Start Date] And [End Date]))
GROUP BY tktimcrd.employee, Trim([name]) & ", " & Trim([first_name])
WITH OWNERACCESS OPTION;


The results never seem to be the same. Sometimes it'll add a few hours to
one employee, the next time to someone else, and then sometimes it will just
mix names, for example, say I have a steve smith and a john doe, everynow and
then it will give some hours to an employee named steve doe. I'm not sure if
this has to do since it's pulling the data from a foxpro database and the
field name for last name is "name" which if I'm remembering is a reserved
word in Access. Any help would be appreciated.

 

[aj]

I've noticed that a lot of Excel Users will use the same method you're
describing below when trying to run sums in Access (trying to mimic the SumIf
function from Excel). I will suggest that you break the Sum(IIF) statements
below and create multiple queries for each type. Then use a query to combine
your results. It may take some time to get it to work but it will save you
time later when you're trying to make a change or find a bug!

good luck!
aj

Light. Strong. Cheap. Pick two.

For bicycles, I will select 1 and 2
For women, it would be 1 and 3

Now I need to go pray.
--
Dave Hargis, Microsoft Access MVP

Is either py_emplo.employee or tktimcrd.employee the primary key fields for
their tables? If not you could have a many-to-many join.

I noticed that there isn't an ORDER BY clause. Could it be that the records
aren't sorted the same way each time and you are seeing different records?

Is there different pay types than "P","S","Y","Z" ?

Speaking of which, is pay_type null in any records?

Does tktimcrd.work_date have times with the dates? If so you might be
missing data from the [End Date]. Try this to see:

SELECT tktimcrd.work_date,
Format(tktimcrd.work_date, "mm/dd/yyyy hh:nn") As WithTime
FROM tktimcrd
WHERE tktimcrd.work_date <> CLng(tktimcrd.work_date) ;
--
Jerry Whittle, Microsoft Access MVP
Light. Strong. Cheap. Pick two. Keith Bontrager - Bicycle Builder.

I keep getting variable results from the following query.


SELECT tktimcrd.employee, Trim([name]) & ", " & Trim([first_name]) AS
EmployeeName, (Sum(IIf([pay_type] Not In ("P","S","Y","Z"),[hours]))) AS
Regular, (Sum(IIf([pay_type] In ("P","Y"),[hours]))) AS Personal,
(Sum(IIf([pay_type] In ("S","Z"),[hours]))) AS Sick, Sum(tktimcrd.hours) AS
SumOfhours
FROM py_emplo INNER JOIN tktimcrd ON py_emplo.employee = tktimcrd.employee
WHERE (((tktimcrd.work_date) Between [Start Date] And [End Date]))
GROUP BY tktimcrd.employee, Trim([name]) & ", " & Trim([first_name])
WITH OWNERACCESS OPTION;


The results never seem to be the same. Sometimes it'll add a few hours to
one employee, the next time to someone else, and then sometimes it will just
mix names, for example, say I have a steve smith and a john doe, everynow and
then it will give some hours to an employee named steve doe. I'm not sure if
this has to do since it's pulling the data from a foxpro database and the
field name for last name is "name" which if I'm remembering is a reserved
word in Access. Any help would be appreciated.