Need help with a simple? math problem

  • Thread starter Thread starter Dj
  • Start date Start date
D

Dj

My table looks like this

name - mpg - mpg date - training date

Every emp has a record for each month tracking their mpg. The training date
remains empty until they get training which could be just once or once every
6 months. When they get trained, the training date goes in the most recent
record. A sampling of this table would be ...

joe 6.4 jul08 null
joe 6.2 aug08 9/3/08
joe 6.8 sep08 null
bob 7.4 jul08 null
bob 6.9 aug08 null
bob 6.8 sep08 null
ann 6.4 jul08 null
ann 6.5 aug08 9/15/08
ann 7.0 sep08 null

Here's what I'd like to do... if the driver had training, I'd like to
calculate the change in MPG from prior to their training to following their
training. I just can't seem to figure out how to do it. It doesn't matter
if the final output is a query or report. I'm beginning to think that I
should pull the training date out of the main table and put it in a seperate
table. Would that make this task easier.

Using the sample data above, my report/query would return the following only
the employees who had training and the difference in their mpg from the month
prior to training until after...

Joe trained on 9/3/08 6.8-6.2=.6
Ann trained on 9/15/08 7.0-6.5=.5
or simplified...
Joe .6
Ann .5

Thanks in advance for your help. Dj
 
A couple of possible queries:

SELECT [name],
MAX([training date]) As TrainedOn,
(SELECT mpg
FROM MileageLog AS ML2
WHERE ML2.[name] = ML1.[name]
AND [mpg date] =
(SELECT MAX([mpg date])
FROM MileageLog AS ML2
WHERE ML2.[name] = ML1.[name]))
AS CurrentMPG,
(SELECT mpg
FROM MileageLog AS ML2
WHERE ML2.[name] = ML1.[name]
AND [training date] =
(SELECT MAX([training date])
FROM MileageLog AS ML2
WHERE ML2.[name] = ML1.[name]))
AS PreviousMPG,
CurrentMPG - PreviousMPG AS Improvement
FROM MileageLog AS ML1
WHERE [training date] IS NOT NULL
GROUP BY [Name];

Or alternatively:

SELECT [name],
(SELECT MAX([training date])
FROM MileageLog AS ML2
WHERE ML2.[name] = ML1.[name])
AS TrainedOn,
mpg AS CurrentMPG,
(SELECT mpg
FROM MileageLog As ML2
WHERE ML2.[name] = ML1.[name]
AND [training date] =
(SELECT MAX([training date])
FROM MileageLog AS ML2
WHERE ML2.[name] = ML1.[name]))
AS PreviousMPG,
mpg - PreviousMPG AS Improvement
FROM MileageLog As ML1
WHERE [mpg date] =
(SELECT MAX([mpg date])
FROM MileageLog AS ML2
WHERE ML2.[name] = ML1.[name])
AND EXISTS
(SELECT *
FROM MileageLog AS ML2
WHERE ML2.[name] = ML1.[name]
AND ML2.[training date] IS NOT NULL);

Where MileageLog is the table name. I'd think the first query is probably
the more efficient, but both will give the same results.

A couple of supplementary points:

1. I'd recommend you don't use 'name' as a column name as it’s the name of
a built in property in Access. I recall a post whwre someone had done this
and the name of the report appeared in each row in a report rather than the
names of the individuals. You could use something like DriverName or
EmployeeName, but even better is to split the names. More on this in point 2
below.

2. Do not use names as keys or parts of a key. In your table the key is in
fact a composite one of name and training date. Names can be duplicated (I
worked with two Maggie Taylors once). Use a numeric value in a column
DriverID or EmployeeID instead. This can then reference a Drivers or
Employees table with an autonumber primary key of DriverID or EmployeeID.
For the names its best to have separate Firstname and Lastname columns. For
data input you can use an Employees form with a MilegeLog subform linked on
DriverID, in which case the relevant DriverID value will automatically be
inserted into the MileageLog table as you add a new record. For a report
you'd join the Drivers table to one of the above queries to include names in
report.

Ken Sheridan
Stafford, England
 
Thank you so much for the help and suggestions.
A couple of follow ups...
1. Where would this code go?
2. I understand I have to build a code expression, but I'm not sure
where to put it?
3. Does it need to be In a query or a report or will either work?
4. Would it go in a new non-bound text field or a bounded text field?

Thanks again for the help and suggestions.

Ken Sheridan said:
A couple of possible queries:

SELECT [name],
MAX([training date]) As TrainedOn,
(SELECT mpg
FROM MileageLog AS ML2
WHERE ML2.[name] = ML1.[name]
AND [mpg date] =
(SELECT MAX([mpg date])
FROM MileageLog AS ML2
WHERE ML2.[name] = ML1.[name]))
AS CurrentMPG,
(SELECT mpg
FROM MileageLog AS ML2
WHERE ML2.[name] = ML1.[name]
AND [training date] =
(SELECT MAX([training date])
FROM MileageLog AS ML2
WHERE ML2.[name] = ML1.[name]))
AS PreviousMPG,
CurrentMPG - PreviousMPG AS Improvement
FROM MileageLog AS ML1
WHERE [training date] IS NOT NULL
GROUP BY [Name];

Or alternatively:

SELECT [name],
(SELECT MAX([training date])
FROM MileageLog AS ML2
WHERE ML2.[name] = ML1.[name])
AS TrainedOn,
mpg AS CurrentMPG,
(SELECT mpg
FROM MileageLog As ML2
WHERE ML2.[name] = ML1.[name]
AND [training date] =
(SELECT MAX([training date])
FROM MileageLog AS ML2
WHERE ML2.[name] = ML1.[name]))
AS PreviousMPG,
mpg - PreviousMPG AS Improvement
FROM MileageLog As ML1
WHERE [mpg date] =
(SELECT MAX([mpg date])
FROM MileageLog AS ML2
WHERE ML2.[name] = ML1.[name])
AND EXISTS
(SELECT *
FROM MileageLog AS ML2
WHERE ML2.[name] = ML1.[name]
AND ML2.[training date] IS NOT NULL);

