- Joined
- Aug 10, 2012
- Messages
- 1
- Reaction score
- 0
I'm completely new to Access, and am having trouble with distinct(count) as Access does not like this as the first select statement. Any advice is appreciated, thanks.
Table contents include this:
Product AMT Status Vendor
Banana 1.01 Yellow 123456
Banana 2.02 Yellow 123456
Banana 2.02 Green 987654
Apple 5.00 Yellow 443322
Apple 6.00 Yellow 332233
I want to query and return results like this:
Product Vendors Trans TTL Status
Banana 1 2 3.03 Yellow
Banana 1 1 2.02 Green
Apple 2 2 11.00 Yellow
Query that does not work:
SELECT product, Count(distinct vendor) as Vendors, Count(vendor) as Trans, Sum(AMT) as TTL, status
FROM myTable
GROUP BY product, status
Table contents include this:
Product AMT Status Vendor
Banana 1.01 Yellow 123456
Banana 2.02 Yellow 123456
Banana 2.02 Green 987654
Apple 5.00 Yellow 443322
Apple 6.00 Yellow 332233
I want to query and return results like this:
Product Vendors Trans TTL Status
Banana 1 2 3.03 Yellow
Banana 1 1 2.02 Green
Apple 2 2 11.00 Yellow
Query that does not work:
SELECT product, Count(distinct vendor) as Vendors, Count(vendor) as Trans, Sum(AMT) as TTL, status
FROM myTable
GROUP BY product, status