Query combining results from multiple tables, data cannot be modified

[James Grant]

Hi Everyone,

This thread originated from another topic previously posted.

Whilst designing a database I have encountered a significant issue in being able to generate queries that combine data from multiple related tables (e.g. generating a query that combined information from the Staff, Positions and Allocations tables to allow me to view a Staff name, Allocated position number, and Position description [using the common fields in the Allocations table -- StaffID allocated to a PositionID.]).

Whilst all the information I need to see "summarised" is present and clear, I cannot modify the data whilst viewing it in this combined form. To many intents and purposes this negates the true functionality of running the query.

Is there a way to allow updates to occur across the tables? Does it lie in re-designing the database to only have 1-to-1 relationships? Is there anything else (work-arounds, alternative methods, permissions I need to allow/deny) to consider?

Thank-you very much for any input and expertise, particularly to NeoPa who has stimulated my posts to date and my drive to figure this all out!

James

[TheSmileyCoder]

I have made an example that I think illustrates your needs. If it does not, I will need more details on your table structure (Field names and types), and a few lines of example data.

[NeoPa]

Let's start with getting a clear understanding of where the limits are of updatable recordsets (Reasons for a Query to be Non-Updatable).

From there, as I expect there will be some queries you need for display which cannot be constrained to be updatable, you can do what I sometimes find myself doing, which is to create a specific form just for editing and trigger that from the display form.

Forms can be filtered easily (to include only the relevant record) or the specific record can be selected automatically from code after the form has been opened (but before the operator sees anything) in case you want to allow more edits while the form is still open.

[MMcCarthy]

Hi James

I know NeoPa has already posted a link to the Normalisation article in the other thread so I'm just posting it here for reference.

Normalisation doesn't mean that you have to have a one to one relationship between tables. Rather it means you can't have a many to many relationship so where that exists (e.g. if staff could hold more than one position and a position can be held by more than one staff member) you would need to add a Join table made up usually by the primary key of both the position and staff tables. This means that both the position and staff tables would have a one to one relationship with the new Join table.

Looking at you original question where you were trying to get a set of data showing the position details of staff on teams, a structure something like the following should work. I'm assuming a staff member can hold a position on more than one team.

tblStaff
StaffID PK
StaffName
PositionID (FK to tblPosition)

tblPosition
PositionID PK
PositionTitle
PositionDescription

tblTeams
TeamID PK
TeamName

tblStaffOnTeam {Join table with 2 FK as 1 PK}
StaffID (FK to tblStaff) PK
TeamID (FK to tblTeams) PK

So now to find a dataset for a particular team (e.g. 2) you cold run a query as follows:

SELECT T.TeamName, S.StaffID, S.StaffName, P.*
FROM tblTeams AS T LEFT JOIN tblStaffOnTeam AS ST
ON T.TeamID = ST.TeamID INNER JOIN 
(tblStaff AS S INNER JOIN tblPosition AS P
ON S.PositionID=P.PositionID)
ON ST.StaffID=S.StaffID
WHERE T.TeamID=2