Where MileageLog is the table name. I'd think the first query is probably
the more efficient, but both will give the same results.

A couple of supplementary points:

1. I'd recommend you don't use 'name' as a column name as it’s the name of
a built in property in Access. I recall a post whwre someone had done this
and the name of the report appeared in each row in a report rather than the
names of the individuals. You could use something like DriverName or
EmployeeName, but even better is to split the names. More on this in point 2
below.

2. Do not use names as keys or parts of a key. In your table the key is in
fact a composite one of name and training date. Names can be duplicated (I
worked with two Maggie Taylors once). Use a numeric value in a column
DriverID or EmployeeID instead. This can then reference a Drivers or
Employees table with an autonumber primary key of DriverID or EmployeeID.
For the names its best to have separate Firstname and Lastname columns. For
data input you can use an Employees form with a MilegeLog subform linked on
DriverID, in which case the relevant DriverID value will automatically be
inserted into the MileageLog table as you add a new record. For a report
you'd join the Drivers table to one of the above queries to include names in
report.

Ken Sheridan
Stafford, England

Dj said:
My table looks like this

name - mpg - mpg date - training date

Every emp has a record for each month tracking their mpg. The training date
remains empty until they get training which could be just once or once every
6 months. When they get trained, the training date goes in the most recent
record. A sampling of this table would be ...

joe 6.4 jul08 null
joe 6.2 aug08 9/3/08
joe 6.8 sep08 null
bob 7.4 jul08 null
bob 6.9 aug08 null
bob 6.8 sep08 null
ann 6.4 jul08 null
ann 6.5 aug08 9/15/08
ann 7.0 sep08 null

Here's what I'd like to do... if the driver had training, I'd like to
calculate the change in MPG from prior to their training to following their
training. I just can't seem to figure out how to do it. It doesn't matter
if the final output is a query or report. I'm beginning to think that I
should pull the training date out of the main table and put it in a seperate
table. Would that make this task easier.

Using the sample data above, my report/query would return the following only
the employees who had training and the difference in their mpg from the month
prior to training until after...

Joe trained on 9/3/08 6.8-6.2=.6
Ann trained on 9/15/08 7.0-6.5=.5
or simplified...
Joe .6
Ann .5

Thanks in advance for your help. Dj
Click to expand...
Click to expand...
 
Both solutions are the SQL (structured query language) for queries. Open the
query designer but don't add any table. Switch to SQL view and paste in one
or other of the solutions. Amend the SQL as necessary so that the table and
column (field) names exactly match you own. Remember that any table or field
names with spaces or other special characters in them have to be in square
brackets, e.g. [training date]. If in doubt put the brackets in.

If the query works OK you can save it and use it as the RecordSource of a
report or form. If you've followed my advice and used a numeric DriverID in
place of 'name' and a separate Drivers table, you can join the table to the
query in another query on the DriverID columns, and then include columns from
both the table and query in the final query. You can do this in design view.

The way the queries work is by using subqueries (the various SELECT…
statements in parentheses) to compute the relevant values. These subqueries
are correlated with the main 'outer' query on the name columns and you'll see
that the separate instances of the MileageLog table are identified by giving
then aliases ML1 and ML2, which allows them to be correlated so that each
subquery looks at only those rows for the current 'name'.

Ken Sheridan
Stafford, England

Dj said:
Thank you so much for the help and suggestions.
A couple of follow ups...
1. Where would this code go?
2. I understand I have to build a code expression, but I'm not sure
where to put it?
3. Does it need to be In a query or a report or will either work?
4. Would it go in a new non-bound text field or a bounded text field?

Thanks again for the help and suggestions.

Ken Sheridan said:
A couple of possible queries:

SELECT [name],
MAX([training date]) As TrainedOn,
(SELECT mpg
FROM MileageLog AS ML2
WHERE ML2.[name] = ML1.[name]
AND [mpg date] =
(SELECT MAX([mpg date])
FROM MileageLog AS ML2
WHERE ML2.[name] = ML1.[name]))
AS CurrentMPG,
(SELECT mpg
FROM MileageLog AS ML2
WHERE ML2.[name] = ML1.[name]
AND [training date] =
(SELECT MAX([training date])
FROM MileageLog AS ML2
WHERE ML2.[name] = ML1.[name]))
AS PreviousMPG,
CurrentMPG - PreviousMPG AS Improvement
FROM MileageLog AS ML1
WHERE [training date] IS NOT NULL
GROUP BY [Name];

Or alternatively:

SELECT [name],
(SELECT MAX([training date])
FROM MileageLog AS ML2
WHERE ML2.[name] = ML1.[name])
AS TrainedOn,
mpg AS CurrentMPG,
(SELECT mpg
FROM MileageLog As ML2
WHERE ML2.[name] = ML1.[name]
AND [training date] =
(SELECT MAX([training date])
FROM MileageLog AS ML2
WHERE ML2.[name] = ML1.[name]))
AS PreviousMPG,
mpg - PreviousMPG AS Improvement
FROM MileageLog As ML1
WHERE [mpg date] =
(SELECT MAX([mpg date])
FROM MileageLog AS ML2
WHERE ML2.[name] = ML1.[name])
AND EXISTS
(SELECT *
FROM MileageLog AS ML2
WHERE ML2.[name] = ML1.[name]
AND ML2.[training date] IS NOT NULL);

