selecting correct records using calculation

[BladeCanyon via AccessMonster.com]

Hi all,

I have an append query that appends records such as the following:
EID IDate ITypeID IHours
00001 3/2/2009 11 8
00001 3/2/2009 11 -8
00001 3/2/2009 13 8

(yes, some "dunce" incorrectly entered records in the Enterprise application)
I have eliminated the ability to import duplicate records from the table but
then I do not get the correct record ( the third record above with type ID 13)
because I have based it on idate and EIDand it takes the first record it
comes to. I want the query to calculate the hours of the typeID and if the
value is zero, ignore it. The records are coming from an excel spreadsheet as
a linked table.

if I change the table index to include the type id to eliminate duplicates I
get the following:
EID IDate ITypeID IHours
00001 3/2/2009 13 8
00001 3/2/2009 11 8

Any suggestions?
thanks in advance
BC

 

[KARL DEWEY]

Try this --
SELECT EID, IDate, ITypeID, Sum([IHours]) AS IHours_Sum
FROM YourTable
WHERE Sum([IHours]) <> 0
ORDER BY EID, IDate, ITypeID;

 

[BladeCanyon via AccessMonster.com]

Karl

thanks for the assistance. However, I am receiving an error.
can not have aggregate function in WHERE clause (SUM(IncidentHours])<>0)

here is the code:
SELECT EmpID, IncidentDate, IncidentTypeID, Sum([IncidentHours]) AS
IHours_Sum
FROM attendance_master
WHERE Sum([IncidentHours]) <> 0
ORDER BY EmpID, IncidentDate, IncidentTypeID

as you can see I correctly used the method suggested. I am sure that the
error is telling me that I am unable to perform this type of action, which is
what I received before. I am missing a step or two I think.

any suggestions?

thanks in advance
BC

thanks in advance

 

[MGFoster]

Karl

thanks for the assistance. However, I am receiving an error.
can not have aggregate function in WHERE clause (SUM(IncidentHours])<>0)

here is the code:
SELECT EmpID, IncidentDate, IncidentTypeID, Sum([IncidentHours]) AS
IHours_Sum
FROM attendance_master
WHERE Sum([IncidentHours]) <> 0
ORDER BY EmpID, IncidentDate, IncidentTypeID

as you can see I correctly used the method suggested. I am sure that the
error is telling me that I am unable to perform this type of action, which is
what I received before. I am missing a step or two I think.

any suggestions?

-----BEGIN PGP SIGNED MESSAGE-----
Hash: SHA1

You need to have a GROUP BY clause and then you can move the WHERE
clause to a HAVING clause, where aggregate functions belong. E.g.:

SELECT EmpID, IncidentDate, IncidentTypeID, Sum([IncidentHours]) AS
IHours_Sum
FROM attendance_master
GROUP BY EmpID, IncidentDate, IncidentTypeID
HAVING Sum([IncidentHours]) <> 0

You don't need the ORDER BY clause 'cuz Access grouping queries
automatically sort by the GROUP BY clause columns.

--
MGFoster:::mgf00 <at> earthlink <decimal-point> net
Oakland, CA (USA)
** Respond only to this newsgroup. I DO NOT respond to emails **

-----BEGIN PGP SIGNATURE-----
Version: PGP for Personal Privacy 5.0
Charset: noconv

iQA/AwUBSbre0YechKqOuFEgEQJOKgCgr7viQLX1ANQAvHV8ERFRGaV3MWkAoLmY
xzEC1BYr/c5NLyJxGi0WVx/j
=EXZd
-----END PGP SIGNATURE-----

 

[BladeCanyon via AccessMonster.com]

MGFoster,

good day friend. thank you for the info. I tried this also. with this, I get
a dialog box when executing the query, incidenTtypeID input dialog box. if I
click ok past it, then I get what looks to be all the right records, but
without the incidentTypeid, it is blank? because i did not put anything in
the input box. I still think we are missing an step or something. can i
achieve this without using the SQL view and putting in the sql statement?
simply setting up the query in design view?
what am I missing here? we are getting closer.

thanks in advance
BC

 

[MGFoster]

MGFoster,

good day friend. thank you for the info. I tried this also. with this, I get
a dialog box when executing the query, incidenTtypeID input dialog box. if I
click ok past it, then I get what looks to be all the right records, but
without the incidentTypeid, it is blank? because i did not put anything in
the input box. I still think we are missing an step or something. can i
achieve this without using the SQL view and putting in the sql statement?
simply setting up the query in design view?
what am I missing here? we are getting closer.

-----BEGIN PGP SIGNED MESSAGE-----
Hash: SHA1

You probably mis-typed IncidentTypeID. Make sure the column name is
spelled correctly. If you cut & paste my previous post's SQL solution
into the SQL view you can then switch to the Design Grid View. It's
just easier to post SQL rather than describe what every cell in the
Design Grid should hold.

--
MGFoster:::mgf00 <at> earthlink <decimal-point> net
Oakland, CA (USA)
** Respond only to this newsgroup. I DO NOT respond to emails **

-----BEGIN PGP SIGNATURE-----
Version: PGP for Personal Privacy 5.0
Charset: noconv

iQA/AwUBSb7jeYechKqOuFEgEQJTQQCg6MALMXDycGMozchFCCryVyKTViIAnR1a
f9+Ir8XUcSFuRDUKZheISxVX
=vp6n
-----END PGP SIGNATURE-----

 

[BladeCanyon via AccessMonster.com]

MGFoster

good day my friend

yes. you were correct. my reference was not mis-spelled. it was incorrect all
together. I completely over looked my import table which is IncidentCode and
then once imported (due to difference in systems) uses incidentTypeID.

Thanks again for your support and persistence.

BC

p.s. let me also add that this site is awesome. the quality, quantity, and
overall helpful-ness of this community is great. Kudos to all.
thanks again
BC