The updateable issue as mentioned by NeoPa is a different issue but I think it's important to understand how normalisation works first. This query in it's current format would not be updatable (at least I don't think so) but with this structure you should be able to create an updatable query.

I hope this makes sense. Feel free to ask questions about anything you don't understand.

Mary

[James Grant]

Hi TheSmileyCoder, NeoPa and Mary,

Thank-you all very much for your replies!

SmileyCoder, your example database was fantastic in highlighting key ideas and programming for me. I'm finding it very useful. I have a couple of questions though if you don't mind:

1. The Staff ID text box (bound to KEY_Person) is 'Disabled': I'm not familiar with Enabled/Disabled text boxes. I would have assumed disabling the text box would prevent it updating or refreshing, but clearly it is feeding up to date information as the recordset progresses. So what changes when it becomes Enabled vs. Disabled?
2. The Position combo box (bound to ID_position): Firstly, I find the effect of combining the properties of a Row Source SELECT statement, Column Count and Column Widths property great. It's very simple in the scheme of things I appreciate, but a neat example of how the little things add up. My question - Why is the Row Source statement returning all those fields? I can understand the Position and Position Description, but why include the KEY_Position field? It's not visible (and neither is the Description field with the current settings either) and I wouldn't imagine whoever's selecting the Position wishes to know what the assigned unique ID is?

NeoPa, the linked article was helpful in explaining to me why I cannot do what I want to do (presently). I've been thinking of two alternative solutions both utilising additional subforms, either: Using tblStaff (being central to staffing our department) as the source for a subform, with two separate subforms beneath for tblTeam and tblPosition linked via the foreign keys such that progressing through the staff list shows which Team they belong to and which Position they hold just below. Alternatively, more closely adhering to your suggestion above and creating a command button that sends the user to a new 'modifying' form with multiple subforms (linked to Staff, Positions, Teams, etc) that have filters placed based upon the row that was selected from the original "summarised" data (using my somewhat dodgy many-to-many query). Do either of these seem more feasible or better to you?

Mary, your solution is most likely to be my intermediate-to-long term answer. I'm currently going through the Normalisation process and restructuring my tables and data, which will take a little time. A complicating factor is the presence of other tables I have not yet added to the mix for simplicity's sake; however I do not believe any of them will pose an issue as their relationships are 1-to-1 or 1-to-many as far as I'm aware (though it's still potentially a very long-winded set of JOIN statements!). Thank-you for typing out your example, it's invaluable in improving my understanding of multiple JOIN queries (I've been using the frowned upon method of FROM tblX, Y, Z WHERE x.a=y.a, y.a=z.a, z.a=x.a if you catch my drift). And your customised example of a JOIN table greatly reassured me I was thinking along the right path of re-structuring.

I will be informing my manager that a little more time is required to make the database workable. But I realise now that without this re-work now even the short-term viability of the database is poor, and long-term useless.

As soon as I've gotten a re-worked structure happening I'll ask you more questions! Good chances I'll have to ask a few during the re-structuring haha.

Thanks!!

-James

[NeoPa]

James,

My answer to that is that both appraoches seem to be good ones. If space is no issue it may be that the subform approach is cleaner and easier to use. For all my experience in some areas, I still have a tendency to ignore areas of less experience when I can find a workable solution another way. This may not always be the best approach, so I would say go for the former (subform based) approach if it suits your layout. It's probably the more natural, Access-orientated of the two.

[TheSmileyCoder]

Hi James

A control can be Locked and/Or Enabled. If a control is locked the user can "enter" the control to copy the information stored in it, but cannot edit it.
If its a locked dropdown (combobox) the user can click the dropdown list, but still cannot change the value.
I find that it confuses the user so I tend to disable combobox instead of locking them (if/when I need to)

A disabled control is also locked to some degree since if the user cannot enter it, he cannot edit it either. You should think of the enabled property as telling you whether or not the user can interact with it.

Now the special thing about KEY_Person is that it is a AutoNumber field. The user can never change this field, so why allow access to it? I disable it, so it gets clear to the user that he cannot edit the ID of the record.

Regarding the Position combo box
I made a small mistake in the properties. The column Count should have been 2 instead of 3, and if you try changing that I think that will illustrate why I included the Description in the return values.

It is true that the users selecting position in the dropdown do not care about the KEY_Position, and that is why it is not shown. However, the Primary Key of table Position (KEY_Position) is what gets stored in the tbl_Staff as ID_Position (some people like to use same names both tables, matter of taste) is known as a foreign key.

Why store the ID of the record, instead of the textvalue? Well for one it is smaller. Another reason is that if you want to change the Position Title, you can change it in the tbl_Position, and the changes will automatically be reflected everywhere the value is used. (Try changing one of the positions directly in the tbl_Position and see the effect on the form (Once the form has been closed and opened))

