subform or combining records revisited

[mfpoore]

Greetings,
I have a form that is used to invoice clients. The invoice amounts come from a SUM query because one hospital often submits more than one sample, but do not want to be billed indiv. for each sample.
I do need to list all records they are being billed for. (A sort of itemization of the SUMed amounts they see on the invoice).
However, using a subform I am showing all records/submissions from all hospitals with submissions.
Is there a way to to just list those records that should be included in each individual hospitals invoice?
(basically I need a sort of SUM function for text data to show all records that went in to make up the SUMed numerical totals, or a way for a subform or something to just list the records associated with that hospital)
thanks for the help,
mfpoore

 

[Pat Hartman]

You need to add a column to the samples table so you can identify what group the samples belong to. If your business rule is to invoice once a month and to include all samples for the month, you can use a date field to group the samples and sum them on the invoice.

 

[mfpoore]

sorry for the incompetence

Im not sure I'm following correctly. I have dates, etc. But to calculate the invoice amounts, I have to GROUP BY (hospitals) in my SUM query.

The doesn't allow me to include non-aggregated fields (in other words, patient name, date etc). Therefore on my invoice form, I have to pull the amounts and hospiatl names from my SUM query, but then I still need a section that lists the clients, tests, and amount that are used to calculate the final SUM...and this has to come from the original UNION query I created to combine all pricing categories.

Are you saying to create the form before SUMming the amounts and then they can be SUM'd?

I'm sure this is very simple, but I am very much an amatuer at this.
mfpoore

 

[Pat Hartman]

I am somewhat surprised that you are saying you are using a form to invoice. Why are you not using a report? How does the customer get the invoice if it is a form?

If you need to show detail on either a form or report, don't summarize it away. Add a footer that sums the amounts.

 

[mfpoore]

thanks

I figured it out. I was just screwing up my subform so it didn't do what I wanted. I've got it now.

p.s. thanks for pointing out that I could have done this with footers. I would use that option, except I use a form so I can look at what I print and make any changes, like giving a test at no cost if they dont get results in a timely manner etc. Can't do that from my main table or form because altering the price there would affect what goes into the accounts of all three labs involved (we offer a dymanic panel that results in each lab's take of the total fee being different for each client based on what tests were requested).

anyway, thanks for help!
this forum saves amateurs like me