Ms Access 97

[A_Classic_Man]

Yup, you read it right, "97".

My employer assigned me to do a research project for him. I have used
Access some, but not much VBA. I have found many answers to my
questions by searching the MVP sites. So thanks for all the help.

I have a table (that is imported from Excel) with the following
fields: InvoiceNum, TruckNum, LeaveTime, ArriveTime, ArriveFirst (a
Yes/No Field).

I have set up a module that will let me pick 25 random invoice numbers
each month and place them in another table.

To that table I have added the following fields: TravelTime (which is
based on the expression:[ArrivalTime]-1[LeaveTime]),
TravelTime<10Min(which has the expression:
IIf([TravelTime]<"00:10:00","-1","0") ), and TravelTime<20Min(which
has the expression: IIf([TravelTime]<"00:20:00","-1","0") ).

There may be from 2 to 6 invoices with the same number with 2 to 6
corrisponding truck numbers and the corrisponding time fields. (IE 2
to 6 records with the same invoice numbers and corrisponding
information)

What I need to do is determine if the first truck to arrive for each
invoice number did so in 10 minutes or less and if all trucks with the
same invoice number arrived in 20 minutes or less.

Any help will be greatly appreciated.

If this is posted to the wrong group, sorry.

Ron

 

[John Nurick]

Hi Ron,

Queries like these should do it:

1) Invoices for which the first truck arrived within 10 minutes:

SELECT DISTINCT A.InvoiceNum
FROM tblRON AS A
WHERE A.ArrivalTime = (
SELECT MIN(B.ArrivalTime)
FROM tblRON AS B
WHERE A.InvoiceNum = B.InvoiceNum
AND B.TravelTimeU10
)
ORDER BY A.InvoiceNum
;


2) Invoices where all trucks arrived within 20 minutes (i.e. not
invoices where any truck arrived outside 20 minutes):

SELECT DISTINCT InvoiceNum
FROM tblRON
WHERE InvoiceNum NOT IN (
SELECT InvoiceNum
FROM tblRON AS A
WHERE NOT TravelTimeU20
)
ORDER BY InvoiceNum
;


A few comments:

-Life is simpler in the long run if you don't use spaces or special
characters in the names of fields or other objects. That's why I used
"TravelTimeU10" instead of "TravelTime<10".

-I've assumed you used Yes/No fields for TravelTimeU10 and
TravelTimeU20, although your use of
"0"
and
"-1"
suggests you may have defined these as text fields.

-Normally it's not necessary or desirable to store calculated values in
a table. Instead, use expressions in your queries to calculate them as
required. In the queries above you could replace (for example)
B.TravelTimeU10
with the expression
DateDiff("n", LeaveTime, ArriveTime) < 10

-Usually, it's not necessary to hive data off into a separate table to
report on it. Extracting a random sample of data may sometimes be an
exception. But I have to ask, why bother with a sample when it's so easy
to query the entire dataset?

Yup, you read it right, "97".

My employer assigned me to do a research project for him. I have used
Access some, but not much VBA. I have found many answers to my
questions by searching the MVP sites. So thanks for all the help.

I have a table (that is imported from Excel) with the following
fields: InvoiceNum, TruckNum, LeaveTime, ArriveTime, ArriveFirst (a
Yes/No Field).

I have set up a module that will let me pick 25 random invoice numbers
each month and place them in another table.

To that table I have added the following fields: TravelTime (which is
based on the expression:[ArrivalTime]-1[LeaveTime]),
TravelTime<10Min(which has the expression:
IIf([TravelTime]<"00:10:00","-1","0") ), and TravelTime<20Min(which
has the expression: IIf([TravelTime]<"00:20:00","-1","0") ).

There may be from 2 to 6 invoices with the same number with 2 to 6
corrisponding truck numbers and the corrisponding time fields. (IE 2
to 6 records with the same invoice numbers and corrisponding
information)

What I need to do is determine if the first truck to arrive for each
invoice number did so in 10 minutes or less and if all trucks with the
same invoice number arrived in 20 minutes or less.

Any help will be greatly appreciated.

If this is posted to the wrong group, sorry.

Ron

 

[A_Classic_Man]

Hi Ron,

Queries like these should do it:

1) Invoices for which the first truck arrived within 10 minutes:

