Select DISTINCT columns along with a NON-DISTINCT column

[losdamianos]

Hi all!
Im trying to run a query which will find some DISTINCT (unique fields) and I want to add other fields to them as well

when I run

SELECT DISTINCT Find_Items.Sku
FROM Find_Items;

It work well but I want to add some other fields to the query such as
SKU Location and quantity,

so when I run
SELECT DISTINCT Find_Items.Sku, Find_Items.Location_id
FROM Find_Items;

DISTINCT expression doesnt work and I get duplicates in return


Basically what I want from this query is to match all SKU from ORDER table with SKU from Location Table BUT it should be one SKU each and now I get many duplicates. Ive tried to run a query to match all SKU from ORDER table with the locations and then run another query with DISTINCT formula to reduce all duplications but I dont know how to add other fields such as Location to this query

Stock Control Database attached

any help will be appriciated

 

[namliam]

That means that your combination of SKU and location ID are not unique/distinct... how would access know which to display??

Perhaps you just want one Location ID? But which one? First/Last/Max/Min?

Have you tried:
SELECT Find_Items.Sku, Max(Find_Items.Location_id)
FROM Find_Items
Group by Find_Items.Sku;

 

[losdamianos]

Hi namliam
thanks for the reply
Basically what Im trying to do is create some sort of query which will help find a locations for each SKU.
I need to design warehouse stock controll database. Where I have Fields like Location (JA001A) JA-JK are racks 001 - 042 columns and A-H are rows (A is the lowest one and H is the highest one)

and SKU (HPL808)

BIG DATABASE looks like that
Location ID SKU
JA001A HPL808
JA001B HPL808
...
...
...
...
JK042H HPL912D

Order database where I want to paste a records on a daily basis

SKU Quantity
HPL808 1
.....
.....
.....
HPL912D 1

When I recieve an order I dont see location next to it (my sales department dont know where each item is)

location is UNIQUE but there are lots of pallets of the same SKU stacked on many locations eg. (JA001A, JA001B, JA001C <- HPL808)

