Select and combine duplicates

M

Mommybear

I have a database that has 1 record per each item. I want to combine these
and only list the total. I have the following SQL statement.

SELECT DISTINCT JHC.Department, JHC.[Dept Desc], JHC.[Room Title],
JHC.[Equip Code No], JHC.[Plan Room], JHC.[Room No], JHC.Room,
JHC.Description, JHC.[Equip Category], Sum(JHC.[Exist Amt]) AS [SumOfExist
Amt], Sum(JHC.[New Amt]) AS [SumOfNew Amt], JHC.[Unit Cost], JHC.[Prime Mfg],
JHC.Model
FROM JHC
GROUP BY JHC.Department, JHC.[Dept Desc], JHC.[Room Title], JHC.[Equip Code
No], JHC.[Plan Room], JHC.[Room No], JHC.Room, JHC.Description, JHC.[Equip
Category], JHC.[Unit Cost], JHC.[Prime Mfg], JHC.Model
HAVING (((Sum(JHC.[Exist Amt]))>0))
ORDER BY JHC.[Equip Code No], JHC.[Plan Room];

This works great, however, on some of my records, they have different "Equip
Categories" but I still want them to be counted.
Here is an example of my data (For simplicity reasons, I removed some data
from my example):

Department Room Equip Code No Room No Equip Category Exist Amt
admin 5 B1234 504 30A
1
admin 5 B1234 504 30A
1
admin 5 B1234 504 0
1
ED 2 B1234 124 30A
1

The Equip Cat is printed as a Header record.
These should print on the report as:
admin 5 B1234 504
3
ED 2 B1234 124
1

but is printing as:
admin 5 B1234 504
2
admin 5 B1234 504
1
ED 2 B1234 124
1


When I remove JHC.[Equip Category], from the Group By, I get an error saying
it is not a part of the aggregate function. This SQL was written from using
the Design View. Any help would be wonderful as I really need to get this
report done today.
 
J

Jerry Whittle

Does this work?

SELECT JHC.Department,
JHC.[Dept Desc],
JHC.[Room Title],
JHC.[Equip Code No],
JHC.[Plan Room],
JHC.[Room No],
JHC.[Room],
JHC.[Description],
Sum(JHC.[Exist Amt]) AS [SumOfExist Amt],
Sum(JHC.[New Amt]) AS [SumOfNew Amt],
JHC.[Unit Cost],
JHC.[Prime Mfg],
JHC.[Model]
FROM JHC
GROUP BY JHC.Department,
JHC.[Dept Desc],
JHC.[Room Title],
JHC.[Equip Code No],
JHC.[Plan Room],
JHC.[Room No],
JHC.[Room],
JHC.[Description],
JHC.[Unit Cost],
JHC.[Prime Mfg],
JHC.[Model]
HAVING Sum(JHC.[Exist Amt])>0
ORDER BY JHC.[Equip Code No],
JHC.[Plan Room];
 
K

KARL DEWEY

The Equip Cat is printed as a Header record.
In this case you can not have your cake and eat it too. Having Equip
Category in the header breaks out the sums by Equip Category also. If you
have two different Equip Categories then the sums are separated into each
Equip Category.
 
M

Mommybear

It worked, however, I am unable to use the Equipment Category in my report
since it was not included in the Query. It is important that I be able to
list this item. I also noticed that the Unit cost was different for the ones
that do not have an Equipment Category. I guess you can say the data I was
sent is not perfect but still need it to work. Is this possible.

Jerry Whittle said:
Does this work?

SELECT JHC.Department,
JHC.[Dept Desc],
JHC.[Room Title],
JHC.[Equip Code No],
JHC.[Plan Room],
JHC.[Room No],
JHC.[Room],
JHC.[Description],
Sum(JHC.[Exist Amt]) AS [SumOfExist Amt],
Sum(JHC.[New Amt]) AS [SumOfNew Amt],
JHC.[Unit Cost],
JHC.[Prime Mfg],
JHC.[Model]
FROM JHC
GROUP BY JHC.Department,
JHC.[Dept Desc],
JHC.[Room Title],
JHC.[Equip Code No],
JHC.[Plan Room],
JHC.[Room No],
JHC.[Room],
JHC.[Description],
JHC.[Unit Cost],
JHC.[Prime Mfg],
JHC.[Model]
HAVING Sum(JHC.[Exist Amt])>0
ORDER BY JHC.[Equip Code No],
JHC.[Plan Room];
--
Jerry Whittle, Microsoft Access MVP
Light. Strong. Cheap. Pick two. Keith Bontrager - Bicycle Builder.


