out of sequece: group by "Product", then using First([TimeProduced]) 1

[rxsims]

Hi. I have a number of production records from many machines, with many product codes. The table gets populated chronologically with data from all machines. example:

[time]=5:00; [Machine]=1; [Product]=ABC; [quantity]=5
[time]=5:01; [Machine]=2; [Product]=ABC; [quantity]=5
[time]=5:02; [Machine]=3; [Product]=ABC; [quantity]=5
[time]=5:04; [Machine]=2; [Product]=ABC; [quantity]=5
[time]=5:05; [Machine]=4; [Product]=ABC; [quantity]=5


My current report shows:

Group by [Machine]
Group by [Product]

[quantity] data is summarized and reported/printed at this level, and I show First([time]) for the "Group by [Product]" to show when the production run for [Product]=abc started on [machine]=1. The PROBLEM is that I want to see the [Product] group sorted by First([time]), but currently it is sorted alphabetically by [Product] instead.

How can I sort the "Group by [Product]" by First([Time]) of the "Group by [Product]"??

Any help is greatly appreciated!

 

[dhookom]

I would create a totals query that groups by Machine and Product with the Min([Time]). Then add this totals query to your record source query and join the Machine and Product fields. You will now have the MinOfTime that you can use rather than the Product.

Note: I have never found a use for "First" in queries. I always use "Min".

Duane
Hook'D on Access
MS Access MVP

 

[rxsims]

Thank you Duane. It worked perfectly.

 

[rxsims]

Another question - possibly should be another thread:

Within the dataset I describe above:

[time]=5:00; [Machine]=1; [Product]=ABC; [quantity]=5
[time]=5:01; [Machine]=2; [Product]=ABC; [quantity]=5
[time]=5:02; [Machine]=3; [Product]=ABC; [quantity]=5
[time]=5:04; [Machine]=2; [Product]=ABC; [quantity]=5
[time]=5:05; [Machine]=4; [Product]=ABC; [quantity]=5

there are also downtime records, i.e.
[time] = 5:03; [Machine]=2; [duration]=10

The downtime records are NOT tagged with the [Product]=ABC.
how can I relate the downtime records within a given time frame (i.e. between min([time] and max([time])?

 

[dhookom]

According to your data, you only produced Product ABC so there shouldn't be an issue.

Do you have table names? Are there dates involved that we need to worry about?

I generally create a query based on the production that uses a subquery to find the EndTime values for the machine that is greater than the current time and is a new Product.

I can then use the Time and EndTime as a between to determine the correct Product.

Duane
Hook'D on Access
MS Access MVP

 

[rxsims]

Hi -
I've already taken into account the dates in the query and report.

to add a bit of detail regarding my table, this is what the records look more like for one machine. A controller creates a data record with individual identifiers (I've used A, B, and C as the data representing those fields) which I combine in my query to get [product] = ABC, but when there is a downtime does not equal 0 record created, it does not attach all three parts of the [product] record.

[time]=5:00; [Machine]=1; [Product]=ABC; [qty]=5
[time]=5:02; [Machine]=1; [Product]=ABC; [qty]=5
[time]=5:02; [Machine]=1; [Product]=AB ; [qty]=5 [downtime]=3
this is unplanned downtime, then continues on same [Product]
[time]=5:07; [Machine]=1; [Product]=ABC; [qty]=5
[time]=5:11; [Machine]=1; [Product]=ABC; [qty]=5
[time]=5:12; [Machine]=1; [Product]=AB ; [qty]=0 [downtime]=10
in this case it's a changeover from [Product]=ABC to ABE
[time]=5:22; [Machine]=1; [Product]=ABE; [qty]=5
[time]=5:26; [Machine]=1; [Product]=ABE; [qty]=5
[time]=5:27; [Machine]=1; [Product]=ABE; [qty]=5

My big problem is that the [downtime]>0 records do not have the field represented by "C" populated. It's a problem with the source program which I can't fix. The only way I can relate the the two is via time span.

What I have with your help is the [downtime]=0 records for [machine 1]=1; [Product]=ABC grouped and totalled, now I want a subreport under each grouping of [machine1]=1 and [Product]=ABC that shows all records with [downtime] >0.

 

[dhookom]

Start by creating a query like:

SELECT Production.Machine, Production.Time, (SELECT Min([Time]) FROM Production P where P.Machine = Production.Machine AND P.[Time]>Production.[Time]) AS EndTime, Production.Product, Production.Quantity
FROM Production
ORDER BY Production.Machine, Production.Time;

This creates a result like:
[tt][blue]
Machine Time EndTime Product Quantity
1 5:00:00 AM 5:02:00 AM ABC 5
1 5:02:00 AM 5:07:00 AM ABC 5
1 5:07:00 AM 5:11:00 AM ABC 5
1 5:11:00 AM 5:22:00 AM ABC 5
1 5:22:00 AM 5:26:00 AM ABE 5
1 5:26:00 AM 5:27:00 AM ABE 5
1 5:27:00 AM ABE 5
[/blue][/tt]
You can use this with your downtime [Time] and [Line] to find the Product.


Duane
Hook'D on Access
MS Access MVP