Annual count queries on a field relative to a value that varies DA

[tobesus]

Hi. I'm still learning Access and this is the toughest problem I've faced yet.
Will try to be as clear as possible.

I have a table (YrOpTEMPS) consisting of the fields: DateTime and OpTEMP

The data are operating temperatures (OpTEMP) of a machine sampled every 2
minutes over a year. I am trying to analyze how much time this machine has
spent operating at a given target temperature (and target temp - 1 degree,
target-2 degrees, etc) during this year.

Thus I would like my final data to be COUNTS of OpTEMPs at the target temp,
the target temp minus 1 degree, the target temp -2 degrees, etc. for the
entire year.

My problem is that this target temperature is different for EACH DAY of the
year!

Target temps come from another table with a field (TargTEMP) listing one
target temp for each DAY (ie: there is only one row per DAY in this table,
NOT one row per sample like in the master table).

Somehow I need to develop a query (or queries) that will take the TargTemp
for each day and use it to interrogate ALL the samples (OpTEMP) for that
particular day, COUNT how many fall within the target temp, target temp minus
1 degree, minus 2, etc. and then give me those counts for the entire year.

To clarify, the counts need to be done on a daily basis (since target temp
varies daily), but only the summed counts for the year are of interest to me.

If anyone has any advice on this one I would be very grateful. Just
formulating this question has taxed every brain cell I have to the point of
exhaustion. Thanks in advance.

 

[Nikos Yannacopoulos]

Tobesus,

Start by making a query on table YrOpTEMPS which includes the OpTEMP
field and a calculated field on the date/time field which returns the
date part only, as:

SampleDate: DateValue([DateTime])

Save it as, say, qryOpTEMPS.
Now make a second query adding the previously saved query qryOpTEMPS and
the target temps table, joining the calculated field in the query
(SampleDate) on the date field in the targets table. Now you need a
cacluclated field like:

TempDiff: [OpTEMP] - [TargTEMP]

This query will return op temp variances from the (daily) target. The
next step is to make the query count the -2's, -1's, 0's, +1's etc. To
do this, drag the SampeDate field down at the grid, then go View >
Totals and notice the new row atht appears in the grid, labelled Total:,
and defaulted to Group By under both fields; leave the TempDiff field to
the default setting, and change the setting under SampleDate to Count.
Now switch to datasheet view and see what you get!

HTH,
Nikos

 

[tobesus]

Excellent. Thanks so much. I've done the first step and it worked great. ie
- I have generated the qryOpTemps query.

I'm SO CLOSE but am running into problems on the second step. Specifically:

I now have two queries:

1. is the qryOpTemps you showed me how to generate with two fields:
[SampleDate] and [OpTemp]. This query contains ~240,000 rows of data - a
sample every two minutes for a year.

2. is the TargetTemp query (I mistakenly called it a table before) which
contains the fields: [Date] and [TargTemp]. This query has 365 rows (one per
day).

So I'm trying to run a third query to generate the TempDiff field you
described, but it just won't seem to let me join queries 1 and 2 above in any
way. It keeps giving me a "Type mismatch in expression" error in Design View
or SQL. And if I try to use the query wizard it immediately tells me that
I've "chosen records from fields that the wizard cannot connect."

It also implies that I'm trying to connect fields from a table and a query
based on that table, but I'm not. I'm trying to connect fields from two
queries, both based on the same table.

UPDATE: I've just made a copy of the original table that queries 1 and 2
were based on, and have based query 2 on the copy. Thus I am now trying to
connect fields from 2 queries based on two SEPARATE tables, and I'm getting
the exact same results.

Any idea what I might be doing wrong?

Is it a problem that the two queries I'm trying to join have differing #s of
data rows?

Something else? Aaaaaaaaaahhhhhh!!!!

Tobesus,

Start by making a query on table YrOpTEMPS which includes the OpTEMP
field and a calculated field on the date/time field which returns the
date part only, as:

SampleDate: DateValue([DateTime])

Save it as, say, qryOpTEMPS.
Now make a second query adding the previously saved query qryOpTEMPS and
the target temps table, joining the calculated field in the query
(SampleDate) on the date field in the targets table. Now you need a
cacluclated field like:

