Update Query Problem

[SteveF]

Hi All, I wonder if anyone can help me with an odd problem?

I have a stock consumption database that I add functions to quite often. I've just added another function and am now seeing some odd behaviour when I run one of the append queries.

The query is supposed to move the closing stock across to become the opening stock and set the purchases, credits and closing stock to zero. It has worked very well in this way up until now.

What I'm seeing now is that the number of records it updates seems to be growing, where the warning message would previously say 'you are about to update 150 records' it now says 'you are about to update 22000 records'.

The database is functioning fine, all the equations are working as they should, as are the queries.

I import a product table with all the product info into the new version each time so I can't understand what the other records may be or where they are coming from.

Can anybody help me shed a little light on this?

Thanks

 

[SteveF]

Re: Append Query Problem

Sorry, update query not append.

I'm working in Access 2007

 

[jdraw]

Do you have a test db? Can you try some tests?
Can you post a db with some dummy data showing the problem?

 

[SteveF]

I can copy it and run tests on the problem yes. I will see if I can get a copy to post

 

[SteveF]

This shows the problem I think, 149 records in the product table and the query updating 22001 records

 

[DCrake]

What does your update query look like?

 

[SteveF]

This is the query

 

[DCrake]

Have you a join between the table an the query? if not you are getting the infamous "cartesian product"

 

[SteveF]

Cartesian Product?

 

[jdraw]

Steve,
Can you get a jpg of your table relationships?

 

[SteveF]

No relationship between tables at all

 

[SteveF]

To elaborate a little, there are no relationships between any of the tables. Queries have various relationships as needed

 

[jdraw]

Are you saying you have a database application with multiple tables, and
"The database is functioning fine, all the equations are working as they should, as are the queries. " and there are no relationships between/among tables.

I think some database restructuring/rules are missing.

See this link for a few ideas
http://www.databaseanswers.org/approach2db_design.htm


I think David has identified the possible issue -- missing join in at least one query.

 

[SteveF]

Yes and yes. It works fine and I use it every day. I've always been aware that I'm winging it though, I started from a position of absolute zero and just solved problems as they happened.

I'm careful to test it as I add new functions and this has only just happened. I'm never surprised when I hit bumps in the road though, my lack of knowledge will always lead me into mistakes.

Are we saying that the query and table need to be joined? I will have a mess around and see if I can get that corrected.

Thanks for help; always appreciated.

 

[Rabbie]

If you post the SQL of your query we my be able to see what your problem is. At a first guess it looks like you have an incorrect join somewhere. Try converting your update query into a select query and see what records it generates

 

[SteveF]

Cured it. I had the query in the wrong place, I've just deleted it and added the correct field from the product table.

Thanks for looking guys, you pointed me in the right direction as per usual.

Steve

 

[jdraw]

Glad you've got it resolved.