Unique records

  • Thread starter Thread starter Mark A. Sam
  • Start date Start date
M

Mark A. Sam

Hello,

I am sure that I am doing this query correctly, but getting what appear to
be incorrect results. My client says that he sold 163 Production Dies (code
'pd') in November of 2008. On a set of queries that that I ran, against all
of the orders and products for that month, I got 163 Production Dies, which
matches my clients number. To test this I ran a simple select query on
Orders for only 'pd' and get a different result of 228 Orders which is more
than the reported number of Production Dies.

I posted an image of the query at
http://www.promote.org/newsgroups/queryimage.aspx

and here is the SQL

SELECT DISTINCT [Order Entry Header].ordJob, [Order Entry ST
Products].detProdCode
FROM [Order Entry Header] INNER JOIN [Order Entry ST Products] ON [Order
Entry Header].ordID = [Order Entry ST Products].ordID
WHERE ((([Order Entry ST Products].detProdCode)="pd") AND (([Order Entry
Header].ordInvDate) Between #11/1/2008# And #11/30/2008#))
ORDER BY [Order Entry Header].ordJob;

This should yield the all of and only the Orders that have Production Dies
in November?

Thanks for the help and God Bless,

Mark A. Sam
 
Hello,

I am sure that I am doing this query correctly, but getting what appear to
be incorrect results. My client says that he sold 163 Production Dies (code
'pd') in November of 2008. On a set of queries that that I ran, against all
of the orders and products for that month, I got 163 Production Dies, which
matches my clients number. To test this I ran a simple select query on
Orders for only 'pd' and get a different result of 228 Orders which is more
than the reported number of Production Dies.

I posted an image of the query at
http://www.promote.org/newsgroups/queryimage.aspx

and here is the SQL

SELECT DISTINCT [Order Entry Header].ordJob, [Order Entry ST
Products].detProdCode
FROM [Order Entry Header] INNER JOIN [Order Entry ST Products] ON [Order
Entry Header].ordID = [Order Entry ST Products].ordID
WHERE ((([Order Entry ST Products].detProdCode)="pd") AND (([Order Entry
Header].ordInvDate) Between #11/1/2008# And #11/30/2008#))
ORDER BY [Order Entry Header].ordJob;

This should yield the all of and only the Orders that have Production Dies
in November?

Thanks for the help and God Bless,

Mark A. Sam
Click to expand...

It will return all of the products that were ordered... and the accompanying
ordJOB. If One product was ordered five times, in five different ordJob
records, you'll see that product five times.

If you JUST want to see one row for each record in Order Entry ST Products,
remove [Order Entry Header].[ordJob] from the SELECT clause and remove the
Order By clause. You can still use it for criteria even if it's not selected
for viewing.
 
John,

The query worked the way it was supposed to. I neglected to filter it by
Location. The company has two factories, so that this gave the total for
the two. My client's figure was for the local branch.

God Bless,

Mark


John W. Vinson said:
Hello,

I am sure that I am doing this query correctly, but getting what appear to
be incorrect results. My client says that he sold 163 Production Dies
(code
'pd') in November of 2008. On a set of queries that that I ran, against
all
of the orders and products for that month, I got 163 Production Dies,
which
matches my clients number. To test this I ran a simple select query on
Orders for only 'pd' and get a different result of 228 Orders which is
more
than the reported number of Production Dies.

I posted an image of the query at
http://www.promote.org/newsgroups/queryimage.aspx

and here is the SQL

SELECT DISTINCT [Order Entry Header].ordJob, [Order Entry ST
Products].detProdCode
FROM [Order Entry Header] INNER JOIN [Order Entry ST Products] ON [Order
Entry Header].ordID = [Order Entry ST Products].ordID
WHERE ((([Order Entry ST Products].detProdCode)="pd") AND (([Order Entry
Header].ordInvDate) Between #11/1/2008# And #11/30/2008#))
ORDER BY [Order Entry Header].ordJob;

This should yield the all of and only the Orders that have Production Dies
in November?

Thanks for the help and God Bless,

Mark A. Sam
Click to expand...

It will return all of the products that were ordered... and the
accompanying
ordJOB. If One product was ordered five times, in five different ordJob
records, you'll see that product five times.

If you JUST want to see one row for each record in Order Entry ST
Products,
remove [Order Entry Header].[ordJob] from the SELECT clause and remove the
Order By clause. You can still use it for criteria even if it's not
selected
for viewing.
Click to expand...
 
Back
Top