TempDiff: [OpTEMP] - [TargTEMP]

This query will return op temp variances from the (daily) target. The
next step is to make the query count the -2's, -1's, 0's, +1's etc. To
do this, drag the SampeDate field down at the grid, then go View >
Totals and notice the new row atht appears in the grid, labelled Total:,
and defaulted to Group By under both fields; leave the TempDiff field to
the default setting, and change the setting under SampleDate to Count.
Now switch to datasheet view and see what you get!

HTH,
Nikos

Hi. I'm still learning Access and this is the toughest problem I've faced yet.
Will try to be as clear as possible.

I have a table (YrOpTEMPS) consisting of the fields: DateTime and OpTEMP

The data are operating temperatures (OpTEMP) of a machine sampled every 2
minutes over a year. I am trying to analyze how much time this machine has
spent operating at a given target temperature (and target temp - 1 degree,
target-2 degrees, etc) during this year.

Thus I would like my final data to be COUNTS of OpTEMPs at the target temp,
the target temp minus 1 degree, the target temp -2 degrees, etc. for the
entire year.

My problem is that this target temperature is different for EACH DAY of the
year!

Target temps come from another table with a field (TargTEMP) listing one
target temp for each DAY (ie: there is only one row per DAY in this table,
NOT one row per sample like in the master table).

Somehow I need to develop a query (or queries) that will take the TargTemp
for each day and use it to interrogate ALL the samples (OpTEMP) for that
particular day, COUNT how many fall within the target temp, target temp minus
1 degree, minus 2, etc. and then give me those counts for the entire year.

To clarify, the counts need to be done on a daily basis (since target temp
varies daily), but only the summed counts for the year are of interest to me.

If anyone has any advice on this one I would be very grateful. Just
formulating this question has taxed every brain cell I have to the point of
exhaustion. Thanks in advance.

 

[Nikos Yannacopoulos]

Tobesus,

See my comments within your post.

Regards,
Nikos

Excellent. Thanks so much. I've done the first step and it worked great. ie
- I have generated the qryOpTemps query.

I'm SO CLOSE but am running into problems on the second step. Specifically:

I now have two queries:

1. is the qryOpTemps you showed me how to generate with two fields:
[SampleDate] and [OpTemp]. This query contains ~240,000 rows of data - a
sample every two minutes for a year.

2. is the TargetTemp query (I mistakenly called it a table before)

No problem, this shouldn't change anything.

which contains the fields: [Date] and [TargTemp]. This query has 365 rows
(one per day).

I would suggest you change the name of the date field to something
different, say, TDate (T for target); Date is a reserved keyword in
Access (a built-in function which returns the system date), and using it
as a field name may get you into trouble.

So I'm trying to run a third query to generate the TempDiff field you
described, but it just won't seem to let me join queries 1 and 2 above in any
way. It keeps giving me a "Type mismatch in expression" error in Design View
or SQL. And if I try to use the query wizard it immediately tells me that
I've "chosen records from fields that the wizard cannot connect."

Most likely the date field in your target temps table on which the
TargetTemp query is based is not a Date/Time field (I would guess it's
text). If that's indeed the case, then in the TargetTemp query use a
calculated field with DateValue on it, just like you did in the other
query. This should solve the problem.

It also implies that I'm trying to connect fields from a table and a query
based on that table, but I'm not. I'm trying to connect fields from two
queries, both based on the same table.

This might be a problem. If the above doesn't solve it, then you might
have to create a separate table for the target temps. If required, give
me the name of the original table and the fields in it, and I'll help
you with it.

UPDATE: I've just made a copy of the original table that queries 1 and 2
were based on, and have based query 2 on the copy. Thus I am now trying to
connect fields from 2 queries based on two SEPARATE tables, and I'm getting
the exact same results.

That's most likely due to the field type incompatibility.

Any idea what I might be doing wrong?

It might be worth revisiting your whole design. Is all the data in just
a single table? If yes then you should! Again, let me know what the
table structure is.

Is it a problem that the two queries I'm trying to join have differing #s of
data rows?

Something else? Aaaaaaaaaahhhhhh!!!!


