Prev: How to combine queries????
Next: Filtering a Crosstab Query from Combo Boxes on Unbound Form
From: atledreier on 22 Apr 2010 03:06
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.

From: Jeanette Cunningham on 22 Apr 2010 03:30
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

"atledreier" <atledreier(a)gmail.com> wrote in message
news:b841c5d7-e662-49a4-8512-4586146d9756(a)i37g2000yqn.googlegroups.com...
> 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.
>


From: atledreier on 22 Apr 2010 03:51
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()));


On 22 apr, 09:30, "Jeanette Cunningham"
<n...(a)discussions.microsoft.com> wrote:
> 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
>
> "atledreier" <atledre...(a)gmail.com> wrote in message
>
> news:b841c5d7-e662-49a4-8512-4586146d9756(a)i37g2000yqn.googlegroups.com...
>
>
>
> > 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.

From: Jeanette Cunningham on 22 Apr 2010 06:53
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" <atledreier(a)gmail.com> wrote in message
news:fe9ae0cf-c139-4b49-bf17-2aba8dc45e91(a)j17g2000yqa.googlegroups.com...
> 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()));
>
>
> On 22 apr, 09:30, "Jeanette Cunningham"
> <n...(a)discussions.microsoft.com> wrote:
>> 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
>>
>> "atledreier" <atledre...(a)gmail.com> wrote in message
>>
>> news:b841c5d7-e662-49a4-8512-4586146d9756(a)i37g2000yqn.googlegroups.com...
>>
>>
>>
>> > 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.
>


From: atledreier on 22 Apr 2010 07:41
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?


On 22 apr, 12:53, "Jeanette Cunningham"
<n...(a)discussions.microsoft.com> wrote:
> 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" <atledre...(a)gmail.com> wrote in message
>
> news:fe9ae0cf-c139-4b49-bf17-2aba8dc45e91(a)j17g2000yqa.googlegroups.com...
>
>
>
> > 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()));
>
> > On 22 apr, 09:30, "Jeanette Cunningham"
> > <n...(a)discussions.microsoft.com> wrote:
> >> 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
>
> >> "atledreier" <atledre...(a)gmail.com> wrote in message
>
> >>news:b841c5d7-e662-49a4-8512-4586146d9756(a)i37g2000yqn.googlegroups.com...
>
> >> > 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.

 |  Next  |  Last
Pages: 1 2
Prev: How to combine queries????
Next: Filtering a Crosstab Query from Combo Boxes on Unbound Form