what happened to my query?

  • Thread starter Thread starter Valerie
  • Start date Start date
V

Valerie

After builing my first database, I gave it a test drive
this morning using some trial data. Everything worked
perfectly. I then appended data from another database into
my new tables. Still everything looked fine. I then began
adding values to one particular field, "InitialCount". Now
my query, which has run perfectly until now, is mutiplying
my input by exactly 53. E.g., a product which should have
resulted in an "OnHand" value of 14 shows 742.

My expression reads as follows:
On Hand: Sum(nz([InitialCount])+nz([UnitsReceived])-nz
([UnitsShrinkage])-nz([UnitsUsed]))
All fields have a default value of 0, and the only field
with any quantities other than 0 is the InitialCount
field.

Please tell me what I'm doing wrong and what might have
happened to cause this query to behave differently than it
did before I appended the data.

Thank you.
 
Dear Valerie:

It would be better if you were to show the SQL for the entire query.
I think it likely the problem is not in the portion you showed here.

Possibly, the best thing is to show some sample data as well.

I'll try to explain what I think is the most likely cause of your
difficulty.

Your InitialCount is probably in a separate table, which I'm guessing
has only one row for each product. You have joined this to another
table which has multiple rows showing UnitsReceived, UnitsShrinkage,
and UnitsUsed.

If you will remove the SUM and GROUP BY from your query, leaving the
JOIN between these two tables, you would see a simple SELECT query in
which the single row containing the InitialCount is repeated for each
and every more detailed row from the table showing UnitsReceived,
UnitsShrinkage, and UnitsUsed.

When you subsequently SUM the InitialCount, the value in this is being
counted many times.

I suggest you write a separate query showing UnitsReceived,
UnitsShrinkage, and UnitsUsed for each product, using the SUM only on
this. Then, join this with the table showing InitialCount and simply
add the figures together, not using any SUM or other aggregate
function at all.

If you need more detailed, specific instructions about what I'm saying
here, please consider sending the details I have requested. Even in
saying this much, I'm doing a lot of guess work, but the possible
error I'm guessing at is not an uncommon one.

Tom Ellison
Microsoft Access MVP
Ellison Enterprises - Your One Stop IT Experts
 
Back
Top