:

Tobesus,

Start by making a query on table YrOpTEMPS which includes the OpTEMP
field and a calculated field on the date/time field which returns the
date part only, as:

SampleDate: DateValue([DateTime])

Save it as, say, qryOpTEMPS.
Now make a second query adding the previously saved query qryOpTEMPS and
the target temps table, joining the calculated field in the query
(SampleDate) on the date field in the targets table. Now you need a
cacluclated field like:

TempDiff: [OpTEMP] - [TargTEMP]

This query will return op temp variances from the (daily) target. The
next step is to make the query count the -2's, -1's, 0's, +1's etc. To
do this, drag the SampeDate field down at the grid, then go View >
Totals and notice the new row atht appears in the grid, labelled Total:,
and defaulted to Group By under both fields; leave the TempDiff field to
the default setting, and change the setting under SampleDate to Count.
Now switch to datasheet view and see what you get!

HTH,
Nikos

Hi. I'm still learning Access and this is the toughest problem I've faced yet.
Will try to be as clear as possible.

I have a table (YrOpTEMPS) consisting of the fields: DateTime and OpTEMP

The data are operating temperatures (OpTEMP) of a machine sampled every 2
minutes over a year. I am trying to analyze how much time this machine has
spent operating at a given target temperature (and target temp - 1 degree,
target-2 degrees, etc) during this year.

Thus I would like my final data to be COUNTS of OpTEMPs at the target temp,
the target temp minus 1 degree, the target temp -2 degrees, etc. for the
entire year.

My problem is that this target temperature is different for EACH DAY of the
year!

Target temps come from another table with a field (TargTEMP) listing one
target temp for each DAY (ie: there is only one row per DAY in this table,
NOT one row per sample like in the master table).

Somehow I need to develop a query (or queries) that will take the TargTemp
for each day and use it to interrogate ALL the samples (OpTEMP) for that
particular day, COUNT how many fall within the target temp, target temp minus
1 degree, minus 2, etc. and then give me those counts for the entire year.

To clarify, the counts need to be done on a daily basis (since target temp
varies daily), but only the summed counts for the year are of interest to me.

If anyone has any advice on this one I would be very grateful. Just
formulating this question has taxed every brain cell I have to the point of
exhaustion. Thanks in advance.

 

[tobesus]

Wow, thanks again Nikos. I'm still struggling here.

THe date values in the two queries I'm trying to join were both in date
format, but one was generated using the DateValue[DATE/TIME] command you
suggested whereas the other was generated using a DATE,'m/d/yyyy' script. I
changed them so that both are generated by DateValue.

When I changed this and tried to run my query, it actually started to run
but then came up with the "type mismatch" error again. Also a quick test with
the query wizard still won't allow me to connect any records from the two
queries.

So now I'm thinking that the problem must be that I'm trying to generate a
field based on cacluations between one field [OpTemps] with 240,000 points
and another [TargTemp] with only 366 points. I would have thought access
could do this somehow.

If that is the problem, then I guess I need to somehow populate a new field
which takes my [TargTemp] values (currently one per day) and expands them to
740 values a day so they'll be on a one-to-one ratio with my [OpTemps]? Not
sure how to do that and I had hoped it wouldn't be necessary.

Table Structure:
All my original data was in one Table: [DataFromExcel] and I was running all
queries from that. In an effort to get around this current problem though, I
made a copy of that table, called [DataFromExcelCopy] and ran the TargTemp
query off of that so I wouldn't have a compatibility problem with the
QryOpTemps query. Unfortunately it's made no difference.

Not sure if it helps, but here's the SQL of the query I keep trying to run
to join queries 1 nad 2 and generate the TempDiff field:

SELECT TargetTemp![TargTEMP]-QryOpTemps![OpTemp] AS TempDiff,
QryOpTemps.SampleDate
FROM TargetTemp INNER JOIN QryOpTemps ON TargetTemp.TDate =
QryOpTemps.SampleDate;


I'm at a complete loss on what to try next. Thanks for sticking with me on
this. Sorry we're not in the same time zone!

Tobesus,

See my comments within your post.

Regards,
Nikos