Mommybear said:
I have a database that has 1 record per each item. I want to combine these
and only list the total. I have the following SQL statement.

SELECT DISTINCT JHC.Department, JHC.[Dept Desc], JHC.[Room Title],
JHC.[Equip Code No], JHC.[Plan Room], JHC.[Room No], JHC.Room,
JHC.Description, JHC.[Equip Category], Sum(JHC.[Exist Amt]) AS [SumOfExist
Amt], Sum(JHC.[New Amt]) AS [SumOfNew Amt], JHC.[Unit Cost], JHC.[Prime Mfg],
JHC.Model
FROM JHC
GROUP BY JHC.Department, JHC.[Dept Desc], JHC.[Room Title], JHC.[Equip Code
No], JHC.[Plan Room], JHC.[Room No], JHC.Room, JHC.Description, JHC.[Equip
Category], JHC.[Unit Cost], JHC.[Prime Mfg], JHC.Model
HAVING (((Sum(JHC.[Exist Amt]))>0))
ORDER BY JHC.[Equip Code No], JHC.[Plan Room];

This works great, however, on some of my records, they have different "Equip
Categories" but I still want them to be counted.
Here is an example of my data (For simplicity reasons, I removed some data
from my example):

Department Room Equip Code No Room No Equip Category Exist Amt
admin 5 B1234 504 30A
1
admin 5 B1234 504 30A
1
admin 5 B1234 504 0
1
ED 2 B1234 124 30A
1

The Equip Cat is printed as a Header record.
These should print on the report as:
admin 5 B1234 504
3
ED 2 B1234 124
1

but is printing as:
admin 5 B1234 504
2
admin 5 B1234 504
1
ED 2 B1234 124
1


When I remove JHC.[Equip Category], from the Group By, I get an error saying
it is not a part of the aggregate function. This SQL was written from using
the Design View. Any help would be wonderful as I really need to get this
report done today.
Click to expand...
Click to expand...
 
M

Mommybear

I understand this, however, if this is the case, why are they printing under
the header this way. Wouldn't this cause it to break out a new header line.
I'm grouping my report on the Equip Code No, then sorting on Dept. This is
all new to me so parden my ignorance. I realized my error on my example. It
should be:

Header Line: B1234 Equip Category 30A
Details Lines:
Department Room Room No Exist Amt
admin 5 504 3
ED 2 124 2





KARL DEWEY said:
In this case you can not have your cake and eat it too. Having Equip
Category in the header breaks out the sums by Equip Category also. If you
have two different Equip Categories then the sums are separated into each
Equip Category.

--
Build a little, test a little.


Mommybear said:
I have a database that has 1 record per each item. I want to combine these
and only list the total. I have the following SQL statement.

SELECT DISTINCT JHC.Department, JHC.[Dept Desc], JHC.[Room Title],
JHC.[Equip Code No], JHC.[Plan Room], JHC.[Room No], JHC.Room,
JHC.Description, JHC.[Equip Category], Sum(JHC.[Exist Amt]) AS [SumOfExist
Amt], Sum(JHC.[New Amt]) AS [SumOfNew Amt], JHC.[Unit Cost], JHC.[Prime Mfg],
JHC.Model
FROM JHC
GROUP BY JHC.Department, JHC.[Dept Desc], JHC.[Room Title], JHC.[Equip Code
No], JHC.[Plan Room], JHC.[Room No], JHC.Room, JHC.Description, JHC.[Equip
Category], JHC.[Unit Cost], JHC.[Prime Mfg], JHC.Model
HAVING (((Sum(JHC.[Exist Amt]))>0))
ORDER BY JHC.[Equip Code No], JHC.[Plan Room];

This works great, however, on some of my records, they have different "Equip
Categories" but I still want them to be counted.
Here is an example of my data (For simplicity reasons, I removed some data
from my example):

Department Room Equip Code No Room No Equip Category Exist Amt
admin 5 B1234 504 30A
1
admin 5 B1234 504 30A
1
admin 5 B1234 504 0
1
ED 2 B1234 124 30A
1

The Equip Cat is printed as a Header record.
These should print on the report as:
admin 5 B1234 504
3
ED 2 B1234 124
1

