showing unique records - ROOKIE needs help here

D

Dan

Hello -

I have the following query which I am wanting to show only unique
records in the UPC column. I've tried using the Totals, Group By and
First with no results.

SELECT [dbo_Item - MT].UPC_11,
Trim([Dept_Num]) AS [HOST POS],
IIf([dbo_BRD_STOREBATCH - MT].zone1 Like "211",[dbo_BRD_STOREBATCH -
MT].dept,"") AS 211,
IIf([dbo_BRD_STOREBATCH - MT].zone1 Like "212",[dbo_BRD_STOREBATCH -
MT].dept,"") AS 212,
IIf([dbo_BRD_STOREBATCH - MT].zone1 Like "214",[dbo_BRD_STOREBATCH -
MT].dept,"") AS 214,
IIf([dbo_BRD_STOREBATCH - MT].zone1 Like "215",[dbo_BRD_STOREBATCH -
MT].dept,"") AS 215,
IIf([dbo_BRD_STOREBATCH - MT].zone1 Like "216",[dbo_BRD_STOREBATCH -
MT].dept,"") AS 216,
IIf([dbo_BRD_STOREBATCH - MT].zone1 Like "217",[dbo_BRD_STOREBATCH -
MT].dept,"") AS 217
FROM [dbo_BRD_STOREBATCH - MT] INNER JOIN [dbo_Item - MT] ON
[dbo_BRD_STOREBATCH - MT].UPC = [dbo_Item - MT].UPC_14
WHERE ((([dbo_Item - MT].UPC_11) Not Like "000000*") AND
((Trim([Dept_Num]))<>[dbo_BRD_STOREBATCH - MT]![DEPT]))
ORDER BY [dbo_Item - MT].UPC_11;

Results:
UPC_11 HOST POS 211 212 214 215 216 217
00005645388 52 14
00005645389 52 13
00005645400 52 88
00005645400 52 14
00005645410 14 52
00005645425 52 14
00005645425 52 14
00005645425 52 14

I would like the UPC_11 unique:
UPC_11 HOST POS 211 212 214 215 216 217
00005645388 52 14
00005645389 52 13
00005645400 52 88 14
00005645410 14 52
00005645425 52 14 14 14

Thanks for any assistance!
Dan
 
D

Dan

Use a report and set the field properties to Hide Duplicates - Yes.
--
KARL DEWEY
Build a little - Test a little



Dan said:
Hello -
Click to expand...
I have the following query which I am wanting to show only unique
records in the UPC column. I've tried using the Totals, Group By and
First with no results.
Click to expand...
SELECT [dbo_Item - MT].UPC_11,
Trim([Dept_Num]) AS [HOST POS],
IIf([dbo_BRD_STOREBATCH - MT].zone1 Like "211",[dbo_BRD_STOREBATCH -
MT].dept,"") AS 211,
IIf([dbo_BRD_STOREBATCH - MT].zone1 Like "212",[dbo_BRD_STOREBATCH -
MT].dept,"") AS 212,
IIf([dbo_BRD_STOREBATCH - MT].zone1 Like "214",[dbo_BRD_STOREBATCH -
MT].dept,"") AS 214,
IIf([dbo_BRD_STOREBATCH - MT].zone1 Like "215",[dbo_BRD_STOREBATCH -
MT].dept,"") AS 215,
IIf([dbo_BRD_STOREBATCH - MT].zone1 Like "216",[dbo_BRD_STOREBATCH -
MT].dept,"") AS 216,
IIf([dbo_BRD_STOREBATCH - MT].zone1 Like "217",[dbo_BRD_STOREBATCH -
MT].dept,"") AS 217
FROM [dbo_BRD_STOREBATCH - MT] INNER JOIN [dbo_Item - MT] ON
[dbo_BRD_STOREBATCH - MT].UPC = [dbo_Item - MT].UPC_14
WHERE ((([dbo_Item - MT].UPC_11) Not Like "000000*") AND
((Trim([Dept_Num]))<>[dbo_BRD_STOREBATCH - MT]![DEPT]))
ORDER BY [dbo_Item - MT].UPC_11;
Click to expand...
Results:
UPC_11 HOST POS 211 212 214 215 216 217
00005645388 52 14
00005645389 52 13
00005645400 52 88
00005645400 52 14
00005645410 14 52
00005645425 52 14
00005645425 52 14
00005645425 52 14
Click to expand...
I would like the UPC_11 unique:
UPC_11 HOST POS 211 212 214 215 216 217
00005645388 52 14
00005645389 52 13
00005645400 52 88 14
00005645410 14 52
00005645425 52 14 14 14
Click to expand...
Thanks for any assistance!
Dan- Hide quoted text -
Click to expand...

