Query changes on its own

  • Thread starter Thread starter RJF
  • Start date Start date
R

RJF

I created a query that was okay the first time I ran it (Query04).

Then I go run other things in the database, not changing anything, just
running a few Select queries.

When I go back to Query04 and run it, I get the following message:

Microsoft Access can’t represent the join expression
[Query03].[AliasITEMNO] = [Query02].[cost_itemno]

This is the query as I wrote it:

SELECT Query3.AliasITEMNO, Query3.DESC, Mid([ops_name],7,Len([ops_name])-10)
AS Name, Query2.description, Query3.equip_id, Query3.Region, Query2.location,
Query3.SumOfcurrent_meter AS current_meter, Format([purchase_date],"MMM -
YY") AS PurchaseDate, tbl_Unit_rprice.Unitpr, Query3.RPT_ORDER

FROM (Query2 INNER JOIN Query3 ON (Query3.AliasITEMNO = Query2.cost_itemno)
AND (Query2.ops_equipment_meters.meter_id =
Query3.ops_equipment_meters.meter_id) AND (Query2.ops_meter_read.meter_id =
Query3.ops_meter_read.meter_id) AND (Query2.acctno = Query3.acctno)) LEFT
JOIN tbl_Unit_rprice ON Query2.cost_itemno = tbl_Unit_rprice.ITEMNO

ORDER BY Query2.description, Query3.Region, Query2.location;


This is what it changes to:

SELECT Query3.AliasITEMNO, Query3.DESC, Mid([ops_name],7,Len([ops_name])-10)
AS Name, Query2.description, Query3.equip_id, Query3.Region, Query2.location,
Query3.SumOfcurrent_meter AS current_meter, Format([purchase_date],"MMM -
YY") AS PurchaseDate, tbl_Unit_rprice.Unitpr, Query3.RPT_ORDER

FROM (Query2 INNER JOIN Query3 ON (Query2.acctno = Query3.acctno) AND
(Query2.ops_meter_read.meter_id = Query3.ops_meter_read.meter_id) AND
(Query2.ops_equipment_meters.meter_id =
Query3.ops_equipment_meters.meter_id)) LEFT JOIN tbl_Unit_rprice ON
Query2.cost_itemno = tbl_Unit_rprice.ITEMNO

ORDER BY Query2.description, Query3.Region, Query2.location;

When I put the join [Query03].[AliasITEMNO] = [Query02].[cost_itemno] back
it, it runs a few times, then later it changes again.

What are I missing? Any suggestions will be greatly appreciated.

Thanks,
 
do the results actually change between the two queries the query
builder has a tendancy to remove things that wont make a difference in
the query.

you also could try re-creating it.

recreate it then save it and close it dont go into query builder then
run it and see what happens.

Regards
Kelvan
 
Hi Kelvan,

Thanks for your help.

It hasn't done it since yesterday morning. I don't understand why it did it
in the first place and why it doesn't do it anymore. I haven't changed the
queries at all.

If it happens again I will try your suggestion.

Thank you so much.
 
Back
Top