Where MileageLog is the table name. I'd think the first query is probably
the more efficient, but both will give the same results.

A couple of supplementary points:

1. I'd recommend you don't use 'name' as a column name as it’s the name of
a built in property in Access. I recall a post whwre someone had done this
and the name of the report appeared in each row in a report rather than the
names of the individuals. You could use something like DriverName or
EmployeeName, but even better is to split the names. More on this in point 2
below.

2. Do not use names as keys or parts of a key. In your table the key is in
fact a composite one of name and training date. Names can be duplicated (I
worked with two Maggie Taylors once). Use a numeric value in a column
DriverID or EmployeeID instead. This can then reference a Drivers or
Employees table with an autonumber primary key of DriverID or EmployeeID.
For the names its best to have separate Firstname and Lastname columns. For
data input you can use an Employees form with a MilegeLog subform linked on
DriverID, in which case the relevant DriverID value will automatically be
inserted into the MileageLog table as you add a new record. For a report
you'd join the Drivers table to one of the above queries to include names in
report.

Ken Sheridan
Stafford, England

Dj said:
My table looks like this

name - mpg - mpg date - training date

Every emp has a record for each month tracking their mpg. The training date
remains empty until they get training which could be just once or once every
6 months. When they get trained, the training date goes in the most recent
record. A sampling of this table would be ...

joe 6.4 jul08 null
joe 6.2 aug08 9/3/08
joe 6.8 sep08 null
bob 7.4 jul08 null
bob 6.9 aug08 null
bob 6.8 sep08 null
ann 6.4 jul08 null
ann 6.5 aug08 9/15/08
ann 7.0 sep08 null

Here's what I'd like to do... if the driver had training, I'd like to
calculate the change in MPG from prior to their training to following their
training. I just can't seem to figure out how to do it. It doesn't matter
if the final output is a query or report. I'm beginning to think that I
should pull the training date out of the main table and put it in a seperate
table. Would that make this task easier.

Using the sample data above, my report/query would return the following only
the employees who had training and the difference in their mpg from the month
prior to training until after...

Joe trained on 9/3/08 6.8-6.2=.6
Ann trained on 9/15/08 7.0-6.5=.5
or simplified...
Joe .6
Ann .5

Thanks in advance for your help. Dj
Click to expand...
Click to expand...
Click to expand...
 
You have been so helpful, I hope you don't mind that I'm a little slow at
this and I have to keep bugging you. I've come so far with your help and I'd
hate to give up now. My actual code is copied below and here are the actual
table and field names.
name=driver name
mpg date=roster date
mileagelog=tblMGP
training date = completed training date

I think those are the only ones I had to change to match my actual db. I
noticed that when I view the design view of this SQL, it only shows a table
ML2. Is that the way it's supposed to work? No ML1 should show? Also, when
I run the query, it's asking me to input the 'completed training date'.

Because I have no experience working with the SQL code of querys, I'm a
little lost.

Here's my code after modifying what you sent me...

SELECT Max([completed training date]) AS TrainedOn, (SELECT mpg FROM tblMPG
AS ML2
WHERE ML2.[driver name] = ML1.[driver name]
AND [roster date] =
(SELECT MAX([roster date]) FROM tblMPG AS ML2
WHERE ML2.[driver name] = ML1.[driver name])) AS CurrentMPG,
(SELECT mpg FROM tblMPG AS ML2
WHERE ML2.[driver name] = ML1.[driver name]
AND [completed training date] =(SELECT MAX([completed training date])
FROM tblMPG AS ML2
WHERE ML2.[driver name] = ML1.[driver name])) AS PreviousMPG,
CurrentMPG-PreviousMPG AS Improvement
FROM tblMPG AS ML1
WHERE ((([completed training date]) Is Not Null));



Ken Sheridan said:
Both solutions are the SQL (structured query language) for queries. Open the
query designer but don't add any table. Switch to SQL view and paste in one
or other of the solutions. Amend the SQL as necessary so that the table and
column (field) names exactly match you own. Remember that any table or field
names with spaces or other special characters in them have to be in square
brackets, e.g. [training date]. If in doubt put the brackets in.

If the query works OK you can save it and use it as the RecordSource of a
report or form. If you've followed my advice and used a numeric DriverID in
place of 'name' and a separate Drivers table, you can join the table to the
query in another query on the DriverID columns, and then include columns from
both the table and query in the final query. You can do this in design view.

The way the queries work is by using subqueries (the various SELECT…
statements in parentheses) to compute the relevant values. These subqueries
are correlated with the main 'outer' query on the name columns and you'll see
that the separate instances of the MileageLog table are identified by giving
then aliases ML1 and ML2, which allows them to be correlated so that each
subquery looks at only those rows for the current 'name'.

Ken Sheridan
Stafford, England

Dj said:
Thank you so much for the help and suggestions.
A couple of follow ups...
1. Where would this code go?
2. I understand I have to build a code expression, but I'm not sure
where to put it?
3. Does it need to be In a query or a report or will either work?
4. Would it go in a new non-bound text field or a bounded text field?

Thanks again for the help and suggestions.

Ken Sheridan said:
A couple of possible queries:

SELECT [name],
MAX([training date]) As TrainedOn,
(SELECT mpg
FROM MileageLog AS ML2
WHERE ML2.[name] = ML1.[name]
AND [mpg date] =
(SELECT MAX([mpg date])
FROM MileageLog AS ML2
WHERE ML2.[name] = ML1.[name]))
AS CurrentMPG,
(SELECT mpg
FROM MileageLog AS ML2
WHERE ML2.[name] = ML1.[name]
AND [training date] =
(SELECT MAX([training date])
FROM MileageLog AS ML2
WHERE ML2.[name] = ML1.[name]))
AS PreviousMPG,
CurrentMPG - PreviousMPG AS Improvement
FROM MileageLog AS ML1
WHERE [training date] IS NOT NULL
GROUP BY [Name];