- Show quoted text -
Click to expand...

Using the Report form only hid the duplicate UPC field, but still left
mutiple lines for it. The end result for each UPC may show mutiple
records, so I want to "roll up" all the data into one line per UPC.

Thanks.
 
K

KARL DEWEY

I did not read all of your post. Try this ---
TRANSFORM Min([dbo_BRD_STOREBATCH - MT].DEPT) AS MinOfDEPT
SELECT [dbo_Item - MT].UPC_11
FROM [dbo_BRD_STOREBATCH - MT] INNER JOIN [dbo_Item - MT] ON
[dbo_BRD_STOREBATCH - MT].upc = [dbo_Item - MT].UPC_14
WHERE ((([dbo_Item - MT].UPC_11) Not Like "000000*") AND
(([dbo_BRD_STOREBATCH - MT].zone1)<>[dbo_BRD_STOREBATCH - MT]![DEPT]))
GROUP BY [dbo_Item - MT].UPC_11
PIVOT [dbo_BRD_STOREBATCH - MT].zone1;

--
KARL DEWEY
Build a little - Test a little


Dan said:
Use a report and set the field properties to Hide Duplicates - Yes.
--
KARL DEWEY
Build a little - Test a little



Dan said:
Hello -
Click to expand...
I have the following query which I am wanting to show only unique
records in the UPC column. I've tried using the Totals, Group By and
First with no results.
Click to expand...
SELECT [dbo_Item - MT].UPC_11,
Trim([Dept_Num]) AS [HOST POS],
IIf([dbo_BRD_STOREBATCH - MT].zone1 Like "211",[dbo_BRD_STOREBATCH -
MT].dept,"") AS 211,
IIf([dbo_BRD_STOREBATCH - MT].zone1 Like "212",[dbo_BRD_STOREBATCH -
MT].dept,"") AS 212,
IIf([dbo_BRD_STOREBATCH - MT].zone1 Like "214",[dbo_BRD_STOREBATCH -
MT].dept,"") AS 214,
IIf([dbo_BRD_STOREBATCH - MT].zone1 Like "215",[dbo_BRD_STOREBATCH -
MT].dept,"") AS 215,
IIf([dbo_BRD_STOREBATCH - MT].zone1 Like "216",[dbo_BRD_STOREBATCH -
MT].dept,"") AS 216,
IIf([dbo_BRD_STOREBATCH - MT].zone1 Like "217",[dbo_BRD_STOREBATCH -
MT].dept,"") AS 217
FROM [dbo_BRD_STOREBATCH - MT] INNER JOIN [dbo_Item - MT] ON
[dbo_BRD_STOREBATCH - MT].UPC = [dbo_Item - MT].UPC_14
WHERE ((([dbo_Item - MT].UPC_11) Not Like "000000*") AND
((Trim([Dept_Num]))<>[dbo_BRD_STOREBATCH - MT]![DEPT]))
ORDER BY [dbo_Item - MT].UPC_11;
Click to expand...
Results:
UPC_11 HOST POS 211 212 214 215 216 217
00005645388 52 14
00005645389 52 13
00005645400 52 88
00005645400 52 14
00005645410 14 52
00005645425 52 14
00005645425 52 14
00005645425 52 14
Click to expand...
I would like the UPC_11 unique:
UPC_11 HOST POS 211 212 214 215 216 217
00005645388 52 14
00005645389 52 13
00005645400 52 88 14
00005645410 14 52
00005645425 52 14 14 14
Click to expand...
Thanks for any assistance!
Dan- Hide quoted text -
Click to expand...

- Show quoted text -
Click to expand...

Using the Report form only hid the duplicate UPC field, but still left
mutiple lines for it. The end result for each UPC may show mutiple
records, so I want to "roll up" all the data into one line per UPC.

Thanks.
Click to expand...
 