but is printing as:
admin 5 B1234 504
2
admin 5 B1234 504
1
ED 2 B1234 124
1


When I remove JHC.[Equip Category], from the Group By, I get an error saying
it is not a part of the aggregate function. This SQL was written from using
the Design View. Any help would be wonderful as I really need to get this
report done today.
Click to expand...
Click to expand...
 
K

KARL DEWEY

This might work but I did not test.
Create a query like this --
SELECT JHC.[Equip Code No], JHC.[Equip Category]
FROM JHC
GROUP BY JHC.[Equip Code No], JHC.[Equip Category];

Then this --
SELECT JHC.Department, JHC.[Dept Desc], JHC.[Room Title], JHC.[Equip Code
No], JHC.[Plan Room], JHC.[Room No], JHC.Room, JHC.Description,
Sum(JHC.[Exist Amt]) AS [SumOfExist Amt], Sum(JHC.[New Amt]) AS [SumOfNew
Amt], JHC.[Unit Cost], JHC.[Prime Mfg], JHC.Model
FROM JHC
GROUP BY JHC.Department, JHC.[Dept Desc], JHC.[Room Title], JHC.[Equip Code
No], JHC.[Plan Room], JHC.[Room No], JHC.Room, JHC.Description, JHC.[Unit
Cost], JHC.[Prime Mfg], JHC.Model
HAVING (((Sum(JHC.[Exist Amt]))>0))
ORDER BY JHC.[Equip Code No], JHC.[Plan Room];

Use a report/subreport and link on JHC.[Equip Code No].

--
Build a little, test a little.


Mommybear said:
I understand this, however, if this is the case, why are they printing under
the header this way. Wouldn't this cause it to break out a new header line.
I'm grouping my report on the Equip Code No, then sorting on Dept. This is
all new to me so parden my ignorance. I realized my error on my example. It
should be:

Header Line: B1234 Equip Category 30A
Details Lines:
Department Room Room No Exist Amt
admin 5 504 3
ED 2 124 2





KARL DEWEY said:
The Equip Cat is printed as a Header record.
Click to expand...
In this case you can not have your cake and eat it too. Having Equip
Category in the header breaks out the sums by Equip Category also. If you
have two different Equip Categories then the sums are separated into each
Equip Category.

--
Build a little, test a little.


Mommybear said:
I have a database that has 1 record per each item. I want to combine these
and only list the total. I have the following SQL statement.

SELECT DISTINCT JHC.Department, JHC.[Dept Desc], JHC.[Room Title],
JHC.[Equip Code No], JHC.[Plan Room], JHC.[Room No], JHC.Room,
JHC.Description, JHC.[Equip Category], Sum(JHC.[Exist Amt]) AS [SumOfExist
Amt], Sum(JHC.[New Amt]) AS [SumOfNew Amt], JHC.[Unit Cost], JHC.[Prime Mfg],
JHC.Model
FROM JHC
GROUP BY JHC.Department, JHC.[Dept Desc], JHC.[Room Title], JHC.[Equip Code
No], JHC.[Plan Room], JHC.[Room No], JHC.Room, JHC.Description, JHC.[Equip
Category], JHC.[Unit Cost], JHC.[Prime Mfg], JHC.Model
HAVING (((Sum(JHC.[Exist Amt]))>0))
ORDER BY JHC.[Equip Code No], JHC.[Plan Room];

This works great, however, on some of my records, they have different "Equip
Categories" but I still want them to be counted.
Here is an example of my data (For simplicity reasons, I removed some data
from my example):

Department Room Equip Code No Room No Equip Category Exist Amt
admin 5 B1234 504 30A
1
admin 5 B1234 504 30A
1
admin 5 B1234 504 0
1
ED 2 B1234 124 30A
1

The Equip Cat is printed as a Header record.
These should print on the report as:
admin 5 B1234 504
3
ED 2 B1234 124
1

but is printing as:
admin 5 B1234 504
2
admin 5 B1234 504
1
ED 2 B1234 124
1


When I remove JHC.[Equip Category], from the Group By, I get an error saying
it is not a part of the aggregate function. This SQL was written from using
the Design View. Any help would be wonderful as I really need to get this
report done today.
Click to expand...
Click to expand...
Click to expand...
 

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

Outlook connector crashes outlook 0

Top