Multitable query problem

  • Thread starter Thread starter atledreier
  • Start date Start date
A

atledreier

Hey.

I have a problem I can't wrap my head around.

I have a database with mostly normalized data.

I have a few tables with a common key, so they are no problem

TBL_Tag
*tagno
description
area
etc...

TBL_Tag_Termination
*Tagno
misc tag info...

TBL_Tag_Misc
*Tagno
misc tag info fields...

Then I have a list of users that log on, and by doing that I set a
public variable 'Login' that is the userID. I retrieve this number in
my forms and queries by the function Getlogin(). All of this works.

Now, to my problem:
I have a table that link users to areas:

TBL_User_Area
UserID
Area

My users populate this table through a form, setting the areas they
'own' in this table
What I want is to make a query that will return relevant fields from
the different 'tag' tables for areas that they own, AND make the query
editable. I have made a query that return the fields I want, but I
can't make the data editable. I'm sure i'm missing something here, I
just can't see what it is.
 
Thank you, Jeanette. Most of these tips checks out fine.

The one I'm suspecting is the "It uses JOINs of different directions
on multiple tables in the FROM clause. Remove some tables." one.

Here is my query. It selects the correct fields, but is not editable.
If I remove the TBL_Bruker_Modul table it becomes editable.

SELECT Tag.*, Tag_diverse.*, [Tag_format_in/_br/_te].*,
TBL_Bruker_Modul.Login
FROM TBL_Bruker_Modul INNER JOIN (([Tag_format_in/_br/_te] RIGHT JOIN
Tag ON [Tag_format_in/_br/_te].Tag = Tag.Tag) LEFT JOIN Tag_diverse ON
Tag.Tag = Tag_diverse.Tag) ON TBL_Bruker_Modul.Modul = Tag.Area
WHERE (((Tag.Tag_cat)<>"c") AND ((Tag.Flag) Is Null) AND
((TBL_Bruker_Modul.Login)=getlogin()));
 
The problem will be connected to the right join and the left join in the
query.
If you made both these joins an inner join, I would think the query would be
editable.
However that probably won't show the results you want.

Maybe you can work around it by having a button that the user can click to
open a form to edit just the selected record.


Jeanette Cunningham MS Access MVP -- Melbourne Victoria Australia


atledreier said:
Thank you, Jeanette. Most of these tips checks out fine.

The one I'm suspecting is the "It uses JOINs of different directions
on multiple tables in the FROM clause. Remove some tables." one.

Here is my query. It selects the correct fields, but is not editable.
If I remove the TBL_Bruker_Modul table it becomes editable.

SELECT Tag.*, Tag_diverse.*, [Tag_format_in/_br/_te].*,
TBL_Bruker_Modul.Login
FROM TBL_Bruker_Modul INNER JOIN (([Tag_format_in/_br/_te] RIGHT JOIN
Tag ON [Tag_format_in/_br/_te].Tag = Tag.Tag) LEFT JOIN Tag_diverse ON
Tag.Tag = Tag_diverse.Tag) ON TBL_Bruker_Modul.Modul = Tag.Area
WHERE (((Tag.Tag_cat)<>"c") AND ((Tag.Flag) Is Null) AND
((TBL_Bruker_Modul.Login)=getlogin()));


See if this helps understanding about editable and not editable queries.

http://allenbrowne.com/ser-61.html

Jeanette Cunningham MS Access MVP -- Melbourne Victoria Australia
Click to expand...
Click to expand...
 
I tried this query:
SELECT Tag.*, Tag_diverse.*, [Tag_format_in/_br/_te].*,
TBL_Bruker_Modul.Login
FROM ([Tag_format_in/_br/_te] INNER JOIN (TBL_Bruker_Modul INNER JOIN
Tag ON TBL_Bruker_Modul.Modul = Tag.Area) ON [Tag_format_in/_br/
_te].Tag = Tag.Tag) INNER JOIN Tag_diverse ON Tag.Tag =
Tag_diverse.Tag
WHERE (((TBL_Bruker_Modul.Login)=getlogin()) AND ((Tag.Tag_cat)<>"c")
AND ((Tag.Flag) Is Null));

