Query changes on its own

[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,

 

[Lord Kelvan]

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

 

[RJF]

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.