Or alternatively:

SELECT [name],
(SELECT MAX([training date])
FROM MileageLog AS ML2
WHERE ML2.[name] = ML1.[name])
AS TrainedOn,
mpg AS CurrentMPG,
(SELECT mpg
FROM MileageLog As ML2
WHERE ML2.[name] = ML1.[name]
AND [training date] =
(SELECT MAX([training date])
FROM MileageLog AS ML2
WHERE ML2.[name] = ML1.[name]))
AS PreviousMPG,
mpg - PreviousMPG AS Improvement
FROM MileageLog As ML1
WHERE [mpg date] =
(SELECT MAX([mpg date])
FROM MileageLog AS ML2
WHERE ML2.[name] = ML1.[name])
AND EXISTS
(SELECT *
FROM MileageLog AS ML2
WHERE ML2.[name] = ML1.[name]
AND ML2.[training date] IS NOT NULL);

Where MileageLog is the table name. I'd think the first query is probably
the more efficient, but both will give the same results.

A couple of supplementary points:

1. I'd recommend you don't use 'name' as a column name as it’s the name of
a built in property in Access. I recall a post whwre someone had done this
and the name of the report appeared in each row in a report rather than the
names of the individuals. You could use something like DriverName or
EmployeeName, but even better is to split the names. More on this in point 2
below.

2. Do not use names as keys or parts of a key. In your table the key is in
fact a composite one of name and training date. Names can be duplicated (I
worked with two Maggie Taylors once). Use a numeric value in a column
DriverID or EmployeeID instead. This can then reference a Drivers or
Employees table with an autonumber primary key of DriverID or EmployeeID.
For the names its best to have separate Firstname and Lastname columns. For
data input you can use an Employees form with a MilegeLog subform linked on
DriverID, in which case the relevant DriverID value will automatically be
inserted into the MileageLog table as you add a new record. For a report
you'd join the Drivers table to one of the above queries to include names in
report.

Ken Sheridan
Stafford, England

:

My table looks like this

name - mpg - mpg date - training date

Every emp has a record for each month tracking their mpg. The training date
remains empty until they get training which could be just once or once every
6 months. When they get trained, the training date goes in the most recent
record. A sampling of this table would be ...

joe 6.4 jul08 null
joe 6.2 aug08 9/3/08
joe 6.8 sep08 null
bob 7.4 jul08 null
bob 6.9 aug08 null
bob 6.8 sep08 null
ann 6.4 jul08 null
ann 6.5 aug08 9/15/08
ann 7.0 sep08 null

Here's what I'd like to do... if the driver had training, I'd like to
calculate the change in MPG from prior to their training to following their
training. I just can't seem to figure out how to do it. It doesn't matter
if the final output is a query or report. I'm beginning to think that I
should pull the training date out of the main table and put it in a seperate
table. Would that make this task easier.

Using the sample data above, my report/query would return the following only
the employees who had training and the difference in their mpg from the month
prior to training until after...

Joe trained on 9/3/08 6.8-6.2=.6
Ann trained on 9/15/08 7.0-6.5=.5
or simplified...
Joe .6
Ann .5

Thanks in advance for your help. Dj
Click to expand...
Click to expand...
Click to expand...
 
Update from previous post...
1. The "asking for [completed training date]" is fixed; it was a typo error.
2. Output is only returning one record w/ the most recent training date.

Also, I didn't mention this before and I don't know if it's important...
The [roster date] is imported at the end of the month and has the last date
of the month for each driver. Where the actual [completed training date] can
happen anytime during the month, the [roster date] will always be the 30th or
31st of the month. Trying to decifer your code, I don't think that makes a
difference here.

Dj said:
You have been so helpful, I hope you don't mind that I'm a little slow at
this and I have to keep bugging you. I've come so far with your help and I'd
hate to give up now. My actual code is copied below and here are the actual
table and field names.
name=driver name
mpg date=roster date
mileagelog=tblMGP
training date = completed training date

I think those are the only ones I had to change to match my actual db. I
noticed that when I view the design view of this SQL, it only shows a table
ML2. Is that the way it's supposed to work? No ML1 should show? Also, when
I run the query, it's asking me to input the 'completed training date'.

Because I have no experience working with the SQL code of querys, I'm a
little lost.

Here's my code after modifying what you sent me...

SELECT Max([completed training date]) AS TrainedOn, (SELECT mpg FROM tblMPG
AS ML2
WHERE ML2.[driver name] = ML1.[driver name]
AND [roster date] =
(SELECT MAX([roster date]) FROM tblMPG AS ML2
WHERE ML2.[driver name] = ML1.[driver name])) AS CurrentMPG,
(SELECT mpg FROM tblMPG AS ML2
WHERE ML2.[driver name] = ML1.[driver name]
AND [completed training date] =(SELECT MAX([completed training date])
FROM tblMPG AS ML2
WHERE ML2.[driver name] = ML1.[driver name])) AS PreviousMPG,
CurrentMPG-PreviousMPG AS Improvement
FROM tblMPG AS ML1
WHERE ((([completed training date]) Is Not Null));



Ken Sheridan said:
Both solutions are the SQL (structured query language) for queries. Open the
query designer but don't add any table. Switch to SQL view and paste in one
or other of the solutions. Amend the SQL as necessary so that the table and
column (field) names exactly match you own. Remember that any table or field
names with spaces or other special characters in them have to be in square
brackets, e.g. [training date]. If in doubt put the brackets in.