Excellent. Thanks so much. I've done the first step and it worked great. ie
- I have generated the qryOpTemps query.

I'm SO CLOSE but am running into problems on the second step. Specifically:

I now have two queries:

1. is the qryOpTemps you showed me how to generate with two fields:
[SampleDate] and [OpTemp]. This query contains ~240,000 rows of data - a
sample every two minutes for a year.

2. is the TargetTemp query (I mistakenly called it a table before)

No problem, this shouldn't change anything.

which contains the fields: [Date] and [TargTemp]. This query has 365 rows
(one per day).

I would suggest you change the name of the date field to something
different, say, TDate (T for target); Date is a reserved keyword in
Access (a built-in function which returns the system date), and using it
as a field name may get you into trouble.

So I'm trying to run a third query to generate the TempDiff field you
described, but it just won't seem to let me join queries 1 and 2 above in any
way. It keeps giving me a "Type mismatch in expression" error in Design View
or SQL. And if I try to use the query wizard it immediately tells me that
I've "chosen records from fields that the wizard cannot connect."

Most likely the date field in your target temps table on which the
TargetTemp query is based is not a Date/Time field (I would guess it's
text). If that's indeed the case, then in the TargetTemp query use a
calculated field with DateValue on it, just like you did in the other
query. This should solve the problem.

It also implies that I'm trying to connect fields from a table and a query
based on that table, but I'm not. I'm trying to connect fields from two
queries, both based on the same table.

This might be a problem. If the above doesn't solve it, then you might
have to create a separate table for the target temps. If required, give
me the name of the original table and the fields in it, and I'll help
you with it.

UPDATE: I've just made a copy of the original table that queries 1 and 2
were based on, and have based query 2 on the copy. Thus I am now trying to
connect fields from 2 queries based on two SEPARATE tables, and I'm getting
the exact same results.

That's most likely due to the field type incompatibility.

Any idea what I might be doing wrong?

It might be worth revisiting your whole design. Is all the data in just
a single table? If yes then you should! Again, let me know what the
table structure is.

Is it a problem that the two queries I'm trying to join have differing #s of
data rows?

Something else? Aaaaaaaaaahhhhhh!!!!


:

Tobesus,

Start by making a query on table YrOpTEMPS which includes the OpTEMP
field and a calculated field on the date/time field which returns the
date part only, as:

SampleDate: DateValue([DateTime])

Save it as, say, qryOpTEMPS.
Now make a second query adding the previously saved query qryOpTEMPS and
the target temps table, joining the calculated field in the query
(SampleDate) on the date field in the targets table. Now you need a
cacluclated field like:

TempDiff: [OpTEMP] - [TargTEMP]

This query will return op temp variances from the (daily) target. The
next step is to make the query count the -2's, -1's, 0's, +1's etc. To
do this, drag the SampeDate field down at the grid, then go View >
Totals and notice the new row atht appears in the grid, labelled Total:,
and defaulted to Group By under both fields; leave the TempDiff field to
the default setting, and change the setting under SampleDate to Count.
Now switch to datasheet view and see what you get!

HTH,
Nikos

tobesus wrote:

Hi. I'm still learning Access and this is the toughest problem I've faced yet.
Will try to be as clear as possible.

I have a table (YrOpTEMPS) consisting of the fields: DateTime and OpTEMP

The data are operating temperatures (OpTEMP) of a machine sampled every 2
minutes over a year. I am trying to analyze how much time this machine has
spent operating at a given target temperature (and target temp - 1 degree,
target-2 degrees, etc) during this year.

Thus I would like my final data to be COUNTS of OpTEMPs at the target temp,
the target temp minus 1 degree, the target temp -2 degrees, etc. for the
entire year.

My problem is that this target temperature is different for EACH DAY of the
year!

Target temps come from another table with a field (TargTEMP) listing one
target temp for each DAY (ie: there is only one row per DAY in this table,
NOT one row per sample like in the master table).

Somehow I need to develop a query (or queries) that will take the TargTemp
for each day and use it to interrogate ALL the samples (OpTEMP) for that
particular day, COUNT how many fall within the target temp, target temp minus
1 degree, minus 2, etc. and then give me those counts for the entire year.

