Crazy DISTINCT with left outer join problem

[wyatt]

I have the following query in MSACCESS. I need a list of my items combined with items that are in different warehouses.

select * from items left outer join itemswarehouse on items.partnum=itemswarehouse.partnum

This works fine but gives duplicate items with the exception of a quantity column. I only care to display just one item per row. Ideally I am looking for this kind of code (MSACCESS doesnt support this kind of thing) because I only care about the first record.

select DISTINCT(items.partnum) * from items left outer join itemswarehouse on items.partnum=itemswarehouse.partnum

My items table is about 25 fields but my itemswarehouse is only 3 fields and only contains rows if items exist in a different warehouse (hence outer join)



Any ideas how to do a DISTINCT on items.partnum only using the above query?

[June7]

Don't understand what you want. If you want only a list of partnum values why join tables? If you want the data from Items table to display with the data from ItemsWarehouse table why do you want DISTINCT?

Maybe you need to build a report with Grouping and Sorting.

[wyatt]

I agree it looks weird. I didnt want to get into explaining. but it's because of arggggh Crystal Reports. The report I have needs the columns from itemswarehouse to be present for other reasons.

I think the only solution is to aggregate every single column using either first() or min() or other aggregate functions which would be a pain in the neck since I have 28 columns at least. I thought maybe there is a simpler way of doing it with sub selects and joins.

Here is what it will look like strung out with all the columns.

SELECT first( items.partnum), first(items.desc), sum(itemswarehouse.onhand_qty), etc etc rest of columns 
FROM items 
LEFT JOIN itemswarehouse ON items.partnum=itemswarehouse.partnum
GROUP BY items.partnum

[June7]

Prompts question - Why are you compelled to use CrystalReports?

Grouping by partnum means partnum does not need First function. How would using aggregate function on other fields (other the Sum of quantity) provide meaningful output? Do you just need the fieldnames and the data is ignored?

The aggregate query looks like easiest way to get the result you want.

[wyatt]

Prompts question - Why are you compelled to use CrystalReports?

Grouping by partnum means partnum does not need First function. How would using aggregate function on other fields (other the Sum of quantity) provide meaningful output? Do you just need the fieldnames and the data is ignored?

The aggregate query looks like easiest way to get the result you want.

Access is just my database file. I don't program with it. This is a legacy app I have to deal with including CR.

I bit the bullet and used the aggregate query.

Thanks for your help.