If this is your first visit, be sure to check out the FAQ by clicking the link above. You may have to register before you can post: click the register link above to proceed. To start viewing messages, select the forum that you want to visit from the selection below. |
|
|
Thread Tools | Display Modes |
#1
|
|||
|
|||
Show true "repeated" entries
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")))))))); |
#2
|
|||
|
|||
Show true "repeated" entries
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" wrote: 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")))))))); |
#3
|
|||
|
|||
Show true "repeated" entries
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" wrote: 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" wrote: 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")))))))); |
#4
|
|||
|
|||
Show true "repeated" entries
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: . ================================================== ========= Get notification to my posts through email? Please refer to http://msdn.microsoft.com/subscripti...ult.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/subscripti...t/default.aspx. ================================================== ========== This posting is provided "AS IS" with no warranties, and confers no rights. ================================================== ======= |
#5
|
|||
|
|||
Show true "repeated" entries
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]"" wrote: 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: . ================================================== ========= Get notification to my posts through email? Please refer to http://msdn.microsoft.com/subscripti...ult.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/subscripti...t/default.aspx. ================================================== ========== This posting is provided "AS IS" with no warranties, and confers no rights. ================================================== ======= |
#6
|
|||
|
|||
Show true "repeated" entries
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: . ================================================== ======= This posting is provided "AS IS" with no warranties, and confers no rights. ================================================== ======= |
#7
|
|||
|
|||
Show true "repeated" entries
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 |
Thread Tools | |
Display Modes | |
|
|