To clarify, the counts need to be done on a daily basis (since target temp
varies daily), but only the summed counts for the year are of interest to me.

If anyone has any advice on this one I would be very grateful. Just
formulating this question has taxed every brain cell I have to the point of
exhaustion. Thanks in advance.

 

[Nikos Yannacopoulos]

Wow, thanks again Nikos. I'm still struggling here.

THe date values in the two queries I'm trying to join were both in date
format, but one was generated using the DateValue[DATE/TIME] command you
suggested whereas the other was generated using a DATE,'m/d/yyyy' script. I
changed them so that both are generated by DateValue.

When I changed this and tried to run my query, it actually started to run
but then came up with the "type mismatch" error again.

Any chance there is some bad data in there in one or more records?
Something in the date field that is invalid as a date? I appreciate it's
hard to check 240k records, but you could try to sort on the date
field in a query, in which case something abnormal should come wither at
the top or the bottom.

Also a quick test with
the query wizard still won't allow me to connect any records from the two
queries.

Because they're both based on the same table? What is the error message?

So now I'm thinking that the problem must be that I'm trying to generate a
field based on cacluations between one field [OpTemps] with 240,000 points
and another [TargTemp] with only 366 points. I would have thought access
could do this somehow.

This is not the problem.

If that is the problem, then I guess I need to somehow populate a new field
which takes my [TargTemp] values (currently one per day) and expands them to
740 values a day so they'll be on a one-to-one ratio with my [OpTemps]? Not
sure how to do that and I had hoped it wouldn't be necessary.

No. The whole idea of relational databases is based on one-to-many
relationships like this ( one target to many readings per day).

Table Structure:
All my original data was in one Table: [DataFromExcel] and I was running all
queries from that. In an effort to get around this current problem though, I
made a copy of that table, called [DataFromExcelCopy] and ran the TargTemp
query off of that so I wouldn't have a compatibility problem with the
QryOpTemps query. Unfortunately it's made no difference.

That's like fighting the symptoms of a disease rather than its cause;
the disease here is the flat table, which may be OK in Excel, but
completely wrong in Access. The way to go is to split your table in two
tables, one for targets and one for actual readings. This is called
"normalizing" your currently "non-normalized" data, in case you've come
across the term. Then the query on the two will work as you want it.

Next step:

Make a query on the original table, including a DateValue on the date
field and the target temp field; use Totals to group by date (so you
should be getting one record per date); now make this a make-table
query, so you create a tblTargetTemp (or something...).

Make another query on the original table, including the original
date/time field (so you don't lose the time data), plus a DateValue on
it in a separate field (so you have the date separately, and in the
correct format) and the OpTemp field; make it a make-table query, so you
create a tblOpTemps (or something...).

Now you can proceed with the queries I suggested originally, on the two
new tables. When you are happy with the whole thing, you can delete the
original table altogether.

If you are still having problems, you are welcome to mail me a copy of
your database so I can have a look, just before you do delete the bulk
of the data leaving just a few days' worth, compact and repair and (if
possible) zip.

HTH,
Nikos

Not sure if it helps, but here's the SQL of the query I keep trying to run
to join queries 1 nad 2 and generate the TempDiff field:

SELECT TargetTemp![TargTEMP]-QryOpTemps![OpTemp] AS TempDiff,
QryOpTemps.SampleDate
FROM TargetTemp INNER JOIN QryOpTemps ON TargetTemp.TDate =
QryOpTemps.SampleDate;


I'm at a complete loss on what to try next. Thanks for sticking with me on
this. Sorry we're not in the same time zone!

 

[tobesus]

Thanks Nikos!!! Believe it or not, you hit on the problem with your first
try on this post. I did a "sort ascending" and "descending" on one of my
first two queries and found TWO records out of 240,000 that were screwing the
whole thing up. Took them out and the query ran great! Problem solved!

Now I just need to get those counts. I followed the instructions you gave
for that a couple posts ago and they worked great except for one problem. My
TempDiff values are not nice whole numbers (like -1, -2) but numbers with
several decimal places.