If the query works OK you can save it and use it as the RecordSource of a
report or form. If you've followed my advice and used a numeric DriverID in
place of 'name' and a separate Drivers table, you can join the table to the
query in another query on the DriverID columns, and then include columns from
both the table and query in the final query. You can do this in design view.

The way the queries work is by using subqueries (the various SELECT…
statements in parentheses) to compute the relevant values. These subqueries
are correlated with the main 'outer' query on the name columns and you'll see
that the separate instances of the MileageLog table are identified by giving
then aliases ML1 and ML2, which allows them to be correlated so that each
subquery looks at only those rows for the current 'name'.

Ken Sheridan
Stafford, England

Dj said:
Thank you so much for the help and suggestions.
A couple of follow ups...
1. Where would this code go?
2. I understand I have to build a code expression, but I'm not sure
where to put it?
3. Does it need to be In a query or a report or will either work?
4. Would it go in a new non-bound text field or a bounded text field?

Thanks again for the help and suggestions.

:

A couple of possible queries:

SELECT [name],
MAX([training date]) As TrainedOn,
(SELECT mpg
FROM MileageLog AS ML2
WHERE ML2.[name] = ML1.[name]
AND [mpg date] =
(SELECT MAX([mpg date])
FROM MileageLog AS ML2
WHERE ML2.[name] = ML1.[name]))
AS CurrentMPG,
(SELECT mpg
FROM MileageLog AS ML2
WHERE ML2.[name] = ML1.[name]
AND [training date] =
(SELECT MAX([training date])
FROM MileageLog AS ML2
WHERE ML2.[name] = ML1.[name]))
AS PreviousMPG,
CurrentMPG - PreviousMPG AS Improvement
FROM MileageLog AS ML1
WHERE [training date] IS NOT NULL
GROUP BY [Name];

Or alternatively:

SELECT [name],
(SELECT MAX([training date])
FROM MileageLog AS ML2
WHERE ML2.[name] = ML1.[name])
AS TrainedOn,
mpg AS CurrentMPG,
(SELECT mpg
FROM MileageLog As ML2
WHERE ML2.[name] = ML1.[name]
AND [training date] =
(SELECT MAX([training date])
FROM MileageLog AS ML2
WHERE ML2.[name] = ML1.[name]))
AS PreviousMPG,
mpg - PreviousMPG AS Improvement
FROM MileageLog As ML1
WHERE [mpg date] =
(SELECT MAX([mpg date])
FROM MileageLog AS ML2
WHERE ML2.[name] = ML1.[name])
AND EXISTS
(SELECT *
FROM MileageLog AS ML2
WHERE ML2.[name] = ML1.[name]
AND ML2.[training date] IS NOT NULL);

Where MileageLog is the table name. I'd think the first query is probably
the more efficient, but both will give the same results.

A couple of supplementary points:

1. I'd recommend you don't use 'name' as a column name as it’s the name of
a built in property in Access. I recall a post whwre someone had done this
and the name of the report appeared in each row in a report rather than the
names of the individuals. You could use something like DriverName or
EmployeeName, but even better is to split the names. More on this in point 2
below.

2. Do not use names as keys or parts of a key. In your table the key is in
fact a composite one of name and training date. Names can be duplicated (I
worked with two Maggie Taylors once). Use a numeric value in a column
DriverID or EmployeeID instead. This can then reference a Drivers or
Employees table with an autonumber primary key of DriverID or EmployeeID.
For the names its best to have separate Firstname and Lastname columns. For
data input you can use an Employees form with a MilegeLog subform linked on
DriverID, in which case the relevant DriverID value will automatically be
inserted into the MileageLog table as you add a new record. For a report
you'd join the Drivers table to one of the above queries to include names in
report.

Ken Sheridan
Stafford, England

:

My table looks like this

name - mpg - mpg date - training date

Every emp has a record for each month tracking their mpg. The training date
remains empty until they get training which could be just once or once every
6 months. When they get trained, the training date goes in the most recent
record. A sampling of this table would be ...

joe 6.4 jul08 null
joe 6.2 aug08 9/3/08
joe 6.8 sep08 null
bob 7.4 jul08 null
bob 6.9 aug08 null
bob 6.8 sep08 null
ann 6.4 jul08 null
ann 6.5 aug08 9/15/08
ann 7.0 sep08 null

Here's what I'd like to do... if the driver had training, I'd like to
calculate the change in MPG from prior to their training to following their
training. I just can't seem to figure out how to do it. It doesn't matter
if the final output is a query or report. I'm beginning to think that I
should pull the training date out of the main table and put it in a seperate
table. Would that make this task easier.

Using the sample data above, my report/query would return the following only
the employees who had training and the difference in their mpg from the month
prior to training until after...

Joe trained on 9/3/08 6.8-6.2=.6
Ann trained on 9/15/08 7.0-6.5=.5
or simplified...
Joe .6
Ann .5

Thanks in advance for your help. Dj
Click to expand...
Click to expand...
Click to expand...
 
You've not grouped the query by driver, so it will only return the one row.
Try this:

SELECT [driver name],
MAX([completed training date]) AS TrainedOn,
(SELECT mpg FROM tblMPG AS ML2
WHERE ML2.[driver name] = ML1.[driver name]
AND [roster date] =
(SELECT MAX([roster date]) FROM tblMPG AS ML2
WHERE ML2.[driver name] = ML1.[driver name]))
AS CurrentMPG,
(SELECT mpg FROM tblMPG AS ML2
WHERE ML2.[driver name] = ML1.[driver name]
AND [completed training date] =
(SELECT MAX([completed training date])
FROM tblMPG AS ML2
WHERE ML2.[driver name] = ML1.[driver name]))
AS PreviousMPG,
CurrentMPG-PreviousMPG AS Improvement
FROM tblMPG AS ML1
WHERE [completed training date] IS NOT NULL
GROUP BY [driver name];

