how to set up these queries ? ?

[LOUISBUHAGIAR54]

I have three tables from which I can derive three queries. Each table will contain details of services, tests, and hospitalisation respectively. On each record there will be a field called invoiced set to Yes/No depending on whether the record has been invoiced. On each of these three queries there will be another field containing an ID card number. In a fourth table (A), there are particulars of a long list of clients, some of which may have needed services, tests or hospitalisation. These have a matching id card number set as the primary key.

I would like to have a list on a form so that as soon as a client has needed a service (record in Query1), has needed a test (record in Query2), or hospitalisation (record in Query3), and has not been invoiced, his name appears in the list so that a billing can be made. I know that in the end I will need a totals query to group the clients regardless of how many tests, services and/or hospitalisation one may have utilised, as one name. However I am not sure how to set up the relationships between the queries themselves and with TableA.

Can one help me with the relationships to set up between Query1, Query2, Query3 and Table A ?


Many thanks for your assistance.


Louis.

 

[Trevor G]

This sounds as though you haven't worked out your normilization properly, as you should have a table for your clients and then 1 other table for the treatment classes and in that you would have a drop down stating whether it is Services, Tests or Hospitalisation, that way you can create your query for each treatment and also then respectively query what invoices haven't been made. You shouldn't have them as 3 seperate tables.

 

[LOUISBUHAGIAR54]

I appreciate your point about not having separate tables. However the tables themselves are different. Like in the hospitalisation table there is only idcard number, date of entry, date of discharge only, invoiced, and paid (Yes/No) fields. The query then deduces duration of stay.

With regards to services, there are fields as to the type of service, the number of items of service, price, invoiced and paid, apart from the id number.

As to the tests there is a field with the id number, the particular test, price, and then a drop-down field depending on whether the test is pending, cancelled,appointed, or taken.

I feel that it would make things more complicated if I put all three tables in one. In addition the choice of tests or services is made from three cascading combo boxes in each case with different types and classification of items for the two different tables.

As such i feel that it makes more sense to have three different tables.


LouisB

 

[Trevor G]

Then perhaps the way to look forward on this is to look to use a temp table that will have the field names that you can use and then use append queries to append the relevant information into the temp table then you are querying one source. If this is done regularly then you can create a delete query to delete the temp table data first then repopulate it. Finally add a macro to run the whole process and once tested make sure you switch the warnings off in the macro so you don't repeat dialog boxes poping up and add a message box to state all things have occured as requested.

If you can upload a sample of your database (taking out all sensitive data) then we can help find a solution for you.

 

[LOUISBUHAGIAR54]

Hi Trevor,


I will try to upload the database for you. It is still in development stage and the information included is fictitious.

I will appreciate your comments on the general setup and layout.

In the meantime I have already set up the billing form with the list. The form is called frmBilling. The list is on this form and I have already populated it with a totals query derived from the other queries and "Table A".

However it is not working as it should. Kindly let me know if you have received it. By the way the password for "BUHAL004" is "DoctorLB04" I will be inserting the current username in all new fields added so that the database will keep record of who is making what in the database.

I will now try to upload the database which is already split.


LouisB

 

[Trevor G]

I will take a look at this, I am off site for 2 days so hang on in there.