Union query dropping "duplicate" rows

From: Don Jellie (anonymous_at_discussions.microsoft.com)
Date: 02/28/04 

Date: Sat, 28 Feb 2004 07:11:34 -0800

Hi all,

I have 3 questions about the same issue. I a have union query across 2 almost identical inventory tables (1 current year & 1 historical) that is dropping rows it thinks are duplicates. They're not dups, though. They just look like it. The real thing is massive, but a simplified example follows.

SQL:
SELECT
CLASS, ITEM, SUM(COUNT)
FROM
CURRENT_TBL
GROUP BY
CLASS, ITEM
UNION
SELECT
CLASS, ITEM, SUM(COUNT)
FROM
HISTORY_TBL
GROUP BY
CLASS, ITEM
Orde by 1, 2

Data:
CLASS ITEM COUNT
Fruit Apples 7
Fruit Apples 100
Veggies Peas 50
Veggies Peas 50 (This row gets dropped!)

Questions:
1) Why is this happening and should I have expected it?
2) Is there syntax that will pull both the current and historical veggie rows even though they're identical?
3) I tried solving this by adding a unique column to each union (e.g. SELECT 'CUR' AS CUR, CLASS, ITEM, SUM(COUNT)) but I get an error on GROUP BY CUR, CLASS, ITEM. Can I not do that or is my syntax just bad?

Thanks a lot in advance,
DJ

Relevant Pages

  • Re: mailutil rsh timeout?
    ... Ok, here's the issue, how do I use the above syntax to pull the entire ... It will pull the entire hierarchy. ... I've left the uw-imap server in it's default config it tries pulling things ...
    (comp.mail.imap)
  • 2 questions: DLookup and Chr(13)/Chr(10)
    ... i'm having trouble with the syntax to pull two text fields' values in a ... Syntax error in query ... expression 'eFirst eLast'. ... returns runtime error 3075: Syntax error in query ...
    (microsoft.public.access.modulesdaovba)
  • Display an image whos image path is stored in a SQL DB
    ... What is the syntax for the string to pull and ... image path from a DB and display that image based on the given path? ...
    (microsoft.public.dotnet.languages.csharp)
  • Access SQL Question
    ... What is the syntax for an sql command to get the following. ... I want to pull in a handful of fields from a table but one of the ... if I have a customer table and I want to get each unique ... I am trying to use DISTINCTROW but to no avail. ...
    (microsoft.public.access.formscoding)