SELECT DISTINCT A.InvoiceNum
FROM tblRON AS A
WHERE A.ArrivalTime = (
SELECT MIN(B.ArrivalTime)
FROM tblRON AS B
WHERE A.InvoiceNum = B.InvoiceNum
AND B.TravelTimeU10
)
ORDER BY A.InvoiceNum
;

2) Invoices where all trucks arrived within 20 minutes (i.e. not
invoices where any truck arrived outside 20 minutes):

SELECT DISTINCT InvoiceNum
FROM tblRON
WHERE InvoiceNum NOT IN (
SELECT InvoiceNum
FROM tblRON AS A
WHERE NOT TravelTimeU20
)
ORDER BY InvoiceNum
;

A few comments:

-Life is simpler in the long run if you don't use spaces or special
characters in the names of fields or other objects. That's why I used
"TravelTimeU10" instead of "TravelTime<10".

-I've assumed you used Yes/No fields for TravelTimeU10 and
TravelTimeU20, although your use of
"0"
and
"-1"
suggests you may have defined these as text fields.

-Normally it's not necessary or desirable to store calculated values in
a table. Instead, use expressions in your queries to calculate them as
required. In the queries above you could replace (for example)
B.TravelTimeU10
with the expression
DateDiff("n", LeaveTime, ArriveTime) < 10

-Usually, it's not necessary to hive data off into a separate table to
report on it. Extracting a random sample of data may sometimes be an
exception. But I have to ask, why bother with a sample when it's so easy
to query the entire dataset?

Yup, you read it right, "97".

My employer assigned me to do a research project for him. I have used
Access some, but not much VBA. I have found many answers to my
questions by searching the MVP sites. So thanks for all the help.

I have a table (that is imported from Excel) with the following
fields: InvoiceNum, TruckNum, LeaveTime, ArriveTime, ArriveFirst (a
Yes/No Field).

I have set up a module that will let me pick 25 random invoice numbers
each month and place them in another table.

To that table I have added the following fields: TravelTime (which is
based on the expression:[ArrivalTime]-1[LeaveTime]),
TravelTime<10Min(which has the expression:
IIf([TravelTime]<"00:10:00","-1","0") ), and TravelTime<20Min(which
has the expression: IIf([TravelTime]<"00:20:00","-1","0") ).

There may be from 2 to 6 invoices with the same number with 2 to 6
corrisponding truck numbers and the corrisponding time fields. (IE 2
to 6 records with the same invoice numbers and corrisponding
information)

What I need to do is determine if the first truck to arrive for each
invoice number did so in 10 minutes or less and if all trucks with the
same invoice number arrived in 20 minutes or less.

Any help will be greatly appreciated.

If this is posted to the wrong group, sorry.

Ron

--
John Nurick [Microsoft Access MVP]

Please respond in the newsgroup and not by email.- Hide quoted text -

- Show quoted text -

Yes/No fields are Text Fields.

I will try this out and let you know how it goes. Might be a day or
two.

Thanks for ALL of the advice and help. It's great to have folks like
you around. You have no idea how much I have relied on MVP since I am
somewhat inexperienced inn Access. Thanks again

Ron

 

[John Nurick]

Yes/No fields are Text Fields.

I will try this out and let you know how it goes. Might be a day or
two.

If text fields, then use text comparisons:

SELECT DISTINCT A.InvoiceNum
FROM tblRON AS A
WHERE A.ArrivalTime = (
SELECT MIN(B.ArrivalTime)
FROM tblRON AS B
WHERE A.InvoiceNum = B.InvoiceNum
AND (B.TravelTimeU10 = '-1')
)
ORDER BY A.InvoiceNum
;

 

[A_Classic_Man]

Yes/No fields are Text Fields.

I will try this out and let you know how it goes. Might be a day or
two.

If text fields, then use text comparisons:

SELECT DISTINCT A.InvoiceNum
FROM tblRON AS A
WHERE A.ArrivalTime = (
SELECT MIN(B.ArrivalTime)
FROM tblRON AS B
WHERE A.InvoiceNum = B.InvoiceNum
AND (B.TravelTimeU10 = '-1')
)
ORDER BY A.InvoiceNum
;

--
John Nurick [Microsoft Access MVP]

Please respond in the newsgroup and not by email.- Hide quoted text -

- Show quoted text -

Hello John

Your suggestion worked, Thanks

Ron