Forgot about the other query.. here is the SQL for MB51Test:
SELECT tblMB51Test.[Pstg date], tblMB51Test.Material,
tblMB51Test.Description, IIf([EUn]="G",[Quantity]/454,[Quantity]) AS Wgts,
tblMB51Test.Amount, tblMB51Test.MvT, tblMB51Test.EUn, tblMB51Test.Reason,
IIf([Reason]="0037","DPT",IIf([MvT]="Z09" Or [MvT]="Z10" Or [MvT]="909" Or
[MvT]="910" Or [MvT]="z51" Or
[MvT]="z52","Skimming",IIf([BISMT]<29999,"Milk",IIf([BISMT]>=300000 And
[BISMT]<=399999,"Fruit",IIf([BISMT]>=40000 And
[BISMT]<60000,"Pkg",IIf([EUn]="ea" Or [EUn]="m2" Or [EUn]="ml" Or
[EUn]="PC","Pkg",IIf([EUn]="G","Milk",IIf([BISMT]>60000,"Milk","Fruit")))))))) AS [Type 2]
FROM (tblMB51Test INNER JOIN tbl2004CloseSchd ON tblMB51Test.[Pstg date] =
tbl2004CloseSchd.Date) LEFT JOIN tblMaterialConversion ON
tblMB51Test.Material = tblMaterialConversion.MATNR
WHERE (((tblMB51Test.[Pstg date])=[Forms]![Enter Date]![Text0]) AND
((tblMB51Test.MvT)<>"555") AND ((tblMB51Test.Reason)<>"0101"))
ORDER BY IIf([Reason]="0037","DPT",IIf([MvT]="Z09" Or [MvT]="Z10" Or
[MvT]="909" Or [MvT]="910" Or [MvT]="z51" Or
[MvT]="z52","Skimming",IIf([BISMT]<29999,"Milk",IIf([BISMT]>=300000 And
[BISMT]<=399999,"Fruit",IIf([BISMT]>=40000 And
[BISMT]<60000,"Pkg",IIf([EUn]="ea" Or [EUn]="m2" Or [EUn]="ml" Or
[EUn]="PC","Pkg",IIf([EUn]="G","Milk",IIf([BISMT]>60000,"Milk","Fruit"))))))));
The date is the first item "pstg date" in both queries.
My guess was that the Total row was rolling up multiple entries, but it
doesn't explain why I get the exploding behavior when I don't include the
Total row.
I made the MB59 query using the "Simple Query Wizard."
As for the results, we are tracking losses, so if we lose 15 lbs of product
A twice in a day it is recorded as such. Inevitably, the rest of the fields
will look identical to the previous entry since there were two 15lbs losses
of product A.
I didn't make the database and I'm relatively new at Access (I just opened
it up last Monday without looking at it since middle school), but my guess is
that I need to include something like an "Incident Number" that is unique to
each row? I don't know how much that will screw up the databse though..
KARL DEWEY said:
When I run MB51 for a particular day,
You spoke of two queries with different results so I asked you to post the
SQL of the queries - plural - meaning more than one. You posted one.
The SQL you posted did not have criteria for date.
The SQL you posted is a totals query - grouping by everything therefore it
will roll up everything, listing a single record even if there are duplicates.
I am modifying an Access 2000 database in which my "qryMB59" query is merging a table to show one other piece of information and an older query "qryMB51."
Is your "qryMB59" an append query by chance? Maybe it should have been an
update query instead as it sound like you appended everything in the table to
the table several times.
I only get 44 results because there are some entries that have similar values
It seems to me that they are identical and not just similar. Why do you
have multiple records that are duplicates?
--
KARL DEWEY
Build a little - Test a little
J Sedoff said:
SELECT qryMB51Test.[Pstg date], qryMB51Test.Material,
qryMB51Test.Description, qryMB51Test.Wgts, qryMB51Test.Amount,
qryMB51Test.MvT, qryMB51Test.Reason, tblZPPR571110503.MTyp, qryMB51Test.[Type
2]
FROM qryMB51Test LEFT JOIN tblZPPR571110503 ON qryMB51Test.Material =
tblZPPR571110503.Component
GROUP BY qryMB51Test.[Pstg date], qryMB51Test.Material,
qryMB51Test.Description, qryMB51Test.Wgts, qryMB51Test.Amount,
qryMB51Test.MvT, qryMB51Test.Reason, tblZPPR571110503.MTyp, qryMB51Test.[Type
2]
HAVING (((qryMB51Test.MvT)<>"Z09" And (qryMB51Test.MvT)<>"Z10" And
(qryMB51Test.MvT)<>"Z51" And (qryMB51Test.MvT)<>"Z52" And
(qryMB51Test.MvT)<>"909" And (qryMB51Test.MvT)<>"910"))
ORDER BY qryMB51Test.Material, tblZPPR571110503.MTyp;
:
Post the SQL of the queries so they can be looked at.
--
KARL DEWEY
Build a little - Test a little
:
I am modifying an Access 2000 database in which my "qryMB59" query is merging
a table to show one other piece of information and an older query "qryMB51."
When I run MB51 for a particular day, I get 54 results; but when I run the
new MB59, I only get 44 results because there are some entries that have
similar values (I presume Access must see them as repeated/multiple entries,
but they are in fact true separate entries) which get filtered out as I have
the "Total" row displayed and everything is set to "Group By." If I don't
have the "Total" row displayed, I get >9000 results which is the same entries
but copied anywhere from 150-225 times, why I don't know.
Any suggestions on how to fix this would be much appreciated!
Thanks, Jim