Ken Sheridan
Stafford, England

Dj said:
Update from previous post...
1. The "asking for [completed training date]" is fixed; it was a typo error.
2. Output is only returning one record w/ the most recent training date.

Also, I didn't mention this before and I don't know if it's important...
The [roster date] is imported at the end of the month and has the last date
of the month for each driver. Where the actual [completed training date] can
happen anytime during the month, the [roster date] will always be the 30th or
31st of the month. Trying to decifer your code, I don't think that makes a
difference here.

Dj said:
You have been so helpful, I hope you don't mind that I'm a little slow at
this and I have to keep bugging you. I've come so far with your help and I'd
hate to give up now. My actual code is copied below and here are the actual
table and field names.
name=driver name
mpg date=roster date
mileagelog=tblMGP
training date = completed training date

I think those are the only ones I had to change to match my actual db. I
noticed that when I view the design view of this SQL, it only shows a table
ML2. Is that the way it's supposed to work? No ML1 should show? Also, when
I run the query, it's asking me to input the 'completed training date'.

Because I have no experience working with the SQL code of querys, I'm a
little lost.

Here's my code after modifying what you sent me...

SELECT Max([completed training date]) AS TrainedOn, (SELECT mpg FROM tblMPG
AS ML2
WHERE ML2.[driver name] = ML1.[driver name]
AND [roster date] =
(SELECT MAX([roster date]) FROM tblMPG AS ML2
WHERE ML2.[driver name] = ML1.[driver name])) AS CurrentMPG,
(SELECT mpg FROM tblMPG AS ML2
WHERE ML2.[driver name] = ML1.[driver name]
AND [completed training date] =(SELECT MAX([completed training date])
FROM tblMPG AS ML2
WHERE ML2.[driver name] = ML1.[driver name])) AS PreviousMPG,
CurrentMPG-PreviousMPG AS Improvement
FROM tblMPG AS ML1
WHERE ((([completed training date]) Is Not Null));



Ken Sheridan said:
Both solutions are the SQL (structured query language) for queries. Open the
query designer but don't add any table. Switch to SQL view and paste in one
or other of the solutions. Amend the SQL as necessary so that the table and
column (field) names exactly match you own. Remember that any table or field
names with spaces or other special characters in them have to be in square
brackets, e.g. [training date]. If in doubt put the brackets in.

If the query works OK you can save it and use it as the RecordSource of a
report or form. If you've followed my advice and used a numeric DriverID in
place of 'name' and a separate Drivers table, you can join the table to the
query in another query on the DriverID columns, and then include columns from
both the table and query in the final query. You can do this in design view.

The way the queries work is by using subqueries (the various SELECT…
statements in parentheses) to compute the relevant values. These subqueries
are correlated with the main 'outer' query on the name columns and you'll see
that the separate instances of the MileageLog table are identified by giving
then aliases ML1 and ML2, which allows them to be correlated so that each
subquery looks at only those rows for the current 'name'.

Ken Sheridan
Stafford, England

:

Thank you so much for the help and suggestions.
A couple of follow ups...
1. Where would this code go?
2. I understand I have to build a code expression, but I'm not sure
where to put it?
3. Does it need to be In a query or a report or will either work?
4. Would it go in a new non-bound text field or a bounded text field?

Thanks again for the help and suggestions.

:

A couple of possible queries:

SELECT [name],
MAX([training date]) As TrainedOn,
(SELECT mpg
FROM MileageLog AS ML2
WHERE ML2.[name] = ML1.[name]
AND [mpg date] =
(SELECT MAX([mpg date])
FROM MileageLog AS ML2
WHERE ML2.[name] = ML1.[name]))
AS CurrentMPG,
(SELECT mpg
FROM MileageLog AS ML2
WHERE ML2.[name] = ML1.[name]
AND [training date] =
(SELECT MAX([training date])
FROM MileageLog AS ML2
WHERE ML2.[name] = ML1.[name]))
AS PreviousMPG,
CurrentMPG - PreviousMPG AS Improvement
FROM MileageLog AS ML1
WHERE [training date] IS NOT NULL
GROUP BY [Name];

Or alternatively:

SELECT [name],
(SELECT MAX([training date])
FROM MileageLog AS ML2
WHERE ML2.[name] = ML1.[name])
AS TrainedOn,
mpg AS CurrentMPG,
(SELECT mpg
FROM MileageLog As ML2
WHERE ML2.[name] = ML1.[name]
AND [training date] =
(SELECT MAX([training date])
FROM MileageLog AS ML2
WHERE ML2.[name] = ML1.[name]))
AS PreviousMPG,
mpg - PreviousMPG AS Improvement
FROM MileageLog As ML1
WHERE [mpg date] =
(SELECT MAX([mpg date])
FROM MileageLog AS ML2
WHERE ML2.[name] = ML1.[name])
AND EXISTS
(SELECT *
FROM MileageLog AS ML2
WHERE ML2.[name] = ML1.[name]
AND ML2.[training date] IS NOT NULL);

Where MileageLog is the table name. I'd think the first query is probably
the more efficient, but both will give the same results.

A couple of supplementary points:

1. I'd recommend you don't use 'name' as a column name as it’s the name of
a built in property in Access. I recall a post whwre someone had done this
and the name of the report appeared in each row in a report rather than the
names of the individuals. You could use something like DriverName or
EmployeeName, but even better is to split the names. More on this in point 2
below.

