tricky trash can counting

[M_Herb]

hello smart people,

how do i create a quantity field in a report?

i have created a db for medical equipment in a hospital i am constructing.

i have 3 tables. Room, Equipment and a Room/Equipment junction table.

In a single room there might be 2 trash cans. Currently, when i create a report, the trash can is listed twice, once per line of the report.

How do i make the report list (2) trash cans instead of trash can and trash can? There are other rooms in the hospital where i have 50 of the same item in a room and i don't want the report to have 50 lines of the same thing. I only want to see one line with a (50) quantity next to it.



Thanks.

[John_G]

Is each trash can a separate entry in the equipment table? In other words, is each trash can uniquely identified? If the answer to that is No, then all you need is a quantity field in the junction table, to indicate how many of each type of equipment are in each room.

[M_Herb]

no.

the equipment table lists each piece of unique equipment in the building.

for example, the equipment table lists item number #451 Rubbermaid 20qt can, #452 Rubbermaid 30qt can, #453 Rubbermaid 9gallon can.

in patient room 4 i have two of item #451. so in the junction table i entered #451 on two lines. are you saying to just add a quantity field to the junction table?

how would i tally all the #451 in the entire hospital at the end of the report?

[John_G]

Hi -

For the first question - yes that's what I suggest. So your junction table would be RommNumber = 4, ItemNumber = 451, quantity = 2.

Tallying all of the items for the whole hospital might best be done with a subreport. The subreport could have a totals query (based on the junction table) as its source.

In its simplest form, the totals query would have two fields: group by on the item number, and sum on quantity.

There might be other ways of doing this, but that's what I would try first.