Show true "repeated" entries

J

J Sedoff

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
"qryMB51Test."

When I run MB51 for a particular day, I get 54 results; but when I run the
new MB59, I only get 41 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 with identical values) 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 are the same entries but copied anywhere from 150-225 times, but I
don't know why.

Any suggestions on how to fix this would be much appreciated!

Thanks, Jim

In case these help you,
SQL of MB59:

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;


SQL of 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"))))))));
 
G

Golfinray

I would guess that your join or your table structure are not correct. That is
almost always the case when you are getting too many returns from a query.
Try a different join type first.

J Sedoff said:
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
"qryMB51Test."

When I run MB51 for a particular day, I get 54 results; but when I run the
new MB59, I only get 41 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 with identical values) 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 are the same entries but copied anywhere from 150-225 times, but I
don't know why.

Any suggestions on how to fix this would be much appreciated!

Thanks, Jim

In case these help you,
SQL of MB59:

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;


SQL of 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"))))))));
Click to expand...
 
J

J Sedoff

Unfortunately, I have tried all the join types (there is only one shared
property) and I still the same results. (I don't know if this helps, but)
most of my tables are linked so I do not believe I can change their structure
(right? I'm somewhat new to Access).

Golfinray said:
I would guess that your join or your table structure are not correct. That is
almost always the case when you are getting too many returns from a query.
Try a different join type first.

J Sedoff said:
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
"qryMB51Test."

When I run MB51 for a particular day, I get 54 results; but when I run the
new MB59, I only get 41 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 with identical values) 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 are the same entries but copied anywhere from 150-225 times, but I
don't know why.

Any suggestions on how to fix this would be much appreciated!

Thanks, Jim

In case these help you,
SQL of MB59:

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;


SQL of 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"))))))));
Click to expand...
Click to expand...
 
C

Charles Wang [MSFT]

Hi Jim,
To let us better understand your issue, could you please first answer me
the following questions:
1. Where is the meaning of "Total" row as you mentioned?
I did not see the "Total" row from your two SQL queries.

2. Why did you use GROUP BY for MB59?
I did not see there were any aggregate functions such as SUM, AVG etc
in your SQL statement.

3. What were your real concerns?
It seemed that the query of MB59 was very different from MB51Test. It
should be normal that they got different result. Did you expect that they
could produce same result?

By the way, The earlier Office products before and including Access XP are
not supported now. I recommend that you upgrade your Access database to
Access 2003 or 2007 now.

If you have any other questions or concerns, please feel free to let me
know. Look forward to your response.

Best regards,
Charles Wang
Microsoft Online Community Support
===========================================================
Delighting our customers is our #1 priority. We welcome your
comments and suggestions about how we can improve the
support we provide to you. Please feel free to let my manager
know what you think of the level of service provided. You can
send feedback directly to my manager at: (e-mail address removed).
===========================================================
Get notification to my posts through email? Please refer to
http://msdn.microsoft.com/subscriptions/managednewsgroups/default.aspx#notif
ications.

Note: The MSDN Managed Newsgroup support offering is for
non-urgent issues where an initial response from the community
or a Microsoft Support Engineer within 1 business day is acceptable.
Please note that each follow up response may take approximately
2 business days as the support professional working with you may
need further investigation to reach the most efficient resolution.
The offering is not appropriate for situations
that require urgent, real-time or phone-based interactions or complex
project analysis and dump analysis issues. Issues of this nature are best
handled working with a dedicated Microsoft Support Engineer by
contacting Microsoft Customer Support Services (CSS) at
http://msdn.microsoft.com/subscriptions/support/default.aspx.
============================================================
This posting is provided "AS IS" with no warranties, and confers no rights.
=========================================================
 
J

J Sedoff

1. In Access you can display the "Total" row where you make decision such as
Group By, Sum, Average, Min, etc.

2. (I'm guessing you might have read past this piece, it was in the middle
of my question) If I don't use the Group By option, my query explodes and
gives me >9000 records, even though there should only be 54. If I use the
Group By, I only get 41 though, and that's my main problem.

3. I was trying to make a new MB51 with the MB59 query. MB51 is using an
outdated grouping method (using Type 2) to segregate our losses (Skimming,
Fruit, Milk), but due to updated material numbers, it is placing some of the
milk in the fruit, some of the fruit in the skimming, etc. There is the MTyp
field in the ZPPR table that I wanted to use instead as a grouping mechanism.
I was expecting very little difference, except another field (MTyp) so that
when I run a report I can organize it by MTyp, rather than Type 2.

As for Version number, I would love to update, except that I'm an intern for
the summer (I have to work with what I got! ...unfortunately.)

"Charles Wang [MSFT]" said:
Hi Jim,
To let us better understand your issue, could you please first answer me
the following questions:
1. Where is the meaning of "Total" row as you mentioned?
I did not see the "Total" row from your two SQL queries.

2. Why did you use GROUP BY for MB59?
I did not see there were any aggregate functions such as SUM, AVG etc
in your SQL statement.

3. What were your real concerns?
It seemed that the query of MB59 was very different from MB51Test. It
should be normal that they got different result. Did you expect that they
could produce same result?

By the way, The earlier Office products before and including Access XP are
not supported now. I recommend that you upgrade your Access database to
Access 2003 or 2007 now.

If you have any other questions or concerns, please feel free to let me
know. Look forward to your response.

Best regards,
Charles Wang
Microsoft Online Community Support
===========================================================
Delighting our customers is our #1 priority. We welcome your
comments and suggestions about how we can improve the
support we provide to you. Please feel free to let my manager
know what you think of the level of service provided. You can
send feedback directly to my manager at: (e-mail address removed).
===========================================================
Get notification to my posts through email? Please refer to
http://msdn.microsoft.com/subscriptions/managednewsgroups/default.aspx#notif
ications.

Note: The MSDN Managed Newsgroup support offering is for
non-urgent issues where an initial response from the community
or a Microsoft Support Engineer within 1 business day is acceptable.
Please note that each follow up response may take approximately
2 business days as the support professional working with you may
need further investigation to reach the most efficient resolution.
The offering is not appropriate for situations
that require urgent, real-time or phone-based interactions or complex
project analysis and dump analysis issues. Issues of this nature are best
handled working with a dedicated Microsoft Support Engineer by
contacting Microsoft Customer Support Services (CSS) at
http://msdn.microsoft.com/subscriptions/support/default.aspx.
============================================================
This posting is provided "AS IS" with no warranties, and confers no rights.
=========================================================
Click to expand...
 
C

Charles Wang [MSFT]

Hi Jim,
Thank you for your response.

Regarding 2, I noticed that your query MB59 did not use aggregate functions such as Sum, AVG
etc. In this case, group by statement indeed do not perform aggregate statistics on those
records. That should be the reason why you saw >9000 records. When you use Access Total
and Group By functions, they automatically apply aggregate functions and group by on those
records from your query and that was why the total number of displayed records was reduced
to 41.

Regarding why it should be 54, I am not very sure of that because I am not familiar with your
data structures and requirements. However one thing I need to point out is that if you want to
use Access group by and Total functions, please do not specify GROUP BY and HAVING
statements in your MB59 query. You may perform further tests and welcome to your further
posting back if you have any other questions or concerns.

Have a nice day!

Best regards,
Charles Wang
Microsoft Online Community Support
=========================================================
Delighting our customers is our #1 priority. We welcome your
comments and suggestions about how we can improve the
support we provide to you. Please feel free to let my manager
know what you think of the level of service provided. You can
send feedback directly to my manager at: (e-mail address removed).
=========================================================
This posting is provided "AS IS" with no warranties, and confers no rights.
=========================================================
 
J

J Sedoff

I have solved the problem. I was told that I needed to make the entries
wholly unique, which was not happening. This was accomplished by adding
another field that, although not unique numbers, when put together with the
rest of the data would yield unique records (a batch number was shared among
several different ingredients, but was unique for each final product, thus
several of the same ingredients would have unique batch numbers, even though
other ingredients might share that same batch number). I kept all the
settings the same as is listed in the SQL's in the first post, and I got all
the 54 results to appear.

Thank you for all your help! Jim
 
Top