It still showed the records I wanted, but still wasn't editable.

But then I got to thinking that the Users area field really act like a
filter, so maybe I could use a filtered query instead, to get the same
result?


The problem will be connected to the right join and the left join in the
query.
If you made both these joins an inner join, I would think the query would be
editable.
However that probably won't show the results you want.

Maybe you can work around it by having a button that the user can click to
open a form to edit just the selected record.

Jeanette Cunningham MS Access MVP -- Melbourne Victoria Australia




Thank you, Jeanette. Most of these tips checks out fine.
Click to expand...
The one I'm suspecting is the "It uses JOINs of different directions
on multiple tables in the FROM clause. Remove some tables." one.
Click to expand...
Here is my query. It selects the correct fields, but is not editable.
If I remove the TBL_Bruker_Modul table it becomes editable.
Click to expand...
SELECT Tag.*, Tag_diverse.*, [Tag_format_in/_br/_te].*,
TBL_Bruker_Modul.Login
FROM TBL_Bruker_Modul INNER JOIN (([Tag_format_in/_br/_te] RIGHT JOIN
Tag ON [Tag_format_in/_br/_te].Tag = Tag.Tag) LEFT JOIN Tag_diverse ON
Tag.Tag = Tag_diverse.Tag) ON TBL_Bruker_Modul.Modul = Tag.Area
WHERE (((Tag.Tag_cat)<>"c") AND ((Tag.Flag) Is Null) AND
((TBL_Bruker_Modul.Login)=getlogin()));
See if this helps understanding about editable and not editable queries.
http://allenbrowne.com/ser-61.html
Jeanette Cunningham MS Access MVP -- Melbourne Victoria Australia

Hey.
I have a problem I can't wrap my head around.
I have a database with mostly normalized data.
I have a few tables with a common key, so they are no problem
TBL_Tag
*tagno
description
area
etc...
TBL_Tag_Termination
*Tagno
misc tag info...
TBL_Tag_Misc
*Tagno
misc tag info fields...
Then I have a list of users that log on, and by doing that I set a
public variable 'Login' that is the userID. I retrieve this number in
my forms and queries by the function Getlogin(). All of this works.
Now, to my problem:
I have a table that link users to areas:
TBL_User_Area
UserID
Area
My users populate this table through a form, setting the areas they
'own' in this table
What I want is to make a query that will return relevant fields from
the different 'tag' tables for areas that they own, AND make the query
editable. I have made a query that return the fields I want, but I
can't make the data editable. I'm sure i'm missing something here, I
just can't see what it is.
Click to expand...
Click to expand...
Click to expand...
 
I have a problem I can't wrap my head around.

I have a database with mostly normalized data.

I have a few tables with a common key, so they are no problem

TBL_Tag
*tagno
description
area
etc...

TBL_Tag_Termination
*Tagno
misc tag info...

TBL_Tag_Misc
*Tagno
misc tag info fields...
Click to expand...

So these are one-to-one relationships, with Tagno being the primary key of all
the tables?

If you need the users to be able to edit data in any or all of the tables,
with the possibility to have some of the child tables empty, it's going to be
really difficult to get a single editable query. However it's probably also
unnecessary. You could instead use a Form based on TBL_Tag with Subforms based
on the related tables, using Tagno as the master/child link field.
 
I set the Recordset type to Dynaset (inconsistent updates), that made
it editable.

This is a split database. Any drawbacks?
 
I set the Recordset type to Dynaset (inconsistent updates), that made
it editable.

This is a split database. Any drawbacks?
Click to expand...

Performance might be impeded, but if you can get it updateable, more power to
you.
 
Back
Top