THus I need to have it give me a count within a given range. For instance
"Targtemp -1" would equal all values between -0.500 and -1.499 and so forth.

I know just enough to do this with an additional query and for one temp
range at a time, but that doesn't really cut it. Obciously the ideal would be
for it to give me the counts for all temp ranges in one query, and have the
sum of all counts equal to the original number of datapoints.

Surely we can't be more than one post away from the solution. Thanks again
for all your help!

Wow, thanks again Nikos. I'm still struggling here.

THe date values in the two queries I'm trying to join were both in date
format, but one was generated using the DateValue[DATE/TIME] command you
suggested whereas the other was generated using a DATE,'m/d/yyyy' script. I
changed them so that both are generated by DateValue.

When I changed this and tried to run my query, it actually started to run
but then came up with the "type mismatch" error again.

Any chance there is some bad data in there in one or more records?
Something in the date field that is invalid as a date? I appreciate it's
hard to check 240k records, but you could try to sort on the date
field in a query, in which case something abnormal should come wither at
the top or the bottom.

Also a quick test with
the query wizard still won't allow me to connect any records from the two
queries.

Because they're both based on the same table? What is the error message?

So now I'm thinking that the problem must be that I'm trying to generate a
field based on cacluations between one field [OpTemps] with 240,000 points
and another [TargTemp] with only 366 points. I would have thought access
could do this somehow.

This is not the problem.

If that is the problem, then I guess I need to somehow populate a new field
which takes my [TargTemp] values (currently one per day) and expands them to
740 values a day so they'll be on a one-to-one ratio with my [OpTemps]? Not
sure how to do that and I had hoped it wouldn't be necessary.

No. The whole idea of relational databases is based on one-to-many
relationships like this ( one target to many readings per day).

Table Structure:
All my original data was in one Table: [DataFromExcel] and I was running all
queries from that. In an effort to get around this current problem though, I
made a copy of that table, called [DataFromExcelCopy] and ran the TargTemp
query off of that so I wouldn't have a compatibility problem with the
QryOpTemps query. Unfortunately it's made no difference.

That's like fighting the symptoms of a disease rather than its cause;
the disease here is the flat table, which may be OK in Excel, but
completely wrong in Access. The way to go is to split your table in two
tables, one for targets and one for actual readings. This is called
"normalizing" your currently "non-normalized" data, in case you've come
across the term. Then the query on the two will work as you want it.

Next step:

Make a query on the original table, including a DateValue on the date
field and the target temp field; use Totals to group by date (so you
should be getting one record per date); now make this a make-table
query, so you create a tblTargetTemp (or something...).

Make another query on the original table, including the original
date/time field (so you don't lose the time data), plus a DateValue on
it in a separate field (so you have the date separately, and in the
correct format) and the OpTemp field; make it a make-table query, so you
create a tblOpTemps (or something...).

Now you can proceed with the queries I suggested originally, on the two
new tables. When you are happy with the whole thing, you can delete the
original table altogether.

If you are still having problems, you are welcome to mail me a copy of
your database so I can have a look, just before you do delete the bulk
of the data leaving just a few days' worth, compact and repair and (if
possible) zip.

HTH,
Nikos

Not sure if it helps, but here's the SQL of the query I keep trying to run
to join queries 1 nad 2 and generate the TempDiff field:

SELECT TargetTemp![TargTEMP]-QryOpTemps![OpTemp] AS TempDiff,
QryOpTemps.SampleDate
FROM TargetTemp INNER JOIN QryOpTemps ON TargetTemp.TDate =
QryOpTemps.SampleDate;


I'm at a complete loss on what to try next. Thanks for sticking with me on
this. Sorry we're not in the same time zone!

 

[Nikos Yannacopoulos]

Tobesus,

Sorry for the delay in coming back to you, I was away for a few days.
You rae indeed very close, all you need is built-in function Round() in
the expression in the calculated field - check it out in help.

Basically, where you had:

TempDiff: [OpTEMP] - [TargTEMP]

use instead:

TempDiff: Round([OpTEMP] - [TargTEMP], 0)

Alternatively, you could use the Round function on OpTEMP only, assuming
your TargTEMP is always integer.

HTH,
Nikos