When I recieve an order with the list of SKU's (lets say 15 codes in table) I can run a query so I can find all codes with the locations next to each code
PROBLEM is that when I run this query to match ordered SKU's with their location i get lots of duplicates because It looking for all SKUs.
(if I run query to find me location for HPL808 it'll find me something like this
HPL808 - JA001A
HPL808 - JA001B
HPL808 - JA001C
and what I really want is only one SKU EACH ->
HPL808 - JA001A
Next SKU - Next Location
Next SKU - Next Location

I've tried to run Distinct SKU after a query but all I get in return is only SKU and I really need matched location so I'll be able to print it and then pass it to forklift guys. Lets say its some kind of map

Perhaps you just want one Location ID? But which one? First/Last/Max/Min?

First would be ok

Have you tried:
SELECT Find_Items.Sku, Max(Find_Items.Location_id)
FROM Find_Items
Group by Find_Items.Sku;

WOW you are my todays and this week's HERO




Sorry about my english

 

[blubug]

newbie: I have a similar problem, pls help

I also get multiple records returned with my query. Any help will be much appreciated.
SELECT
ITEMMASTER.SCANNUMBER AS BARCODE,
ITEMMASTER.DESCRIPTION AS ITEM_DESCRIPTION,
ITEMMASTER.SELLINGINCL AS SELLING_PRICE,
ITEMAUDIT.QUANTITY AS QTY_SOLD,
(ITEMMASTER.SELLINGINCL*ITEMAUDIT.QUANTITY) AS SALES_VALUE,
ITEMAUDIT.ONHAND AS QTY_ON_HAND
FROM
(ITEMMASTER INNER JOIN ITEMAUDIT
ON ITEMMASTER.PLUNUMBER = ITEMAUDIT.PLUNUMBER) INNER JOIN SUPPLIERPLU
ON ITEMMASTER.PLUNUMBER = SUPPLIERPLU.PLUNUMBER
WHERE
ITEMAUDIT.TXCODE In ('IN','RT')
GROUP BY
ITEMMASTER.SCANNUMBER,
ITEMMASTER.DESCRIPTION,
ITEMMASTER.SELLINGINCL,
ITEMAUDIT.QUANTITY,
ITEMAUDIT.ONHAND
;

Thx in advance

 

[namliam]

Well seeing as your grouping on the quantity sold (ITEMAUDIT.QUANTITY) it seems likely you will get a row per unique quanitity sold.

 

[blubug]

Hi,
I understand what your point, however if I only keep
GROUP BY
ITEMMASTER.SCANNUMBER
which is what I'm afer (a single line returned per scannumber)
I get an error:
"Invalid token. Dynamic SQL error
SQL error code = -104
Invalid expression in the select list (not contained in either an aggregate function or the GROUP BY clause."

It seems the structure I'm using is all worng. Any ideas how I can overcome this issue.

 

[namliam]

That is a whole new question, for that I need to know what it is you are trying to accomplish

 

[blubug]

I want to see:
a) The sales and stock on hand by barcode (grouped by barcode) - one line item retuned for each barcode in the stock system.

I'm looking at this output:
Barcode l Item Description l Selling Price l QTY Sold l Sales Value (Price * Sales - expression) l Current On Hand

the information comes from two tables linked on PLU number.
Does that help?

Thanx for ur patience, appreciate it.

 

[namliam]

SELECT
ITEMMASTER.SCANNUMBER AS BARCODE,
ITEMMASTER.DESCRIPTION AS ITEM_DESCRIPTION,
avg(ITEMMASTER.SELLINGINCL) AS SELLING_PRICE,
sum(ITEMAUDIT.QUANTITY) AS QTY_SOLD,
sum((ITEMMASTER.SELLINGINCL*ITEMAUDIT.QUANTITY)) AS SALES_VALUE,
sum(ITEMAUDIT.ONHAND) AS QTY_ON_HAND
FROM
(ITEMMASTER INNER JOIN ITEMAUDIT
ON ITEMMASTER.PLUNUMBER = ITEMAUDIT.PLUNUMBER) INNER JOIN SUPPLIERPLU
ON ITEMMASTER.PLUNUMBER = SUPPLIERPLU.PLUNUMBER
WHERE
ITEMAUDIT.TXCODE In ('IN','RT')
GROUP BY
ITEMMASTER.SCANNUMBER,
ITEMMASTER.DESCRIPTION,
;

Probably wont be 100% correct but you are probably looking for something like that.

 

[blubug]

let me try this quickly...brb

 

[blubug]

Hi,
Almost everything is working. Only problem is my On Hand figure that is way out. I'll play a bit and see if I can get this one.

 

[blubug]

Having a problem with on hand,
when I run:
SELECT DISTINCT
ITEMMASTER.SCANNUMBER AS BARCODE,
ITEMMASTER.PLUNUMBER AS PLUNO,
ITEMMASTER.DESCRIPTION AS ITEM_DESCRIPTION,
Avg(ITEMMASTER.SELLINGINCL) AS SELLING_PRICE,
Sum(ITEMAUDIT.QUANTITY) AS QTY_SOLD,
Sum(ITEMMASTER.SELLINGINCL*ITEMAUDIT.QUANTITY) AS SALES_VALUE,
Sum(ITEMAUDIT.ONHAND) AS QTY_ON_HAND
FROM (ITEMMASTER INNER JOIN ITEMAUDIT ON ITEMMASTER.PLUNUMBER = ITEMAUDIT.PLUNUMBER) INNER JOIN SUPPLIERPLU ON ITEMAUDIT.PLUNUMBER = SUPPLIERPLU.PLUNUMBER
WHERE (((ITEMAUDIT.TXCODE) In ('IN','RT')))
GROUP BY
ITEMMASTER.SCANNUMBER,
ITEMMASTER.PLUNUMBER,
ITEMMASTER.DESCRIPTION
ORDER BY
ITEMMASTER.SCANNUMBER;

My results are correct, exept for the on hand totals

I found that what is happening with Sum(ITEMAUDIT.ONHAND) AS QTY_ON_HAND (which outputs -100 which should be -17); is that every transaction generates an on hand total. These are added together with sum
line 1: -8
line 2: - 14
line 3: -4
and so on
all the data added togther give the -100.

I've tried using DISTINCT but this has had no effect on the output.

 

[namliam]

Yeah sum "sums" all the details... If you dont want this for this column you can
avg
min
max
it but that will probably not work for you either. For now I suggest you leave it out to get the results you want.

Then in a seperate query you have to find the last [On Hand] value and join it back to this data... no other way to do it I am affraid.

 

[blubug]

Thanks for your advice, will give it a go.