One of the most important reasons is that it is always unique. Imagine if you had 2 positions that had the same title but different wages, and that you kept the wages in the same table. How would you distinguish between the 2 if you bind it to the title? Using a unique key helps you to accomplish that.

[James Grant]

Hi All,

Firstly, SmileyCoder thank-you for your explanation. I'm still processing through it at this moment but I know it's saved me a lot of heartache searching through forums and help files.

Secondly, a further query for all (and I feel that it adheres to the original thread, please correct me if I'm wrong) regarding forms, subforms and sub-subforms as a possible solution for viewing all this interlinked data.

Originally I created a tab control that would allow me to view the different tables of data quickly and easily. As my earlier proposal to NeoPa states, I would like to slightly tweak this such that my Staff table is the main table, then as you cycle through each row a linked subform to my Allocations table (on StaffID as it were) only shows the resultant allocation for that staff member. Then again, for clarity, a second subform (?sub-subform) based on my Positions tables (linked on PositionID) would provide the details of the position allocated above.

The issue that I have encountered is I don't have a master form on which to base the first subform. Because I've created tabs the tables are currently only ever being displayed as a subform, and never a form themselves. I tried setting the Master Link and Child Link fields to [StaffID] but because there's no underlying, bound form it's not working. I've tried specifying the first subform prior to the linking field (e.g. [tblStaff subform].[Staff ID]) but the filter does not work (no error, simply a blank subform).

I've read in other posts about setting an invisible text box to allow subsequent subforms to be set; but I think I need to surmount this problem first.

I thought about simply recreating the tabs such that they are based on a form, and perhaps I'm simply too tired, but I cannot find a way to do that either. My trials with creating a test-only form based on a table, with a subform in the footer section correctly linked via the Access Wizard was only partially successful: the link and filter worked perfectly, however I had to view the form's data in a form view and the subform in datasheet -- I require both the form and subform to be in a datasheet view (better for an 'overview' perspective).

I apologise if this post is somewhat tangential (and again if this should be a whole new thread please let me know) and meandering. I really should do my programming in the morning when I'm fresh and not last thing at night I fear.

Many thanks for all your help so far! Any information on any part of the above post will be most appreciated :)

-James

[James Grant]

Hi All,

Just a very quick update on my progress to date:
-After restructuring and relating my tables as per earlier advice I was successful in creating a 'combining query' that allowed me to view the data in the Allocations table plus clarifying information such as the Staff ID's relevant Name (from Staff table) and the Position codes relevant Title and Description (from Positions table). This combined query, I presume due to the 1:1 or 1:M only relationships between my tables now, is updatable (exactly what I wanted).
-NeoPa, As such I've bypassed the issue of attempting to create a filtered sub-subform from a subform on a tab page without an original form as a basis. I'm sure this has to be possible and will hope to come across the answer in the future.
-SmileyCoder your last response, which I was processing at the time of my last response, has made my data readability many-MANY fold better! Without that tidbit I believe I would still be stumped. Thank-you!
-Again Mary, thank-you very much for your suggested code and structuring - it certainly made it possible for me to 'get away' with a combining query/subform arrangement (my original intention funnily enough) rather than a cascading 'form-subform-subsubform' arrangement which was proving difficult (aforementioned). I now enjoy the much cleaner and more easily adapted query results from data that is linked from separate tables using JOIN statements, and filtered by WHERE.

I intend on posting my finished table structures, table relationships and relevant SQL later in the week so that others might be able to learn from my experiences to date.

On the whole the database appears to be functional (much to the joy of management) and it's mainly tweaking and Report writing remaining.

Cheers!

-James

[NeoPa]

Excellent news James. Very pleased to hear your success story.

I say yours deliberately. We can help, but recognising the help and incorporating it into your project is your part in the tale and that is also very important. Not everyone manages that as well as you seem to have done here. Congratulations.