Query Accross Multiple Tables

[Gethzerion]

Not sure if this belongs in Queries or Tables or Forms, but I'm realy stuck on this one. I've been quite verbose in my question, so please stick with me on this, but I'm trying to make sure you helpful souls have all the information you need.

The premise, is as follows:

Master table recording general appointment details (time place etc)

For each appointment we have a number of tests that need to be performed. What I have been asked to provide is the following:

For each appointment there must be one or many tests.

For each test, there must be 1 or many sub tests.

The test number is just an integer

Each SubTest has a code, description, price. The price for each test is date dependant. The operator should just be able to enter the sub test code and autopopulate the remaining fields.

So, I want to add a subform in a subform to the main appointment form.

The tables are as follows (Excluding appointment table):

tbl_Test
Test_Number_PK (AutoCounter)
Appointment_Number_FK
Test_Comments

tbl_link_Test_to_Sub_Test
Test_Number_PK (Composite Primary Key)
Test_Code_PK

tbl_Test_Code
Test_Code_PK
Test_Desc

tbl_Test_Code_Price
Test_Code_PK
Price_Date_PK
Price

Now, as the price is dative I have a query on a query (both select queries) to get the prices as of the appointment date. This works fine:

First Filter:

SELECT tbl_Test_Code_Price.Test_Code_PK, tbl_Test_Code_Price.Price_Date_PK, tbl_Test_Code_Price.Price, tbl_Test_Code.Test_Desc
FROM tbl_Test_Code INNER JOIN tbl_Test_Code_Price ON tbl_Test_Code.Test_Code_PK = tbl_Test_Code_Price.Test_Code_PK
WHERE (((tbl_Test_Code_Price.Price_Date_PK)>=#1/1/2007#));

**Note I've just set the Where statement as a dummy value in order to test**

Second Filter:

SELECT qry_first_filter.Test_Code_PK, Max(qry_first_filter.Price_Date_PK) AS MaxOfPrice_Date, qry_first_filter.Price, qry_first_filter.Test_Desc
FROM qry_first_filter
GROUP BY qry_first_filter.Test_Code_PK, qry_first_filter.Price, qry_first_filter.Test_Desc;


I've added the link table to get over the many to many relationship I have between Tests and Test Codes.

As soon as I try and link the tables or tables + query together for the sub sub form (i.e. the test codes data entry)it all goes wrong. I get no option to add data....

Linking the tbl_lnk to Second Filter:

SELECT tbl_Link_Test_To_Sub_Test.Test_Number_PK, tbl_Link_Test_To_Sub_Test.Test_Code_PK, qry_second_filter.MaxOfPrice_Date, qry_second_filter.Price, qry_second_filter.Test_Desc
FROM tbl_Link_Test_To_Sub_Test LEFT JOIN qry_second_filter ON tbl_Link_Test_To_Sub_Test.Test_Code_PK = qry_second_filter.Test_Code_PK;

Or Simply trying to link the tables:

SELECT tbl_Link_Test_To_Sub_Test.Test_Code_PK, tbl_Test_Code.Test_Desc, tbl_Test_Code_Price.Price_Date_PK, tbl_Test_Code_Price.Price
FROM (tbl_Test_Code INNER JOIN tbl_Link_Test_To_Sub_Test ON tbl_Test_Code.Test_Code_PK = tbl_Link_Test_To_Sub_Test.Test_Code_PK) INNER JOIN tbl_Test_Code_Price ON tbl_Test_Code.Test_Code_PK = tbl_Test_Code_Price.Test_Code_PK;

If I manually enter the data into the seperate tables it works fine, and will display correctly on the Appointments form, but you still cannot edit/add records - the error is that this recordset is not updatable.

So, firstly, have I set the tables up correctly? secondly if yes, Am I using the correct approach to this? If yes to both, where am I going wrong? Do I need to create a table from the pricing query or is it much simpler?

Thanks in advance

 

[Len Boorman]

A recordset is not updateable if the source query has a join. That is not you or Access... it is SQL

From your question. Sub Form within Sub Form. Hmmmmm not sure if this is possible at all.

Now it is possible to have a pop up form appear over the top of another form. The linking value (Foreign Key) could be set to be the value that appears in the form that is underneath your pop up. You would need to force a record save on the main form before the popup appears to ensure record exists

L

 

[Gethzerion]

Hmm ok.

So if I've understood correctly what you have written. I would use a pop up form to enter the test codes? The FK would then have to be manually trasnferred (albeit through VBA) into the link table?

As an un-tested suggestion, based on the recordset not being updatable due to the join. If I removed the price table from the query I'm assuming then it would work? Could I then use some form of DLOOKUP or even a modified QBF or similar to populate the fields from the price table?

 

[Len Boorman]

Use base form to enter data to primary table.
Use popup to populate the secondary tables where you need the PK vale from primary table as FK in the secondary.

Now I am not up to spped on exactly what you are trying to achieve but if I understand correctly it is the Price that is the difficult bit.

Believe this is also in secondary table. If so then why not use a combo for the price on the popup. Source of the combo would be the teckie bit and quite probably give the user a choice of 1.

Price is sensitive to values entered on the popup and date so once these values are entered on the popup requery the combo to get price.

Working in the dark here a bit so these are ideas/thought/suggestions

L

 

[Gethzerion]

Maybe this will help:

Appointment 01

Test 01

1111 Alpha1 £0.99
2222 Alpha2 £1.99
3333 Alpha5 £3.25

Test 02

5555 Alpha15 £2.00

Test 03

6666 Delta9 £5.00
7777 Delta12 £2.25

This is an example of the data that needs to be entered.

 

[Len Boorman]

Okay looks fine

Assume Appointment details already completed

Form 1 Enter Test and Test Comments
popup 1 Enter sub test data, Price can be combo where source is based on Test Code and Price Date

Need to think through situations
1) New Test therefore all sub tests new
2) Base test already done along with sub test but new sub test to be entered

Technique I use

Select Person from drop combo on a General Enquiries form
Display basic person details.

Buttons on Details form to
Display Appointment details
Display Base Test Details
Display Sub Test details

These can all be either popups of individual sub forms where visible property is set to False and changed to True on button click

Similar set of buttons to Add Appointment, Base Test, Sub Test

These I suggest would be popups or normal forms whichever you prefer. In both cases they open over the top of Details Form so that when closed you are returned to that individuals details

L