Joining More Than Three Tables

[MatthewB]

Hi Mathew

As I said previously, all of the Lookup Fields in the tables need to be changed to normal Field Types.

Thank you for providing this insight. I must read about parent and child properties as I see this is how you link the subform to the form. I cannot get the form to sort. I would like to sort the query based on StrataPlanNr Ascending, and then the Service from 82_Infosheet_T. I tried to sort the query but it does not sort. I checked the SQL for errors but noticed none. Do I need to sort the embedded subform?
Thanks

 

[mike60smart]

Can you upload your current copy of the database?

 

[mike60smart]

Thank you for providing this insight. I must read about parent and child properties as I see this is how you link the subform to the form. I cannot get the form to sort. I would like to sort the query based on StrataPlanNr Ascending, and then the Service from 82_Infosheet_T. I tried to sort the query but it does not sort. I checked the SQL for errors but noticed none. Do I need to sort the embedded subform?
Thanks

Not really understanding your comment:-
"I would like to sort the query based on StrataPlanNr Ascending, and then the Service from 82_Infosheet_T. I tried to sort the query but it does not sort."

StrataPlanNr is in the Main Form and this Form is based on the Table and not a query??

Service from 82_Infosheet_T. - this name does not appear anywhere on the Form ??

 

[MatthewB]

Okay, yes right. So no need to sort by the StrataPlanNr, because it is the main form. 04_Infosheet_T.Services is what I meant to write. In the Infosheet subform on the Form the service (Plumber, Catch Basin, Landscaper ect) in not in alphabetical order but the kp_04_Infosheet_ID order.

I have been otherwise trying to cleanup my DB which means some slight variations on some field names.

 

[mike60smart]

The purpose of Forms is to allow for Data Input and I see no reason to sort by Service.

If you want data to be displayed in a specific way then you can create Queries or Reports to do this.

 

[mike60smart]

Hi Mathew
I also took a look at your Contractor Table.
The design of this table is wrong as you have fields which do not describe a Contractor.
You have Repeating Groups where you are entering Multiple Contact details.
ALL of the fields for Contacts should be Records in a related Table.
The Relationship would be something on the lines of this:-

tblContractor
-ContractorID - Autonumber - Long Integer - PK
-Contactorname
-(Other fields to describe the Contractor)

tblContractorContacts
-ContractorContactID - Autonumber - Long Integer
-ContractorID - Number - LongInteger - FK - (Linked to PK from tblContractor)
-ContactFirstname
-ContactSurname
-(Other fields to describe the Contact)

 

[MatthewB]

Access remembers these things if you say yes to the save.

I guess I'll never know if you tried to remove the duplicate join and that fixed the query.

I did as you said in the SQL.

I seem to be at odds with several commentators about how DBs function. Mike60Smart says the purpose of forms is to input data. My documents are very much alive, meaning the data is updated often. Reports are not produced frequently. I just work from the form so it is beneficial that it shows like a report. So the services wants to be alphabetical just for ease of use. I am glad (relieved) that by some luck I figured out a way to put services in order.

 

[The_Doc_Man]

I seem to be at odds with several commentators about how DBs function.

In general, a form is used for piecemeal data input, either original or modified, and sometimes for data removal. Reports are used for wholesale data presentation. It doesn't matter HOW dynamic a form might be. The mechanism behind the form is designed for isolated record control. If you think that forms and reports have other purposes, you are dealing with an incorrect viewpoint. As a 20+ year veteran of using Access, I can tell you that if you try to use Access in a way other than as it was intended, you will have a scrap on your hands and things WILL go bonkers on you.

You complain about sorting things. The way to sort things is through a query. Tables have no inherent order, particularly if updating is commonly done. When it comes to data actions, action queries do the work. When it comes to preparing for presentation, SELECT queries are your workhorses. Whatever you are trying to sort, do it with a query. Forms allow queries as data sources. So do sub-forms. It is not uncommon to have dedicated, named queries set aside for use in forms. Although it IS possible to use an ORDER BY in a form's properties, you would do far better to just build a query for it. The advantage is that you can open the query as a test to verify you got the results right before you pass it to a form for visual organization.