Sorry, I dont seem to be able to go away
I don't think I know how to ask this correctly.
I have a form based on a UNION query (comprised of two SUM queries) that returns total amounts for invoicing, as well as the client count for that month.
I would like to be able to list on the form the clients. (Instead of saying "charges for '9' clients" then the total price, I would like to be able to list the client names, then go to next page and see the next hospitals client list.
It was suggested that I group by date on a subform, however, the SUM queries (that went into the final UNION query) are grouped by hospital, because there are many hospitals, most of which with many clients, and the invoice is for the totals for the hospital, which only allows me to COUNT client name, not list them individually on the form.
If this is not clear, look below for a sort of procedural outline:
1)table with individual clients (lists client name, hospital, address, testsrequested, etc)--->2)Query1 performed on clients that included a panel to perform billing prices--->3)Query2 performed on clients that did not include a panel to perform billing prices--->4)SUM of Query1 GROUPed BY hospital, to get totals for the month for each hospital affected--->5)SUM of Query2 GROUPed BY hospital to get totals for the month for each hospital affected--->6)UNION query to combine both SUM queries into one--->7)form built on final query for printable invoices (form and not report so that I can give free tests when not completed in time, etc)
My final form has all the information I need for the numeric values, the total fees for each hospital for the month, the break down of how much of each fee goes to which labs, etc. But what I really want is to list the client names for each hospital, and not just say "billing for 9 clients".
Sorry to keep taking up so much time, I just can't seem to get it working properly.
mfpoore
I don't think I know how to ask this correctly.I have a form based on a UNION query (comprised of two SUM queries) that returns total amounts for invoicing, as well as the client count for that month.
I would like to be able to list on the form the clients. (Instead of saying "charges for '9' clients" then the total price, I would like to be able to list the client names, then go to next page and see the next hospitals client list.
It was suggested that I group by date on a subform, however, the SUM queries (that went into the final UNION query) are grouped by hospital, because there are many hospitals, most of which with many clients, and the invoice is for the totals for the hospital, which only allows me to COUNT client name, not list them individually on the form.
If this is not clear, look below for a sort of procedural outline:
1)table with individual clients (lists client name, hospital, address, testsrequested, etc)--->2)Query1 performed on clients that included a panel to perform billing prices--->3)Query2 performed on clients that did not include a panel to perform billing prices--->4)SUM of Query1 GROUPed BY hospital, to get totals for the month for each hospital affected--->5)SUM of Query2 GROUPed BY hospital to get totals for the month for each hospital affected--->6)UNION query to combine both SUM queries into one--->7)form built on final query for printable invoices (form and not report so that I can give free tests when not completed in time, etc)
My final form has all the information I need for the numeric values, the total fees for each hospital for the month, the break down of how much of each fee goes to which labs, etc. But what I really want is to list the client names for each hospital, and not just say "billing for 9 clients".
Sorry to keep taking up so much time, I just can't seem to get it working properly.
mfpoore