2. Do not use names as keys or parts of a key. In your table the key is in
fact a composite one of name and training date. Names can be duplicated (I
worked with two Maggie Taylors once). Use a numeric value in a column
DriverID or EmployeeID instead. This can then reference a Drivers or
Employees table with an autonumber primary key of DriverID or EmployeeID.
For the names its best to have separate Firstname and Lastname columns. For
data input you can use an Employees form with a MilegeLog subform linked on
DriverID, in which case the relevant DriverID value will automatically be
inserted into the MileageLog table as you add a new record. For a report
you'd join the Drivers table to one of the above queries to include names in
report.

Ken Sheridan
Stafford, England

:

My table looks like this

name - mpg - mpg date - training date

Every emp has a record for each month tracking their mpg. The training date
remains empty until they get training which could be just once or once every
6 months. When they get trained, the training date goes in the most recent
record. A sampling of this table would be ...

joe 6.4 jul08 null
joe 6.2 aug08 9/3/08
joe 6.8 sep08 null
bob 7.4 jul08 null
bob 6.9 aug08 null
bob 6.8 sep08 null
ann 6.4 jul08 null
ann 6.5 aug08 9/15/08
ann 7.0 sep08 null

Here's what I'd like to do... if the driver had training, I'd like to
calculate the change in MPG from prior to their training to following their
training. I just can't seem to figure out how to do it. It doesn't matter
if the final output is a query or report. I'm beginning to think that I
should pull the training date out of the main table and put it in a seperate
table. Would that make this task easier.

Using the sample data above, my report/query would return the following only
the employees who had training and the difference in their mpg from the month
prior to training until after...

Joe trained on 9/3/08 6.8-6.2=.6
Ann trained on 9/15/08 7.0-6.5=.5
or simplified...
Joe .6
Ann .5

Thanks in advance for your help. Dj
Click to expand...
Click to expand...
Click to expand...
 
Hi Ken,

I'm back. You code is still working fine, however, becuase I don't totally
understand how it works, I still need help with one more thing (if you don't
mind).

In the query you wrote for me, you created what I'm calling temporary fields
and one of them is called improvement. I'm now want to create a report based
on the query and I want to include a max and min of the improvment showing
the highest improvment and the least improvement as well as a count of the
records and an average MPG of the drivers from the report.
When I do my Report Wizard, I don't get a summary option. Is that because
"improvement" and the two MPG fields are calculated fields that are not in
any table? Is there a way to get a Max and Min of the the Improvment field
and the count and average mpg all the records my report put out.

As always, thanks in advance for your help. dj
 
Hi Ken,

I'm back. You code is still working fine, however, becuase I don't totally
understand how it works, I still need help with one more thing (if you don't
mind).

In the query you wrote for me, you created what I'm calling temporary fields
and one of them is called improvement. I'm now want to create a report based
on the query and I want to include a max and min of the improvment showing
the highest improvment and the least improvement as well as a count of the
records and an average MPG of the drivers from the report.
When I do my Report Wizard, I don't get a summary option. Is that because
"improvement" and the two MPG fields are calculated fields that are not in
any table? Is there a way to get a Max and Min of the the Improvment field
and the count and average mpg all the records my report put out.

As always, thanks in advance for your help. Dj

P.S. When I look at the query you created for me, I see a table called ML.
I've been referring to it as a virtual table. What is the actual technique
called so I can look it up and learn more about it? Thanks!


Ken Sheridan said:
You've not grouped the query by driver, so it will only return the one row.
Try this:

SELECT [driver name],
MAX([completed training date]) AS TrainedOn,
(SELECT mpg FROM tblMPG AS ML2
WHERE ML2.[driver name] = ML1.[driver name]
AND [roster date] =
(SELECT MAX([roster date]) FROM tblMPG AS ML2
WHERE ML2.[driver name] = ML1.[driver name]))
AS CurrentMPG,
(SELECT mpg FROM tblMPG AS ML2
WHERE ML2.[driver name] = ML1.[driver name]
AND [completed training date] =
(SELECT MAX([completed training date])
FROM tblMPG AS ML2
WHERE ML2.[driver name] = ML1.[driver name]))
AS PreviousMPG,
CurrentMPG-PreviousMPG AS Improvement
FROM tblMPG AS ML1
WHERE [completed training date] IS NOT NULL
GROUP BY [driver name];

Ken Sheridan
Stafford, England

Dj said:
Update from previous post...
1. The "asking for [completed training date]" is fixed; it was a typo error.
2. Output is only returning one record w/ the most recent training date.

Also, I didn't mention this before and I don't know if it's important...
The [roster date] is imported at the end of the month and has the last date
of the month for each driver. Where the actual [completed training date] can
happen anytime during the month, the [roster date] will always be the 30th or
31st of the month. Trying to decifer your code, I don't think that makes a
difference here.

Dj said:
You have been so helpful, I hope you don't mind that I'm a little slow at
this and I have to keep bugging you. I've come so far with your help and I'd
hate to give up now. My actual code is copied below and here are the actual
table and field names.
name=driver name
mpg date=roster date
mileagelog=tblMGP
training date = completed training date

I think those are the only ones I had to change to match my actual db. I
noticed that when I view the design view of this SQL, it only shows a table
ML2. Is that the way it's supposed to work? No ML1 should show? Also, when
I run the query, it's asking me to input the 'completed training date'.

Because I have no experience working with the SQL code of querys, I'm a
little lost.

Here's my code after modifying what you sent me...

SELECT Max([completed training date]) AS TrainedOn, (SELECT mpg FROM tblMPG
AS ML2
WHERE ML2.[driver name] = ML1.[driver name]
AND [roster date] =
(SELECT MAX([roster date]) FROM tblMPG AS ML2
WHERE ML2.[driver name] = ML1.[driver name])) AS CurrentMPG,
(SELECT mpg FROM tblMPG AS ML2
WHERE ML2.[driver name] = ML1.[driver name]
AND [completed training date] =(SELECT MAX([completed training date])
FROM tblMPG AS ML2
WHERE ML2.[driver name] = ML1.[driver name])) AS PreviousMPG,
CurrentMPG-PreviousMPG AS Improvement
FROM tblMPG AS ML1
WHERE ((([completed training date]) Is Not Null));



