IN Clause in an append query

[mbynoe via AccessMonster.com]

Can someone please tell me why this query isn't working correctly:

INSERT INTO workingnew ( meter_number, meter_name, prod_date_time, meas_dth,
meas_mcf, BTU )
SELECT dbo_meterset_volume.meter_number, dbo_meter.meter_name,
dbo_meterset_volume.prod_date_time, [meas_dth]/1000 AS DTH, [meas_mcf]/1000
AS MCF, IIf([meas_mcf]<0.0001," 0",+[meas_dth]/[meas_mcf]*1000) AS BTU
FROM dbo_meterset_volume INNER JOIN dbo_meter ON dbo_meterset_volume.
meter_number=dbo_meter.meter_number
GROUP BY dbo_meterset_volume.meter_number, dbo_meter.meter_name,
dbo_meterset_volume.prod_date_time, [meas_dth]/1000, [meas_mcf]/1000, IIf(
[meas_mcf]<0.0001," 0",+[meas_dth]/[meas_mcf]*1000)
HAVING (((dbo_meterset_volume.meter_number) In (Select meter_number from
InjectedMeter)) AND ((dbo_meterset_volume.prod_date_time)=#9/29/2006#));

Thanks!

 

[DanielS via AccessMonster.com]

What is the error message you get?

Can someone please tell me why this query isn't working correctly:

INSERT INTO workingnew ( meter_number, meter_name, prod_date_time, meas_dth,
meas_mcf, BTU )
SELECT dbo_meterset_volume.meter_number, dbo_meter.meter_name,
dbo_meterset_volume.prod_date_time, [meas_dth]/1000 AS DTH, [meas_mcf]/1000
AS MCF, IIf([meas_mcf]<0.0001," 0",+[meas_dth]/[meas_mcf]*1000) AS BTU
FROM dbo_meterset_volume INNER JOIN dbo_meter ON dbo_meterset_volume.
meter_number=dbo_meter.meter_number
GROUP BY dbo_meterset_volume.meter_number, dbo_meter.meter_name,
dbo_meterset_volume.prod_date_time, [meas_dth]/1000, [meas_mcf]/1000, IIf(
[meas_mcf]<0.0001," 0",+[meas_dth]/[meas_mcf]*1000)
HAVING (((dbo_meterset_volume.meter_number) In (Select meter_number from
InjectedMeter)) AND ((dbo_meterset_volume.prod_date_time)=#9/29/2006#));

Thanks!

 

[mbynoe via AccessMonster.com]

It states that "I am about to append 0 records".

What is the error message you get?

Can someone please tell me why this query isn't working correctly:

[quoted text clipped - 12 lines]

Thanks!

 

[mbynoe via AccessMonster.com]

There are records in the table.....don't why it states 0!

It states that "I am about to append 0 records".

What is the error message you get?

[quoted text clipped - 3 lines]

 

[John Vinson]

Can someone please tell me why this query isn't working correctly:

INSERT INTO workingnew ( meter_number, meter_name, prod_date_time, meas_dth,
meas_mcf, BTU )
SELECT dbo_meterset_volume.meter_number, dbo_meter.meter_name,
dbo_meterset_volume.prod_date_time, [meas_dth]/1000 AS DTH, [meas_mcf]/1000
AS MCF, IIf([meas_mcf]<0.0001," 0",+[meas_dth]/[meas_mcf]*1000) AS BTU
FROM dbo_meterset_volume INNER JOIN dbo_meter ON dbo_meterset_volume.
meter_number=dbo_meter.meter_number
GROUP BY dbo_meterset_volume.meter_number, dbo_meter.meter_name,
dbo_meterset_volume.prod_date_time, [meas_dth]/1000, [meas_mcf]/1000, IIf(
[meas_mcf]<0.0001," 0",+[meas_dth]/[meas_mcf]*1000)
HAVING (((dbo_meterset_volume.meter_number) In (Select meter_number from
InjectedMeter)) AND ((dbo_meterset_volume.prod_date_time)=#9/29/2006#));

Thanks!

Not sure what the problem might be, but I'd suggest changing the
HAVING to WHERE. HAVING is applied *after* all the summing and
calculations are done; WHERE is done before.

You're not using any Sum, Count, First, etc. operators, and you're
grouping by all the fields. Is there some reason you're using a Totals
query (with a GROUP BY) for this? That may be part of the problem!

The other problem might be the JOIN. If there is no matching
meter_number in the table dbo_meter you'll get no records. Is this a
possibility?

John W. Vinson[MVP]

 

[mbynoe via AccessMonster.com]

Thanks John...I will try your suggestions!

Can someone please tell me why this query isn't working correctly:

[quoted text clipped - 12 lines]

Thanks!

Not sure what the problem might be, but I'd suggest changing the
HAVING to WHERE. HAVING is applied *after* all the summing and
calculations are done; WHERE is done before.

You're not using any Sum, Count, First, etc. operators, and you're
grouping by all the fields. Is there some reason you're using a Totals
query (with a GROUP BY) for this? That may be part of the problem!

The other problem might be the JOIN. If there is no matching
meter_number in the table dbo_meter you'll get no records. Is this a
possibility?

John W. Vinson[MVP]