Thanks Nikos!!! Believe it or not, you hit on the problem with your first
try on this post. I did a "sort ascending" and "descending" on one of my
first two queries and found TWO records out of 240,000 that were screwing the
whole thing up. Took them out and the query ran great! Problem solved!

Now I just need to get those counts. I followed the instructions you gave
for that a couple posts ago and they worked great except for one problem. My
TempDiff values are not nice whole numbers (like -1, -2) but numbers with
several decimal places.

THus I need to have it give me a count within a given range. For instance
"Targtemp -1" would equal all values between -0.500 and -1.499 and so forth.

I know just enough to do this with an additional query and for one temp
range at a time, but that doesn't really cut it. Obciously the ideal would be
for it to give me the counts for all temp ranges in one query, and have the
sum of all counts equal to the original number of datapoints.

Surely we can't be more than one post away from the solution. Thanks again
for all your help!

:

Wow, thanks again Nikos. I'm still struggling here.

THe date values in the two queries I'm trying to join were both in date
format, but one was generated using the DateValue[DATE/TIME] command you
suggested whereas the other was generated using a DATE,'m/d/yyyy' script. I
changed them so that both are generated by DateValue.

When I changed this and tried to run my query, it actually started to run
but then came up with the "type mismatch" error again.

Any chance there is some bad data in there in one or more records?
Something in the date field that is invalid as a date? I appreciate it's
hard to check 240k records, but you could try to sort on the date
field in a query, in which case something abnormal should come wither at
the top or the bottom.

Also a quick test with
the query wizard still won't allow me to connect any records from the two
queries.

Because they're both based on the same table? What is the error message?

So now I'm thinking that the problem must be that I'm trying to generate a
field based on cacluations between one field [OpTemps] with 240,000 points
and another [TargTemp] with only 366 points. I would have thought access
could do this somehow.

This is not the problem.

If that is the problem, then I guess I need to somehow populate a new field
which takes my [TargTemp] values (currently one per day) and expands them to
740 values a day so they'll be on a one-to-one ratio with my [OpTemps]? Not
sure how to do that and I had hoped it wouldn't be necessary.

No. The whole idea of relational databases is based on one-to-many
relationships like this ( one target to many readings per day).

Table Structure:
All my original data was in one Table: [DataFromExcel] and I was running all
queries from that. In an effort to get around this current problem though, I
made a copy of that table, called [DataFromExcelCopy] and ran the TargTemp
query off of that so I wouldn't have a compatibility problem with the
QryOpTemps query. Unfortunately it's made no difference.

That's like fighting the symptoms of a disease rather than its cause;
the disease here is the flat table, which may be OK in Excel, but
completely wrong in Access. The way to go is to split your table in two
tables, one for targets and one for actual readings. This is called
"normalizing" your currently "non-normalized" data, in case you've come
across the term. Then the query on the two will work as you want it.

Next step:

Make a query on the original table, including a DateValue on the date
field and the target temp field; use Totals to group by date (so you
should be getting one record per date); now make this a make-table
query, so you create a tblTargetTemp (or something...).

Make another query on the original table, including the original
date/time field (so you don't lose the time data), plus a DateValue on
it in a separate field (so you have the date separately, and in the
correct format) and the OpTemp field; make it a make-table query, so you
create a tblOpTemps (or something...).

Now you can proceed with the queries I suggested originally, on the two
new tables. When you are happy with the whole thing, you can delete the
original table altogether.

If you are still having problems, you are welcome to mail me a copy of
your database so I can have a look, just before you do delete the bulk
of the data leaving just a few days' worth, compact and repair and (if
possible) zip.

HTH,
Nikos

Not sure if it helps, but here's the SQL of the query I keep trying to run
to join queries 1 nad 2 and generate the TempDiff field:

SELECT TargetTemp![TargTEMP]-QryOpTemps![OpTemp] AS TempDiff,
QryOpTemps.SampleDate
FROM TargetTemp INNER JOIN QryOpTemps ON TargetTemp.TDate =
QryOpTemps.SampleDate;


I'm at a complete loss on what to try next. Thanks for sticking with me on
this. Sorry we're not in the same time zone!