D

Dan

I did not read all of your post. Try this ---
TRANSFORM Min([dbo_BRD_STOREBATCH - MT].DEPT) AS MinOfDEPT
SELECT [dbo_Item - MT].UPC_11
FROM [dbo_BRD_STOREBATCH - MT] INNER JOIN [dbo_Item - MT] ON
[dbo_BRD_STOREBATCH - MT].upc = [dbo_Item - MT].UPC_14
WHERE ((([dbo_Item - MT].UPC_11) Not Like "000000*") AND
(([dbo_BRD_STOREBATCH - MT].zone1)<>[dbo_BRD_STOREBATCH - MT]![DEPT]))
GROUP BY [dbo_Item - MT].UPC_11
PIVOT [dbo_BRD_STOREBATCH - MT].zone1;

--
KARL DEWEY
Build a little - Test a little



Dan said:
Use a report and set the field properties to Hide Duplicates - Yes.
--
KARL DEWEY
Build a little - Test a little
:
Hello -
I have the following query which I am wanting to show only unique
records in the UPC column. I've tried using the Totals, Group By and
First with no results.
SELECT [dbo_Item - MT].UPC_11,
Trim([Dept_Num]) AS [HOST POS],
IIf([dbo_BRD_STOREBATCH - MT].zone1 Like "211",[dbo_BRD_STOREBATCH -
MT].dept,"") AS 211,
IIf([dbo_BRD_STOREBATCH - MT].zone1 Like "212",[dbo_BRD_STOREBATCH -
MT].dept,"") AS 212,
IIf([dbo_BRD_STOREBATCH - MT].zone1 Like "214",[dbo_BRD_STOREBATCH -
MT].dept,"") AS 214,
IIf([dbo_BRD_STOREBATCH - MT].zone1 Like "215",[dbo_BRD_STOREBATCH -
MT].dept,"") AS 215,
IIf([dbo_BRD_STOREBATCH - MT].zone1 Like "216",[dbo_BRD_STOREBATCH -
MT].dept,"") AS 216,
IIf([dbo_BRD_STOREBATCH - MT].zone1 Like "217",[dbo_BRD_STOREBATCH -
MT].dept,"") AS 217
FROM [dbo_BRD_STOREBATCH - MT] INNER JOIN [dbo_Item - MT] ON
[dbo_BRD_STOREBATCH - MT].UPC = [dbo_Item - MT].UPC_14
WHERE ((([dbo_Item - MT].UPC_11) Not Like "000000*") AND
((Trim([Dept_Num]))<>[dbo_BRD_STOREBATCH - MT]![DEPT]))
ORDER BY [dbo_Item - MT].UPC_11;
Results:
UPC_11 HOST POS 211 212 214 215 216 217
00005645388 52 14
00005645389 52 13
00005645400 52 88
00005645400 52 14
00005645410 14 52
00005645425 52 14
00005645425 52 14
00005645425 52 14
I would like the UPC_11 unique:
UPC_11 HOST POS 211 212 214 215 216 217
00005645388 52 14
00005645389 52 13
00005645400 52 88 14
00005645410 14 52
00005645425 52 14 14 14
Thanks for any assistance!
Dan- Hide quoted text -
- Show quoted text -
Click to expand...
Click to expand...
Using the Report form only hid the duplicate UPC field, but still left
mutiple lines for it. The end result for each UPC may show mutiple
records, so I want to "roll up" all the data into one line per UPC.
Click to expand...
Thanks.- Hide quoted text -
Click to expand...

- Show quoted text -
Click to expand...

Thanks much for the help. I've never used a Crosstab query, very
cool!

Dan
 

Ask a Question

Want to reply to this thread or ask your own question?

You'll need to choose a username for the site, which only take a couple of moments. After that, you can post your question and our members will help you out.

Ask a Question

Similar Threads

Excel crashes as soon as I open it? 0
Help...... 4
Problems opening workspaces to add tables/fields to secured access 0
Help with this challenging VBA coding 3
Graphing Database Growth Rate 1
Outlook crashes with Outlook connector 2002 0
Outlook connector crashes outlook 0
Mac Stuff CDs, A to Z, updated 2007/September/20, and Win & Mac programs, 'WinMac', 'PC/MaC', 'Win-M 0

Top