:

Both solutions are the SQL (structured query language) for queries. Open the
query designer but don't add any table. Switch to SQL view and paste in one
or other of the solutions. Amend the SQL as necessary so that the table and
column (field) names exactly match you own. Remember that any table or field
names with spaces or other special characters in them have to be in square
brackets, e.g. [training date]. If in doubt put the brackets in.

If the query works OK you can save it and use it as the RecordSource of a
report or form. If you've followed my advice and used a numeric DriverID in
place of 'name' and a separate Drivers table, you can join the table to the
query in another query on the DriverID columns, and then include columns from
both the table and query in the final query. You can do this in design view.

The way the queries work is by using subqueries (the various SELECT…
statements in parentheses) to compute the relevant values. These subqueries
are correlated with the main 'outer' query on the name columns and you'll see
that the separate instances of the MileageLog table are identified by giving
then aliases ML1 and ML2, which allows them to be correlated so that each
subquery looks at only those rows for the current 'name'.

Ken Sheridan
Stafford, England

:

Thank you so much for the help and suggestions.
A couple of follow ups...
1. Where would this code go?
2. I understand I have to build a code expression, but I'm not sure
where to put it?
3. Does it need to be In a query or a report or will either work?
4. Would it go in a new non-bound text field or a bounded text field?

Thanks again for the help and suggestions.

:

A couple of possible queries:

SELECT [name],
MAX([training date]) As TrainedOn,
(SELECT mpg
FROM MileageLog AS ML2
WHERE ML2.[name] = ML1.[name]
AND [mpg date] =
(SELECT MAX([mpg date])
FROM MileageLog AS ML2
WHERE ML2.[name] = ML1.[name]))
AS CurrentMPG,
(SELECT mpg
FROM MileageLog AS ML2
WHERE ML2.[name] = ML1.[name]
AND [training date] =
(SELECT MAX([training date])
FROM MileageLog AS ML2
WHERE ML2.[name] = ML1.[name]))
AS PreviousMPG,
CurrentMPG - PreviousMPG AS Improvement
FROM MileageLog AS ML1
WHERE [training date] IS NOT NULL
GROUP BY [Name];

Or alternatively:

SELECT [name],
(SELECT MAX([training date])
FROM MileageLog AS ML2
WHERE ML2.[name] = ML1.[name])
AS TrainedOn,
mpg AS CurrentMPG,
(SELECT mpg
FROM MileageLog As ML2
WHERE ML2.[name] = ML1.[name]
AND [training date] =
(SELECT MAX([training date])
FROM MileageLog AS ML2
WHERE ML2.[name] = ML1.[name]))
AS PreviousMPG,
mpg - PreviousMPG AS Improvement
FROM MileageLog As ML1
WHERE [mpg date] =
(SELECT MAX([mpg date])
FROM MileageLog AS ML2
WHERE ML2.[name] = ML1.[name])
AND EXISTS
(SELECT *
FROM MileageLog AS ML2
WHERE ML2.[name] = ML1.[name]
AND ML2.[training date] IS NOT NULL);

Where MileageLog is the table name. I'd think the first query is probably
the more efficient, but both will give the same results.

A couple of supplementary points:

1. I'd recommend you don't use 'name' as a column name as it’s the name of
a built in property in Access. I recall a post whwre someone had done this
and the name of the report appeared in each row in a report rather than the
names of the individuals. You could use something like DriverName or
EmployeeName, but even better is to split the names. More on this in point 2
below.

2. Do not use names as keys or parts of a key. In your table the key is in
fact a composite one of name and training date. Names can be duplicated (I
worked with two Maggie Taylors once). Use a numeric value in a column
DriverID or EmployeeID instead. This can then reference a Drivers or
Employees table with an autonumber primary key of DriverID or EmployeeID.
For the names its best to have separate Firstname and Lastname columns. For
data input you can use an Employees form with a MilegeLog subform linked on
DriverID, in which case the relevant DriverID value will automatically be
inserted into the MileageLog table as you add a new record. For a report
you'd join the Drivers table to one of the above queries to include names in
report.

Ken Sheridan
Stafford, England

:

My table looks like this

name - mpg - mpg date - training date

Every emp has a record for each month tracking their mpg. The training date
remains empty until they get training which could be just once or once every
6 months. When they get trained, the training date goes in the most recent
record. A sampling of this table would be ...

joe 6.4 jul08 null
joe 6.2 aug08 9/3/08
joe 6.8 sep08 null
bob 7.4 jul08 null
bob 6.9 aug08 null
bob 6.8 sep08 null
ann 6.4 jul08 null
ann 6.5 aug08 9/15/08
ann 7.0 sep08 null

Here's what I'd like to do... if the driver had training, I'd like to
calculate the change in MPG from prior to their training to following their
training. I just can't seem to figure out how to do it. It doesn't matter
if the final output is a query or report. I'm beginning to think that I
should pull the training date out of the main table and put it in a seperate
table. Would that make this task easier.

Using the sample data above, my report/query would return the following only
the employees who had training and the difference in their mpg from the month
prior to training until after...

Joe trained on 9/3/08 6.8-6.2=.6
Ann trained on 9/15/08 7.0-6.5=.5
or simplified...
Joe .6
Ann .5

Thanks in advance for your help. Dj
Click to expand...
Click to expand...
Click to expand...
 
Back
Top