Include additional fields in or from a totals qry

[Dee]

Ok here's the issue. In order to find MFG #s with different prices (since we
should always be charged the same price for said product), I had to make two
queries. The first one to group the products and the second to find
duplicate product numbers with different prices. However, in the end, I need
other fields from the table to show the rest of the data for the duplicate
products. But I don't know how find the duplicate information without
grouping and if I put every field on the original totals query, then it will
defeat the purpose of grouping... How can I do this?

Here are the two queries

Discrepancies in Unit Price:

SELECT Data.MANUFACTURER, Data.[MFG #], Data.DESCRIPTION, Data.[UNIT PRICE]
FROM Data
WHERE (((Data.Month)=#10/1/2006#))
GROUP BY Data.MANUFACTURER, Data.[MFG #], Data.DESCRIPTION, Data.[UNIT PRICE]
ORDER BY Data.MANUFACTURER, Data.[MFG #];

Find duplicates for Discrepancies in Unit Price

SELECT [Discrepancies in Unit Price].[MFG #], [Discrepancies in Unit
Price].MANUFACTURER, [Discrepancies in Unit Price].DESCRIPTION,
[Discrepancies in Unit Price].[UNIT PRICE]
FROM [Discrepancies in Unit Price]
WHERE ((([Discrepancies in Unit Price].MANUFACTURER) In (SELECT
[MANUFACTURER] FROM [Discrepancies in Unit Price] As Tmp GROUP BY
[MANUFACTURER],[MFG #] HAVING Count(*)>1 And [MFG #] = [Discrepancies in
Unit Price].[MFG #])))
ORDER BY [Discrepancies in Unit Price].[MFG #], [Discrepancies in Unit
Price].MANUFACTURER;

Here are all of the fields in the original table (which if I could include
this information as well, I would like to):

ID, INVOICE NUMBER, TYPE, INVOICE DATE, BILL-TO, COST CENTER, PRACTICE NAME,
ADDRESS, CITY, STATE, ZIP, MFG #, MANUFACTURER, ITEM #, DESCRIPTION, UNIT,
QTY SHIP, UNIT PRICE, AMOUNT, MONTH

 

[kingston via AccessMonster.com]

Try building a query where the table is linked to itself via the field [MFG #]
(I.E. add the table to the design pane twice and allow only one join on [MFG
#]). Then return all fields from the first table where [Unit Price] <>
[Table_1].[Unit Price]. This assumes that [MFG #] is a key field. This will
return a lot of records but you can eliminate duplicates and sort the results
as required. HTH

Ok here's the issue. In order to find MFG #s with different prices (since we
should always be charged the same price for said product), I had to make two
queries. The first one to group the products and the second to find
duplicate product numbers with different prices. However, in the end, I need
other fields from the table to show the rest of the data for the duplicate
products. But I don't know how find the duplicate information without
grouping and if I put every field on the original totals query, then it will
defeat the purpose of grouping... How can I do this?

Here are the two queries

Discrepancies in Unit Price:

SELECT Data.MANUFACTURER, Data.[MFG #], Data.DESCRIPTION, Data.[UNIT PRICE]
FROM Data
WHERE (((Data.Month)=#10/1/2006#))
GROUP BY Data.MANUFACTURER, Data.[MFG #], Data.DESCRIPTION, Data.[UNIT PRICE]
ORDER BY Data.MANUFACTURER, Data.[MFG #];

Find duplicates for Discrepancies in Unit Price

SELECT [Discrepancies in Unit Price].[MFG #], [Discrepancies in Unit
Price].MANUFACTURER, [Discrepancies in Unit Price].DESCRIPTION,
[Discrepancies in Unit Price].[UNIT PRICE]
FROM [Discrepancies in Unit Price]
WHERE ((([Discrepancies in Unit Price].MANUFACTURER) In (SELECT
[MANUFACTURER] FROM [Discrepancies in Unit Price] As Tmp GROUP BY
[MANUFACTURER],[MFG #] HAVING Count(*)>1 And [MFG #] = [Discrepancies in
Unit Price].[MFG #])))
ORDER BY [Discrepancies in Unit Price].[MFG #], [Discrepancies in Unit
Price].MANUFACTURER;

Here are all of the fields in the original table (which if I could include
this information as well, I would like to):

ID, INVOICE NUMBER, TYPE, INVOICE DATE, BILL-TO, COST CENTER, PRACTICE NAME,
ADDRESS, CITY, STATE, ZIP, MFG #, MANUFACTURER, ITEM #, DESCRIPTION, UNIT,
QTY SHIP, UNIT PRICE, AMOUNT, MONTH

 

[Dee]

Thanks, but MFG # is not a key field because it is duplicated in the table.
This information was imported from excel and I don't have time to break it
out into other tables... I put a field called ID as a unique value since MFG
# is not a unique value in the table. Any other suggestions???
--
Thank you very much for your help!!! I really appreciate it ).

Try building a query where the table is linked to itself via the field [MFG #]
(I.E. add the table to the design pane twice and allow only one join on [MFG
#]). Then return all fields from the first table where [Unit Price] <>
[Table_1].[Unit Price]. This assumes that [MFG #] is a key field. This will
return a lot of records but you can eliminate duplicates and sort the results
as required. HTH

Ok here's the issue. In order to find MFG #s with different prices (since we
should always be charged the same price for said product), I had to make two
queries. The first one to group the products and the second to find
duplicate product numbers with different prices. However, in the end, I need
other fields from the table to show the rest of the data for the duplicate
products. But I don't know how find the duplicate information without
grouping and if I put every field on the original totals query, then it will
defeat the purpose of grouping... How can I do this?

Here are the two queries

Discrepancies in Unit Price:

SELECT Data.MANUFACTURER, Data.[MFG #], Data.DESCRIPTION, Data.[UNIT PRICE]
FROM Data
WHERE (((Data.Month)=#10/1/2006#))
GROUP BY Data.MANUFACTURER, Data.[MFG #], Data.DESCRIPTION, Data.[UNIT PRICE]
ORDER BY Data.MANUFACTURER, Data.[MFG #];

Find duplicates for Discrepancies in Unit Price

SELECT [Discrepancies in Unit Price].[MFG #], [Discrepancies in Unit
Price].MANUFACTURER, [Discrepancies in Unit Price].DESCRIPTION,
[Discrepancies in Unit Price].[UNIT PRICE]
FROM [Discrepancies in Unit Price]
WHERE ((([Discrepancies in Unit Price].MANUFACTURER) In (SELECT
[MANUFACTURER] FROM [Discrepancies in Unit Price] As Tmp GROUP BY
[MANUFACTURER],[MFG #] HAVING Count(*)>1 And [MFG #] = [Discrepancies in
Unit Price].[MFG #])))
ORDER BY [Discrepancies in Unit Price].[MFG #], [Discrepancies in Unit
Price].MANUFACTURER;

Here are all of the fields in the original table (which if I could include
this information as well, I would like to):

ID, INVOICE NUMBER, TYPE, INVOICE DATE, BILL-TO, COST CENTER, PRACTICE NAME,
ADDRESS, CITY, STATE, ZIP, MFG #, MANUFACTURER, ITEM #, DESCRIPTION, UNIT,
QTY SHIP, UNIT PRICE, AMOUNT, MONTH

 

[kingston via AccessMonster.com]

What I meant was that the field [MFG #] contains key data. IOW, it contains
data that is used to uniquely identify, in your case, an item (as part the
many side of a one-to-many relationship).

Thanks, but MFG # is not a key field because it is duplicated in the table.
This information was imported from excel and I don't have time to break it
out into other tables... I put a field called ID as a unique value since MFG
# is not a unique value in the table. Any other suggestions???

Try building a query where the table is linked to itself via the field [MFG #]
(I.E. add the table to the design pane twice and allow only one join on [MFG

[quoted text clipped - 41 lines]

 

[John Spencer]

qOne:
SELECT DISTINCT Manufacturer, [MFG#], [Unit Price]
FROM Data
WHERE [Month] = #10/1/2006#

qTwo:
SELECT Manufacturer, [MFG#]
FROM qOne
GROUP BY Manufacturer, [MFG#]
HAVING Count(Manufacturer) > 1

QThree:
SELECT Data.*
FROM Data INNER JOIN qTwo
ON Data.Manufacturer = qTwoManufacturer
AND Data.[MFG#] =qTwo.[MFG#]
WHERE Data.[Month] = #10/1/2006#

If your field names didn't have spaces and other special characters (#) and
reserved words (month) as field names, this could be done in one query using
subqueries.

 

[Dee]

Any reason why the queries I created come up with 820 records while using the
queries below come up with 13,096 records??? Any known reason why this would
happen?
--
Thank you very much for your help!!! I really appreciate it ).

qOne:
SELECT DISTINCT Manufacturer, [MFG#], [Unit Price]
FROM Data
WHERE [Month] = #10/1/2006#

qTwo:
SELECT Manufacturer, [MFG#]
FROM qOne
GROUP BY Manufacturer, [MFG#]
HAVING Count(Manufacturer) > 1

QThree:
SELECT Data.*
FROM Data INNER JOIN qTwo
ON Data.Manufacturer = qTwoManufacturer
AND Data.[MFG#] =qTwo.[MFG#]
WHERE Data.[Month] = #10/1/2006#

If your field names didn't have spaces and other special characters (#) and
reserved words (month) as field names, this could be done in one query using
subqueries.

Ok here's the issue. In order to find MFG #s with different prices (since
we
should always be charged the same price for said product), I had to make
two
queries. The first one to group the products and the second to find
duplicate product numbers with different prices. However, in the end, I
need
other fields from the table to show the rest of the data for the duplicate
products. But I don't know how find the duplicate information without
grouping and if I put every field on the original totals query, then it
will
defeat the purpose of grouping... How can I do this?

Here are the two queries

Discrepancies in Unit Price:

SELECT Data.MANUFACTURER, Data.[MFG #], Data.DESCRIPTION, Data.[UNIT
PRICE]
FROM Data
WHERE (((Data.Month)=#10/1/2006#))
GROUP BY Data.MANUFACTURER, Data.[MFG #], Data.DESCRIPTION, Data.[UNIT
PRICE]
ORDER BY Data.MANUFACTURER, Data.[MFG #];

Find duplicates for Discrepancies in Unit Price

SELECT [Discrepancies in Unit Price].[MFG #], [Discrepancies in Unit
Price].MANUFACTURER, [Discrepancies in Unit Price].DESCRIPTION,
[Discrepancies in Unit Price].[UNIT PRICE]
FROM [Discrepancies in Unit Price]
WHERE ((([Discrepancies in Unit Price].MANUFACTURER) In (SELECT
[MANUFACTURER] FROM [Discrepancies in Unit Price] As Tmp GROUP BY
[MANUFACTURER],[MFG #] HAVING Count(*)>1 And [MFG #] = [Discrepancies in
Unit Price].[MFG #])))
ORDER BY [Discrepancies in Unit Price].[MFG #], [Discrepancies in Unit
Price].MANUFACTURER;

Here are all of the fields in the original table (which if I could include
this information as well, I would like to):

ID, INVOICE NUMBER, TYPE, INVOICE DATE, BILL-TO, COST CENTER, PRACTICE
NAME,
ADDRESS, CITY, STATE, ZIP, MFG #, MANUFACTURER, ITEM #, DESCRIPTION, UNIT,
QTY SHIP, UNIT PRICE, AMOUNT, MONTH

 

[Dee]

Nevermind.... DUH... my query was originally only bringing back unique values
in the Unit Price while your query brings back all of the records lol....
--
Thank you very much for your help!!! I really appreciate it ).

Any reason why the queries I created come up with 820 records while using the
queries below come up with 13,096 records??? Any known reason why this would
happen?
--
Thank you very much for your help!!! I really appreciate it ).

qOne:
SELECT DISTINCT Manufacturer, [MFG#], [Unit Price]
FROM Data
WHERE [Month] = #10/1/2006#

qTwo:
SELECT Manufacturer, [MFG#]
FROM qOne
GROUP BY Manufacturer, [MFG#]
HAVING Count(Manufacturer) > 1

QThree:
SELECT Data.*
FROM Data INNER JOIN qTwo
ON Data.Manufacturer = qTwoManufacturer
AND Data.[MFG#] =qTwo.[MFG#]
WHERE Data.[Month] = #10/1/2006#

If your field names didn't have spaces and other special characters (#) and
reserved words (month) as field names, this could be done in one query using
subqueries.

Ok here's the issue. In order to find MFG #s with different prices (since
we
should always be charged the same price for said product), I had to make
two
queries. The first one to group the products and the second to find
duplicate product numbers with different prices. However, in the end, I
need
other fields from the table to show the rest of the data for the duplicate
products. But I don't know how find the duplicate information without
grouping and if I put every field on the original totals query, then it
will
defeat the purpose of grouping... How can I do this?

Here are the two queries

Discrepancies in Unit Price:

SELECT Data.MANUFACTURER, Data.[MFG #], Data.DESCRIPTION, Data.[UNIT
PRICE]
FROM Data
WHERE (((Data.Month)=#10/1/2006#))
GROUP BY Data.MANUFACTURER, Data.[MFG #], Data.DESCRIPTION, Data.[UNIT
PRICE]
ORDER BY Data.MANUFACTURER, Data.[MFG #];

Find duplicates for Discrepancies in Unit Price

SELECT [Discrepancies in Unit Price].[MFG #], [Discrepancies in Unit
Price].MANUFACTURER, [Discrepancies in Unit Price].DESCRIPTION,
[Discrepancies in Unit Price].[UNIT PRICE]
FROM [Discrepancies in Unit Price]
WHERE ((([Discrepancies in Unit Price].MANUFACTURER) In (SELECT
[MANUFACTURER] FROM [Discrepancies in Unit Price] As Tmp GROUP BY
[MANUFACTURER],[MFG #] HAVING Count(*)>1 And [MFG #] = [Discrepancies in
Unit Price].[MFG #])))
ORDER BY [Discrepancies in Unit Price].[MFG #], [Discrepancies in Unit
Price].MANUFACTURER;

Here are all of the fields in the original table (which if I could include
this information as well, I would like to):

ID, INVOICE NUMBER, TYPE, INVOICE DATE, BILL-TO, COST CENTER, PRACTICE
NAME,
ADDRESS, CITY, STATE, ZIP, MFG #, MANUFACTURER, ITEM #, DESCRIPTION, UNIT,
QTY SHIP, UNIT PRICE, AMOUNT, MONTH

 

[John Spencer]

Well, there is an error in QThree in the join clause. Which should have
geneated an error - ON Data.Manufacturer = qTwo.Manufacturer was missing a
period between the query name and the field name.


SELECT Data.*
FROM Data INNER JOIN qTwo
ON Data.Manufacturer = qTwo.Manufacturer
AND Data.[MFG#] =qTwo.[MFG#]
WHERE Data.[Month] = #10/1/2006#

Is either set of queries giving you the correct results?

Any reason why the queries I created come up with 820 records while using
the
queries below come up with 13,096 records??? Any known reason why this
would
happen?
--
Thank you very much for your help!!! I really appreciate it ).

qOne:
SELECT DISTINCT Manufacturer, [MFG#], [Unit Price]
FROM Data
WHERE [Month] = #10/1/2006#

qTwo:
SELECT Manufacturer, [MFG#]
FROM qOne
GROUP BY Manufacturer, [MFG#]
HAVING Count(Manufacturer) > 1

QThree:
SELECT Data.*
FROM Data INNER JOIN qTwo
ON Data.Manufacturer = qTwoManufacturer
AND Data.[MFG#] =qTwo.[MFG#]
WHERE Data.[Month] = #10/1/2006#

If your field names didn't have spaces and other special characters (#)
and
reserved words (month) as field names, this could be done in one query
using
subqueries.

Ok here's the issue. In order to find MFG #s with different prices
(since
we
should always be charged the same price for said product), I had to
make
two
queries. The first one to group the products and the second to find
duplicate product numbers with different prices. However, in the end,
I
need
other fields from the table to show the rest of the data for the
duplicate
products. But I don't know how find the duplicate information without
grouping and if I put every field on the original totals query, then it
will
defeat the purpose of grouping... How can I do this?

Here are the two queries

Discrepancies in Unit Price:

SELECT Data.MANUFACTURER, Data.[MFG #], Data.DESCRIPTION, Data.[UNIT
PRICE]
FROM Data
WHERE (((Data.Month)=#10/1/2006#))
GROUP BY Data.MANUFACTURER, Data.[MFG #], Data.DESCRIPTION, Data.[UNIT
PRICE]
ORDER BY Data.MANUFACTURER, Data.[MFG #];

Find duplicates for Discrepancies in Unit Price

SELECT [Discrepancies in Unit Price].[MFG #], [Discrepancies in Unit
Price].MANUFACTURER, [Discrepancies in Unit Price].DESCRIPTION,
[Discrepancies in Unit Price].[UNIT PRICE]
FROM [Discrepancies in Unit Price]
WHERE ((([Discrepancies in Unit Price].MANUFACTURER) In (SELECT
[MANUFACTURER] FROM [Discrepancies in Unit Price] As Tmp GROUP BY
[MANUFACTURER],[MFG #] HAVING Count(*)>1 And [MFG #] = [Discrepancies
in
Unit Price].[MFG #])))
ORDER BY [Discrepancies in Unit Price].[MFG #], [Discrepancies in Unit
Price].MANUFACTURER;

Here are all of the fields in the original table (which if I could
include
this information as well, I would like to):

ID, INVOICE NUMBER, TYPE, INVOICE DATE, BILL-TO, COST CENTER, PRACTICE
NAME,
ADDRESS, CITY, STATE, ZIP, MFG #, MANUFACTURER, ITEM #, DESCRIPTION,
UNIT,
QTY SHIP, UNIT PRICE, AMOUNT, MONTH

 

[Dee]

Ok, so why do I see some records that do not have multiple unit prices? I
only need the records where the Manufacturer & MFG# match and there are
various unit prices. I am trying to find where we were charged various unit
prices for the same product ).

Also, let's say that we have three unit prices for Product X... 5 units at
$0.01, 3 units at $0.02 and 6 units at $0.03... how can I run a report that
will tell me had we purchased the 9 units ($0.02 & $0.03) at the lowest price
($0.01) ... this is how much we would have saved on Product X?

--
Thank you very much for your help!!! I really appreciate it ).

qOne:
SELECT DISTINCT Manufacturer, [MFG#], [Unit Price]
FROM Data
WHERE [Month] = #10/1/2006#

qTwo:
SELECT Manufacturer, [MFG#]
FROM qOne
GROUP BY Manufacturer, [MFG#]
HAVING Count(Manufacturer) > 1

QThree:
SELECT Data.*
FROM Data INNER JOIN qTwo
ON Data.Manufacturer = qTwoManufacturer
AND Data.[MFG#] =qTwo.[MFG#]
WHERE Data.[Month] = #10/1/2006#

If your field names didn't have spaces and other special characters (#) and
reserved words (month) as field names, this could be done in one query using
subqueries.

Ok here's the issue. In order to find MFG #s with different prices (since
we
should always be charged the same price for said product), I had to make
two
queries. The first one to group the products and the second to find
duplicate product numbers with different prices. However, in the end, I
need
other fields from the table to show the rest of the data for the duplicate
products. But I don't know how find the duplicate information without
grouping and if I put every field on the original totals query, then it
will
defeat the purpose of grouping... How can I do this?

Here are the two queries

Discrepancies in Unit Price:

SELECT Data.MANUFACTURER, Data.[MFG #], Data.DESCRIPTION, Data.[UNIT
PRICE]
FROM Data
WHERE (((Data.Month)=#10/1/2006#))
GROUP BY Data.MANUFACTURER, Data.[MFG #], Data.DESCRIPTION, Data.[UNIT
PRICE]
ORDER BY Data.MANUFACTURER, Data.[MFG #];

Find duplicates for Discrepancies in Unit Price

SELECT [Discrepancies in Unit Price].[MFG #], [Discrepancies in Unit
Price].MANUFACTURER, [Discrepancies in Unit Price].DESCRIPTION,
[Discrepancies in Unit Price].[UNIT PRICE]
FROM [Discrepancies in Unit Price]
WHERE ((([Discrepancies in Unit Price].MANUFACTURER) In (SELECT
[MANUFACTURER] FROM [Discrepancies in Unit Price] As Tmp GROUP BY
[MANUFACTURER],[MFG #] HAVING Count(*)>1 And [MFG #] = [Discrepancies in
Unit Price].[MFG #])))
ORDER BY [Discrepancies in Unit Price].[MFG #], [Discrepancies in Unit
Price].MANUFACTURER;

Here are all of the fields in the original table (which if I could include
this information as well, I would like to):

ID, INVOICE NUMBER, TYPE, INVOICE DATE, BILL-TO, COST CENTER, PRACTICE
NAME,
ADDRESS, CITY, STATE, ZIP, MFG #, MANUFACTURER, ITEM #, DESCRIPTION, UNIT,
QTY SHIP, UNIT PRICE, AMOUNT, MONTH

 

[Dee]

I noticed the missing period... but now my problem is the MFG# column
actually has the data from Manufacturer (Manufacturer's Name) and visa
versa........

Here's the query...

SELECT DISTINCT Data.MANUFACTURER, Data.MFGNO, Data.DESCRIPTION,
Data.UNITPRICE, Data.INVMO
FROM Data
WHERE (((Data.INVMO)=#10/1/2006#));

Why would the data for Manufacturer show under the column heading MFG# and
visa versa???
--
Thank you very much for your help!!! I really appreciate it ).

Ok, so why do I see some records that do not have multiple unit prices? I
only need the records where the Manufacturer & MFG# match and there are
various unit prices. I am trying to find where we were charged various unit
prices for the same product ).

Also, let's say that we have three unit prices for Product X... 5 units at
$0.01, 3 units at $0.02 and 6 units at $0.03... how can I run a report that
will tell me had we purchased the 9 units ($0.02 & $0.03) at the lowest price
($0.01) ... this is how much we would have saved on Product X?

--
Thank you very much for your help!!! I really appreciate it ).

qOne:
SELECT DISTINCT Manufacturer, [MFG#], [Unit Price]
FROM Data
WHERE [Month] = #10/1/2006#

qTwo:
SELECT Manufacturer, [MFG#]
FROM qOne
GROUP BY Manufacturer, [MFG#]
HAVING Count(Manufacturer) > 1

QThree:
SELECT Data.*
FROM Data INNER JOIN qTwo
ON Data.Manufacturer = qTwoManufacturer
AND Data.[MFG#] =qTwo.[MFG#]
WHERE Data.[Month] = #10/1/2006#

If your field names didn't have spaces and other special characters (#) and
reserved words (month) as field names, this could be done in one query using
subqueries.

Ok here's the issue. In order to find MFG #s with different prices (since
we
should always be charged the same price for said product), I had to make
two
queries. The first one to group the products and the second to find
duplicate product numbers with different prices. However, in the end, I
need
other fields from the table to show the rest of the data for the duplicate
products. But I don't know how find the duplicate information without
grouping and if I put every field on the original totals query, then it
will
defeat the purpose of grouping... How can I do this?

Here are the two queries

Discrepancies in Unit Price:

SELECT Data.MANUFACTURER, Data.[MFG #], Data.DESCRIPTION, Data.[UNIT
PRICE]
FROM Data
WHERE (((Data.Month)=#10/1/2006#))
GROUP BY Data.MANUFACTURER, Data.[MFG #], Data.DESCRIPTION, Data.[UNIT
PRICE]
ORDER BY Data.MANUFACTURER, Data.[MFG #];

Find duplicates for Discrepancies in Unit Price

SELECT [Discrepancies in Unit Price].[MFG #], [Discrepancies in Unit
Price].MANUFACTURER, [Discrepancies in Unit Price].DESCRIPTION,
[Discrepancies in Unit Price].[UNIT PRICE]
FROM [Discrepancies in Unit Price]
WHERE ((([Discrepancies in Unit Price].MANUFACTURER) In (SELECT
[MANUFACTURER] FROM [Discrepancies in Unit Price] As Tmp GROUP BY
[MANUFACTURER],[MFG #] HAVING Count(*)>1 And [MFG #] = [Discrepancies in
Unit Price].[MFG #])))
ORDER BY [Discrepancies in Unit Price].[MFG #], [Discrepancies in Unit
Price].MANUFACTURER;

Here are all of the fields in the original table (which if I could include
this information as well, I would like to):

ID, INVOICE NUMBER, TYPE, INVOICE DATE, BILL-TO, COST CENTER, PRACTICE
NAME,
ADDRESS, CITY, STATE, ZIP, MFG #, MANUFACTURER, ITEM #, DESCRIPTION, UNIT,
QTY SHIP, UNIT PRICE, AMOUNT, MONTH

 

[Dee]

I have renamed the fields if that helps to:

INVOICENUMBER, TYPE, INVOICEDATE, BILL-TO, COSTCENTER, PRACTICENAME,
ADDRESS, CITY, STATE, ZIP, MFGNO, ITEMNO, DESCRIPTION, MANUFACTURER, UNIT,
QTYSHIP, UNITPRICE, AMOUNT, INVMO

So as to get rid of special characters and reserved names.... in case that
would help... please see below about the columns mixing data and field
names...

--
Thank you very much for your help!!! I really appreciate it ).

I noticed the missing period... but now my problem is the MFG# column
actually has the data from Manufacturer (Manufacturer's Name) and visa
versa........

Here's the query...

SELECT DISTINCT Data.MANUFACTURER, Data.MFGNO, Data.DESCRIPTION,
Data.UNITPRICE, Data.INVMO
FROM Data
WHERE (((Data.INVMO)=#10/1/2006#));

Why would the data for Manufacturer show under the column heading MFG# and
visa versa???
--
Thank you very much for your help!!! I really appreciate it ).

Ok, so why do I see some records that do not have multiple unit prices? I
only need the records where the Manufacturer & MFG# match and there are
various unit prices. I am trying to find where we were charged various unit
prices for the same product ).

Also, let's say that we have three unit prices for Product X... 5 units at
$0.01, 3 units at $0.02 and 6 units at $0.03... how can I run a report that
will tell me had we purchased the 9 units ($0.02 & $0.03) at the lowest price
($0.01) ... this is how much we would have saved on Product X?

--
Thank you very much for your help!!! I really appreciate it ).

qOne:
SELECT DISTINCT Manufacturer, [MFG#], [Unit Price]
FROM Data
WHERE [Month] = #10/1/2006#

qTwo:
SELECT Manufacturer, [MFG#]
FROM qOne
GROUP BY Manufacturer, [MFG#]
HAVING Count(Manufacturer) > 1

QThree:
SELECT Data.*
FROM Data INNER JOIN qTwo
ON Data.Manufacturer = qTwoManufacturer
AND Data.[MFG#] =qTwo.[MFG#]
WHERE Data.[Month] = #10/1/2006#

If your field names didn't have spaces and other special characters (#) and
reserved words (month) as field names, this could be done in one query using
subqueries.

Ok here's the issue. In order to find MFG #s with different prices (since
we
should always be charged the same price for said product), I had to make
two
queries. The first one to group the products and the second to find
duplicate product numbers with different prices. However, in the end, I
need
other fields from the table to show the rest of the data for the duplicate
products. But I don't know how find the duplicate information without
grouping and if I put every field on the original totals query, then it
will
defeat the purpose of grouping... How can I do this?

Here are the two queries

Discrepancies in Unit Price:

SELECT Data.MANUFACTURER, Data.[MFG #], Data.DESCRIPTION, Data.[UNIT
PRICE]
FROM Data
WHERE (((Data.Month)=#10/1/2006#))
GROUP BY Data.MANUFACTURER, Data.[MFG #], Data.DESCRIPTION, Data.[UNIT
PRICE]
ORDER BY Data.MANUFACTURER, Data.[MFG #];

Find duplicates for Discrepancies in Unit Price

SELECT [Discrepancies in Unit Price].[MFG #], [Discrepancies in Unit
Price].MANUFACTURER, [Discrepancies in Unit Price].DESCRIPTION,
[Discrepancies in Unit Price].[UNIT PRICE]
FROM [Discrepancies in Unit Price]
WHERE ((([Discrepancies in Unit Price].MANUFACTURER) In (SELECT
[MANUFACTURER] FROM [Discrepancies in Unit Price] As Tmp GROUP BY
[MANUFACTURER],[MFG #] HAVING Count(*)>1 And [MFG #] = [Discrepancies in
Unit Price].[MFG #])))
ORDER BY [Discrepancies in Unit Price].[MFG #], [Discrepancies in Unit
Price].MANUFACTURER;

Here are all of the fields in the original table (which if I could include
this information as well, I would like to):

ID, INVOICE NUMBER, TYPE, INVOICE DATE, BILL-TO, COST CENTER, PRACTICE
NAME,
ADDRESS, CITY, STATE, ZIP, MFG #, MANUFACTURER, ITEM #, DESCRIPTION, UNIT,
QTY SHIP, UNIT PRICE, AMOUNT, MONTH

 

[John Spencer]

I would guess that your data is like that. That there are names of
manufacturers in the MFGNo field and MfgNo in the Manufacturer field.
Without having your data to look at, I have no other explanation of what you
see.

Your renaming of the fields is a good idea, but you still have one field
with a special character "Bill-To". If you use that field you have to
surround it with [] so that Access will know it is a field and not a
subtraction problem (Subtract the field to" from the field "Bill").

At this stage, I wouldn't rewrite the query all into one. The use of nested
queries is efficient and usually easier to understand and troubleshoot.

I have renamed the fields if that helps to:

INVOICENUMBER, TYPE, INVOICEDATE, BILL-TO, COSTCENTER, PRACTICENAME,
ADDRESS, CITY, STATE, ZIP, MFGNO, ITEMNO, DESCRIPTION, MANUFACTURER, UNIT,
QTYSHIP, UNITPRICE, AMOUNT, INVMO

So as to get rid of special characters and reserved names.... in case that
would help... please see below about the columns mixing data and field
names...

--
Thank you very much for your help!!! I really appreciate it ).

I noticed the missing period... but now my problem is the MFG# column
actually has the data from Manufacturer (Manufacturer's Name) and visa
versa........

Here's the query...

SELECT DISTINCT Data.MANUFACTURER, Data.MFGNO, Data.DESCRIPTION,
Data.UNITPRICE, Data.INVMO
FROM Data
WHERE (((Data.INVMO)=#10/1/2006#));

Why would the data for Manufacturer show under the column heading MFG#
and
visa versa???
--
Thank you very much for your help!!! I really appreciate it ).

Ok, so why do I see some records that do not have multiple unit prices?
I
only need the records where the Manufacturer & MFG# match and there are
various unit prices. I am trying to find where we were charged various
unit
prices for the same product ).

Also, let's say that we have three unit prices for Product X... 5 units
at
$0.01, 3 units at $0.02 and 6 units at $0.03... how can I run a report
that
will tell me had we purchased the 9 units ($0.02 & $0.03) at the lowest
price
($0.01) ... this is how much we would have saved on Product X?

--
Thank you very much for your help!!! I really appreciate it ).


:

qOne:
SELECT DISTINCT Manufacturer, [MFG#], [Unit Price]
FROM Data
WHERE [Month] = #10/1/2006#

qTwo:
SELECT Manufacturer, [MFG#]
FROM qOne
GROUP BY Manufacturer, [MFG#]
HAVING Count(Manufacturer) > 1

QThree:
SELECT Data.*
FROM Data INNER JOIN qTwo
ON Data.Manufacturer = qTwoManufacturer
AND Data.[MFG#] =qTwo.[MFG#]
WHERE Data.[Month] = #10/1/2006#

If your field names didn't have spaces and other special characters
(#) and
reserved words (month) as field names, this could be done in one
query using
subqueries.

Ok here's the issue. In order to find MFG #s with different prices
(since
we
should always be charged the same price for said product), I had to
make
two
queries. The first one to group the products and the second to
find
duplicate product numbers with different prices. However, in the
end, I
need
other fields from the table to show the rest of the data for the
duplicate
products. But I don't know how find the duplicate information
without
grouping and if I put every field on the original totals query,
then it
will
defeat the purpose of grouping... How can I do this?

Here are the two queries

Discrepancies in Unit Price:

SELECT Data.MANUFACTURER, Data.[MFG #], Data.DESCRIPTION,
Data.[UNIT
PRICE]
FROM Data
WHERE (((Data.Month)=#10/1/2006#))
GROUP BY Data.MANUFACTURER, Data.[MFG #], Data.DESCRIPTION,
Data.[UNIT
PRICE]
ORDER BY Data.MANUFACTURER, Data.[MFG #];

Find duplicates for Discrepancies in Unit Price

SELECT [Discrepancies in Unit Price].[MFG #], [Discrepancies in
Unit
Price].MANUFACTURER, [Discrepancies in Unit Price].DESCRIPTION,
[Discrepancies in Unit Price].[UNIT PRICE]
FROM [Discrepancies in Unit Price]
WHERE ((([Discrepancies in Unit Price].MANUFACTURER) In (SELECT
[MANUFACTURER] FROM [Discrepancies in Unit Price] As Tmp GROUP BY
[MANUFACTURER],[MFG #] HAVING Count(*)>1 And [MFG #] =
[Discrepancies in
Unit Price].[MFG #])))
ORDER BY [Discrepancies in Unit Price].[MFG #], [Discrepancies in
Unit
Price].MANUFACTURER;

Here are all of the fields in the original table (which if I could
include
this information as well, I would like to):

ID, INVOICE NUMBER, TYPE, INVOICE DATE, BILL-TO, COST CENTER,
PRACTICE
NAME,
ADDRESS, CITY, STATE, ZIP, MFG #, MANUFACTURER, ITEM #,
DESCRIPTION, UNIT,
QTY SHIP, UNIT PRICE, AMOUNT, MONTH

 

[John Spencer]

Wish I could help more, but I have to admit that I am baffled. If you want
to work more on this problem, I suggest you start a new thread so that
others will pick up on it.

Unfortunately, the data is not like that. That would have been the first
thing that I looked at ). I have no explanation for that either, but
that
is how it is displaying the data.
--
Thank you very much for your help!!! I really appreciate it ).

I would guess that your data is like that. That there are names of
manufacturers in the MFGNo field and MfgNo in the Manufacturer field.
Without having your data to look at, I have no other explanation of what
you
see.

Your renaming of the fields is a good idea, but you still have one field
with a special character "Bill-To". If you use that field you have to
surround it with [] so that Access will know it is a field and not a
subtraction problem (Subtract the field to" from the field "Bill").

At this stage, I wouldn't rewrite the query all into one. The use of
nested
queries is efficient and usually easier to understand and troubleshoot.

I have renamed the fields if that helps to:

INVOICENUMBER, TYPE, INVOICEDATE, BILL-TO, COSTCENTER, PRACTICENAME,
ADDRESS, CITY, STATE, ZIP, MFGNO, ITEMNO, DESCRIPTION, MANUFACTURER,
UNIT,
QTYSHIP, UNITPRICE, AMOUNT, INVMO

So as to get rid of special characters and reserved names.... in case
that
would help... please see below about the columns mixing data and field
names...

--
Thank you very much for your help!!! I really appreciate it ).


:

I noticed the missing period... but now my problem is the MFG# column
actually has the data from Manufacturer (Manufacturer's Name) and visa
versa........

Here's the query...

SELECT DISTINCT Data.MANUFACTURER, Data.MFGNO, Data.DESCRIPTION,
Data.UNITPRICE, Data.INVMO
FROM Data
WHERE (((Data.INVMO)=#10/1/2006#));

Why would the data for Manufacturer show under the column heading MFG#
and
visa versa???
--
Thank you very much for your help!!! I really appreciate it ).


:

Ok, so why do I see some records that do not have multiple unit
prices?
I
only need the records where the Manufacturer & MFG# match and there
are
various unit prices. I am trying to find where we were charged
various
unit
prices for the same product ).

Also, let's say that we have three unit prices for Product X... 5
units
at
$0.01, 3 units at $0.02 and 6 units at $0.03... how can I run a
report
that
will tell me had we purchased the 9 units ($0.02 & $0.03) at the
lowest
price
($0.01) ... this is how much we would have saved on Product X?

--
Thank you very much for your help!!! I really appreciate it ).


:

qOne:
SELECT DISTINCT Manufacturer, [MFG#], [Unit Price]
FROM Data
WHERE [Month] = #10/1/2006#

qTwo:
SELECT Manufacturer, [MFG#]
FROM qOne
GROUP BY Manufacturer, [MFG#]
HAVING Count(Manufacturer) > 1

QThree:
SELECT Data.*
FROM Data INNER JOIN qTwo
ON Data.Manufacturer = qTwoManufacturer
AND Data.[MFG#] =qTwo.[MFG#]
WHERE Data.[Month] = #10/1/2006#

If your field names didn't have spaces and other special
characters
(#) and
reserved words (month) as field names, this could be done in one
query using
subqueries.

Ok here's the issue. In order to find MFG #s with different
prices
(since
we
should always be charged the same price for said product), I had
to
make
two
queries. The first one to group the products and the second to
find
duplicate product numbers with different prices. However, in
the
end, I
need
other fields from the table to show the rest of the data for the
duplicate
products. But I don't know how find the duplicate information
without
grouping and if I put every field on the original totals query,
then it
will
defeat the purpose of grouping... How can I do this?

Here are the two queries

Discrepancies in Unit Price:

SELECT Data.MANUFACTURER, Data.[MFG #], Data.DESCRIPTION,
Data.[UNIT
PRICE]
FROM Data
WHERE (((Data.Month)=#10/1/2006#))
GROUP BY Data.MANUFACTURER, Data.[MFG #], Data.DESCRIPTION,
Data.[UNIT
PRICE]
ORDER BY Data.MANUFACTURER, Data.[MFG #];

Find duplicates for Discrepancies in Unit Price

SELECT [Discrepancies in Unit Price].[MFG #], [Discrepancies in
Unit
Price].MANUFACTURER, [Discrepancies in Unit Price].DESCRIPTION,
[Discrepancies in Unit Price].[UNIT PRICE]
FROM [Discrepancies in Unit Price]
WHERE ((([Discrepancies in Unit Price].MANUFACTURER) In (SELECT
[MANUFACTURER] FROM [Discrepancies in Unit Price] As Tmp GROUP
BY
[MANUFACTURER],[MFG #] HAVING Count(*)>1 And [MFG #] =
[Discrepancies in
Unit Price].[MFG #])))
ORDER BY [Discrepancies in Unit Price].[MFG #], [Discrepancies
in
Unit
Price].MANUFACTURER;

Here are all of the fields in the original table (which if I
could
include
this information as well, I would like to):

ID, INVOICE NUMBER, TYPE, INVOICE DATE, BILL-TO, COST CENTER,
PRACTICE
NAME,
ADDRESS, CITY, STATE, ZIP, MFG #, MANUFACTURER, ITEM #,
DESCRIPTION, UNIT,
QTY SHIP, UNIT PRICE, AMOUNT, MONTH