Help with Query, Append, or Update...I'm just not sure.

[Palomino33]

I have:

• An Active patient query (discharge date is null) that lists 25 active patients.
• In this query, each patient is assigned to a "doctor". There are 5 patients per doctor, 5 different doctors.

Consider:

• Each doctor sees each of their patients daily. So 5 doctors, see their 5 patients on a daily basis, and for the most part all patients get the same charge but also consider, the charge field, is a pick list from a table of charges.

I want:

• A Form that the doctor can open. Service date defaults to today. (So he doesn't have to enter a date). He sees the list of his patients. He clicks the charge field, and picks the service that was done that day.
• At the end of the day, I want to look at a full list of all visits for the day by all doctors (I don't know if this is a query or table)...so that I can see what each doctor has done....and then if the list is correct, I want it to send the data to a master charge table, a table that keeps track of all visits.

I am not an expert but I think this can be easily done by someone who knows much more than me Any help would be appreciated

[AdventureBob]

First you dont need a master charge table. You need a master charge qry that simply performs the right math on the transactions your have the docs updating. You never store calculated values in access.

[Palomino33]

Hi Bob, the table doesn't have "calculated charges. Those are actually Charge Codes....for example 90917 might mean short visit.

[cbibles]

Do you have the form with "charge" drop-down/"pick list" and tables created? If you have a query you must have some tables you have already created. You need to list what you have already got working in your database before anyone can sufficiently answer your question.

As for getting the report you can create a parameter query and under the date field in the criteria put [Enter date:]. Use the query as the record source for a report. You may want to group the report on Doctor. When you run the report it will prompt you for a date. After you enter the date the report will run and show the records for the date you entered. It is not necessary to create another table if you already have the information you described recording in tables. You can always run the query and enter any date you want to list the records for that date.