first occurrences each day

[Guest]

I am a beginner at Access, so please excuse if this is too simple a question.
Seems like all the tutorials and books never answer exactly what I'm looking
for.

I have several machines that produce widgets. An Access table exists that
shows the machine number (there are about 20 machines each with ID numbers)
and the cumulative number of widgets produced at many different times each
day. (different events through the day trigger that this data is recorded, so
each machine has many records each day)

I want to know total number of widgets each machine produced each day. I
think what needs to happen is to make a query that selects only the first
record for each machine for each day. How do I do this?

 

[John Vinson]

I am a beginner at Access, so please excuse if this is too simple a question.
Seems like all the tutorials and books never answer exactly what I'm looking
for.

I have several machines that produce widgets. An Access table exists that
shows the machine number (there are about 20 machines each with ID numbers)
and the cumulative number of widgets produced at many different times each
day. (different events through the day trigger that this data is recorded, so
each machine has many records each day)

I want to know total number of widgets each machine produced each day. I
think what needs to happen is to make a query that selects only the first
record for each machine for each day. How do I do this?

Um?

How would finding the first of n widgets help you count how many
widgets were produced during the day?

I guess I don't understand the structure of your table. If each record
has (or can be used to calculate) the number produced during that
record's time period, a simple Totals query using a criterion of

= [Enter date:] AND < DateAdd("d", 1, [Enter date:])

will prompt you for what date you want to count, and report the sum of
the widgets for each machine.

Am I misunderstanding? What is in fact the structure of the table, and
how is the number recorded?

John W. Vinson[MVP]

 

[John Spencer]

It would help if you posted something about your table structure.
Assumptions:
Table with fields MachineID, IncidentDateTime, WidgetCount
IncidentDateTime contains both a Date and a Time

SELECT MachineID, IncidentDateTime, WidgetCount
FROM YourTable as X
WHERE IncidentDateTime =
(SELECT Min(IncidentDateTime)
FROM YourTable as X2
WHERE X2.MachineID = X.MachineID
AND DateValue(X2.IncidentDateTime) = DateValue(X.IncidentDateTime))

I think that should give you a list of the earliest count on each day for
each machineid that had an entry on a date.