T
Tom Stoddard
That's why I keep asking these questions. Sooner or later someone shows me
the obvious and the light goes on. Thanks Joan!
That's why I keep asking these questions. Sooner or later someone shows me
the obvious and the light goes on. Thanks Joan!
T
TC
Hi Tom
Let's see if we can work through this
Table structures
----------------
First, multi-table qwuestions are too hard to understand, when you
describe them only as normal narrative text. You need to show the table
structures in some clearer way. Here's how I do it:
SalesReps
SalesRepID (PK?)
CustomerID
CustomerName
Permissions
UserName (PK?)
SalesRepID
You didn't define the Customers table, aso I'll make the obvious guess:
Customers
CustomerID (PK)
CustomerName
Second, it is an imperative, basic requirement of such problems, that
you clearly state the primary key (PK) of each table. The fact that you
haven't done that, makes me wonder whether you have defined them
correctly. I really should stop here, until you have done that.
However, let's plow on, regardless!
I've marked my idea of what the primary key should be for each table.
But, now we have a problem. If UserName is the PK of the Permissions
table, then, that table can have at most one record for a given User.
That means, a given User can have at most one SalesRepID. And, if
SalesRepID is the PK of the SalesRep table, that means that each
SalesRepID can have at most one CustomerID. So, putting all of that
together, each User can have at most one CustomerID. So the SalesReps
table is not required, and you could put the CustomerID directly in the
Permissions table!
That does not make sense. So, either, you have mis-designed the tables,
or I have mis-guessed the PK of one or more tables. Reviwing the
design, I'll change the PK of the Permissions table to include both
fields. This leads to a multi-field, or so-called "composite" primary
key. You're probably starting to see why it is so important that you
state your primary keys explicuitly when you describe the problem!
Permissions
UserName (P..)
SalesRepID (..K)
Now you should check, in the table design view, that you have defined
each primary key correctly. If you get the primary keying wrong,
everything goes out the window, and many things won't work the way you
think they should!
Third, by including CustomerID in the SalesReps table, you are saying:
"each sales rep has at most one current customer". If a sales rep can
have /several/ current customers, the CustomerID field should not be in
the SalesRep table. There should be a seperate table, called a
"joining" table. I can help you with that, if you need it. For the
moment, lets' go with what you currently have.
Forth, CustomerName /definitely should not/ be included in the
SalesReps table. That is a so-called "normalization" error. It will
cause you /loads/ of problems. You do not need it there. Your queries
can get it from the Customers table, via the CustomerID field. Again, a
single normalization error of this kind can stop the whole scheme
working.
Fifth, I wouldn't call the Permissions table that. It does not, in and
of itself, define any permissions. It just defines the SalesRepID for
each Access username. I think it would be better called, UserIDs, or
AccessUserSalesRepIDs, or somesuch. Let's ignore that for the moment.
So much for the table structures
Relationships
-------------
Like the primary keys, you are also way too vague about the
relationships. You can't just say, "table A is related to table B". HOW
is it related (there are three different possible ways: 0:1, 1:0, and
1:1)? WHAT FIELD(S) is it related through? Here's my view of the
relationships, given the structure defined above:
SalesReps
SalesRepID (PK)
CustomerID < 1:1 relationship to Customers.CustomerID
NO(CustomerName)
Permissions
UserName (P..)
SalesRepID (..K) < 1:1 relationship to SalesReps.SalesRepID
Customers
CustomerID (PK)
CustomerName
Next
----
I was going to continue on, but I think that I should stop at this
point, for you to confirm that you have got the primary keys &
relationships correct and/or that I have properly understood them.
HTH,
TC
Let's see if we can work through this
Table structures
----------------
First, multi-table qwuestions are too hard to understand, when you
describe them only as normal narrative text. You need to show the table
structures in some clearer way. Here's how I do it:
SalesReps
SalesRepID (PK?)
CustomerID
CustomerName
Permissions
UserName (PK?)
SalesRepID
You didn't define the Customers table, aso I'll make the obvious guess:
Customers
CustomerID (PK)
CustomerName
Second, it is an imperative, basic requirement of such problems, that
you clearly state the primary key (PK) of each table. The fact that you
haven't done that, makes me wonder whether you have defined them
correctly. I really should stop here, until you have done that.
However, let's plow on, regardless!
I've marked my idea of what the primary key should be for each table.
But, now we have a problem. If UserName is the PK of the Permissions
table, then, that table can have at most one record for a given User.
That means, a given User can have at most one SalesRepID. And, if
SalesRepID is the PK of the SalesRep table, that means that each
SalesRepID can have at most one CustomerID. So, putting all of that
together, each User can have at most one CustomerID. So the SalesReps
table is not required, and you could put the CustomerID directly in the
Permissions table!
That does not make sense. So, either, you have mis-designed the tables,
or I have mis-guessed the PK of one or more tables. Reviwing the
design, I'll change the PK of the Permissions table to include both
fields. This leads to a multi-field, or so-called "composite" primary
key. You're probably starting to see why it is so important that you
state your primary keys explicuitly when you describe the problem!
Permissions
UserName (P..)
SalesRepID (..K)
Now you should check, in the table design view, that you have defined
each primary key correctly. If you get the primary keying wrong,
everything goes out the window, and many things won't work the way you
think they should!
Third, by including CustomerID in the SalesReps table, you are saying:
"each sales rep has at most one current customer". If a sales rep can
have /several/ current customers, the CustomerID field should not be in
the SalesRep table. There should be a seperate table, called a
"joining" table. I can help you with that, if you need it. For the
moment, lets' go with what you currently have.
Forth, CustomerName /definitely should not/ be included in the
SalesReps table. That is a so-called "normalization" error. It will
cause you /loads/ of problems. You do not need it there. Your queries
can get it from the Customers table, via the CustomerID field. Again, a
single normalization error of this kind can stop the whole scheme
working.
Fifth, I wouldn't call the Permissions table that. It does not, in and
of itself, define any permissions. It just defines the SalesRepID for
each Access username. I think it would be better called, UserIDs, or
AccessUserSalesRepIDs, or somesuch. Let's ignore that for the moment.
So much for the table structures
Relationships
-------------
Like the primary keys, you are also way too vague about the
relationships. You can't just say, "table A is related to table B". HOW
is it related (there are three different possible ways: 0:1, 1:0, and
1:1)? WHAT FIELD(S) is it related through? Here's my view of the
relationships, given the structure defined above:
SalesReps
SalesRepID (PK)
CustomerID < 1:1 relationship to Customers.CustomerID
NO(CustomerName)
Permissions
UserName (P..)
SalesRepID (..K) < 1:1 relationship to SalesReps.SalesRepID
Customers
CustomerID (PK)
CustomerName
Next
----
I was going to continue on, but I think that I should stop at this
point, for you to confirm that you have got the primary keys &
relationships correct and/or that I have properly understood them.
HTH,
TC
T
TC
PS. The impact of giving the Permissions table the composite primary
key, is that now, each User can have any # of records in that table,
ie. any # of SalesRepIDs. However, in fact, what I said before still
stands, and you could /still/ discard the SalesRep table & put the
relevant SalesRepID directly in the Permissions table! - /if/ you dfid
not need to store any other information /specific to each individual
SalesRep/.
But if you /do/ need to store other information specific to each
SalesRep - eg. their dates of birth - then, the SalesRep table is
required.
The more that I think about it, the more I suspect that a SalesRep
/can/ have more than one current customer. If that is correct, your
table structure is quite wrong & needs to be re-done.
You absolutely have to get these things right, before you even /think/
of writing any code, or queries.
Don't be disillusioned about it! I spent years in a corporate software
development environment, where professional software developers would
constantly try to write SQL without knowing the primary keys of the
tables involved. Then they wondered why after literally /weeks/ of
fulltime work, they couldn't get a single query to return the correct
data.
Cheers,
TC
key, is that now, each User can have any # of records in that table,
ie. any # of SalesRepIDs. However, in fact, what I said before still
stands, and you could /still/ discard the SalesRep table & put the
relevant SalesRepID directly in the Permissions table! - /if/ you dfid
not need to store any other information /specific to each individual
SalesRep/.
But if you /do/ need to store other information specific to each
SalesRep - eg. their dates of birth - then, the SalesRep table is
required.
The more that I think about it, the more I suspect that a SalesRep
/can/ have more than one current customer. If that is correct, your
table structure is quite wrong & needs to be re-done.
You absolutely have to get these things right, before you even /think/
of writing any code, or queries.
Don't be disillusioned about it! I spent years in a corporate software
development environment, where professional software developers would
constantly try to write SQL without knowing the primary keys of the
tables involved. Then they wondered why after literally /weeks/ of
fulltime work, they couldn't get a single query to return the correct
data.
Cheers,
TC
G
Guest
Another solution would be to use a User Defined Function (VBA)
for the query criteria. In the User Defined Function you could
read a value from a form, or supply a default value, or return Null,
or Prompt for a value, or raise an error or ....
select * from qry where idx = gfnGetIDXfromForm()
function gfnGetIDXfromForm as long
on error resume next
gfnGetIDXfromForm = 0
gfnGetIDXfromForm = forms("myform").txt_idx
exit function
(david)
for the query criteria. In the User Defined Function you could
read a value from a form, or supply a default value, or return Null,
or Prompt for a value, or raise an error or ....
select * from qry where idx = gfnGetIDXfromForm()
function gfnGetIDXfromForm as long
on error resume next
gfnGetIDXfromForm = 0
gfnGetIDXfromForm = forms("myform").txt_idx
exit function
(david)
T
Tom Stoddard
TC said:Hi Tom
Let's see if we can work through this
Table structures
----------------
TC,
You have misinterpreted my narrative. Let me try to define my tables in the
format you've suggested.
tblSalesReps
SalesRepID (PK)
SalesRepName
tblCustomers
CustomerID (PK)
CustomerName
SalesRepID < many:1 Relationship with tblSalesRep.SalesRepID
tblPermissions
UserName (P..)
SalesRepID (..K) < many:1 relationship with tblSalesRepID.SalesRepID
I hope that's clear. I appreciate you showing me how to express this in
text. Please let me know if I could improve on what I've put down here.
As I originally stated, both relationships listed above have referential
integrity in effect with cascading updates and deletes. This structure
allows me to arbitrarily assign permission to any user to view/edit any
number of customers based on which salesrep is assigned to the customer. The
relationship between tblCustomers and tblPermissions is many-to-many so they
are not joined directly in the relationship window but rather they are
joined indirectly through their relationships with tblSalesReps. This works
well for filtering the records in tblCustomers but the resulting recordsets
are not updateable. I keep thinking that there is some simple, obvious
solution to this but I don't see it.
Thanks again for all of your help!
T
Tom Stoddard
David,
That was my original thought but I was led to believe that using
user-defined functions in the query grid would result in inefficient
queries. Am I mistaken?
That was my original thought but I was led to believe that using
user-defined functions in the query grid would result in inefficient
queries. Am I mistaken?
J
Joan Wild
I think you need to provide more information. From your description of the
tables and this statement...
but you have not defined a many - to - many with the tblSalesReps as the
linking table. There is no relationship between customers and permissions.
Have a look at Northwind; specifically the many to many relationship between
Employees and Customers and you'll see the difference. If there really is a
relationship between the two, then you need a third table tblCustPerm with
CustomerID and Username to create the link to tblCustomers and
tblPermissions
tblCustomers (1-m) tblCustPerm
tblPermissions (1-m) tblCustPerm
What is the sql of your form's and subform's recordsources? What are you
trying to accomplish? I think you want users in the tblpermissions to be
able to delete records on this form and subform. Provide the sql and we'll
see.
tables and this statement...
but you have not defined a many - to - many with the tblSalesReps as the
linking table. There is no relationship between customers and permissions.
Have a look at Northwind; specifically the many to many relationship between
Employees and Customers and you'll see the difference. If there really is a
relationship between the two, then you need a third table tblCustPerm with
CustomerID and Username to create the link to tblCustomers and
tblPermissions
tblCustomers (1-m) tblCustPerm
tblPermissions (1-m) tblCustPerm
What is the sql of your form's and subform's recordsources? What are you
trying to accomplish? I think you want users in the tblpermissions to be
able to delete records on this form and subform. Provide the sql and we'll
see.
T
Tom Stoddard
Joan Wild said:
Joan, there is a 1-m relationship between Permissions and SalesReps and a
1-m relationship between SalesReps and Customers. It seems inefficient to me
to have the create a permissions table with the username and customerid in
it when I can determine which customers a user has permissions on by
filtering the SalesReps table which in turn filters the Customers table. The
following SQL will return all customers that "Tom" is given permission to
access but it's not updateable:
SELECT tblCustomers.*
FROM (tblSalesReps INNER JOIN tblCustomers ON tblSalesReps.SalesRepID =
tblCustomers.SalesRepID) INNER JOIN tblPermissions ON
tblSalesReps.SalesRepID = tblPermissions.SalesID
WHERE tblPermissions.UserName = "Tom"
My structure is actually this:
tblSalesReps (1-m) tblPermissions
tblSalesReps (1-m) tblCustomers
or
tblPermissions (m-1) tblSalesReps (1-m) tblCustomers
I won't get into the subforms yet because I'm sure you'll have some ideas
for me based on what I've already given you but suffice it to say that the
subforms are just extensions of what I've described above. Customers have
(1-m) relationships with other tables which inturn have (1-m) relationships
with other tables. It's all very orderly and normalized. A salesrep has
multiple customers. A customer has multiple price lists. The price lists
have multiple grades. Each grades have sub-grades.... If I want to delete a
grade from a price list I need to have permissions granted for the salesrep.
The challenge is that I want multiple users with permissions on multiple
salesreps. That's where the permissions table comes in. It's really the
table that's in between Users and SalesReps to facilitate the (m-m)
relationship that they have but Users isn't a table, it could be if I wanted
it to be but that wouldn't make a difference. I get a user name from the
currentuser() functions so I don't need a table to provide me with it.
Thanks Again!
D
david epsom dot com dot au
Tom Stoddard said:
No, not mistaken. However, a UDF as shown makes negligible
difference most of the time.
The UDF as shown is evaluated twice before the query runs,
then the value is passed to the query engine. This is not
as efficient as hardcoding a number (ie "3") into a predeclared
query, but not much different from any other parameter query,
including your first example.
The real problems come when you have a UDF that is (or
appears to be) dependent on a value from the record. A
common example is when you have an ODBC connection and a
UDF in the criteria that needs to be evaluated for every
record:
"where [idx] = gfnMatch([idx])"
which forces local evaluation of the criteria, increasing
network traffic and preventing use of indexes and
optimisations.
The worst examples are when you have multiple fields dependent
on a calculation, like "gfnCalc([item])". The UDF needs to be
evaluated at least once for each dependent field on each
record. -A real killer if the UDF is slow (a real killer
if the UDF includes database lookups).
(david)
J
Joan Wild
Tom said:
From your description that isn't the case. There is a m-1 between
Permissions and SalesReps
(one SalesReps can have many users)
You need to create a junction table between customers and permissions. Yes
this will be circular, but this is a circumstance where that is warranted.
If you do this, you'll find your query will be updateable.
T
TC
Tom Stoddard wrote:
Looks good.
Looks good. Each customer has a single salesrep, but a single salesrep
can have many customers. Presumeable you have made SalesRepID a
non-required field in tblCustomers, so you can create a customer record
before you decide which salesrep is assigned to that customer, or you
can erase the SalesRepID value to indicate that the customer no longer
has a salesrep assigned.
The composite primary key means that a single Access user can manage
the customers for /many diferent/ sales represenatives - not just one.
Is that what you want?
Seems right to me.
This is correct /if/ tblPermissions retains the composite primary key;
ie. each Access user can manage the customers of many different sales
reps, not just one.
Ok - now show us the SQL!
PS. I simply have not had time to read Joan's replies. So if anything
differs, it's probably just that we differ in our interpretation of
what you are saying.
Cheers,
TC
Looks good.
Looks good. Each customer has a single salesrep, but a single salesrep
can have many customers. Presumeable you have made SalesRepID a
non-required field in tblCustomers, so you can create a customer record
before you decide which salesrep is assigned to that customer, or you
can erase the SalesRepID value to indicate that the customer no longer
has a salesrep assigned.
The composite primary key means that a single Access user can manage
the customers for /many diferent/ sales represenatives - not just one.
Is that what you want?
Seems right to me.
This is correct /if/ tblPermissions retains the composite primary key;
ie. each Access user can manage the customers of many different sales
reps, not just one.
Ok - now show us the SQL!
PS. I simply have not had time to read Joan's replies. So if anything
differs, it's probably just that we differ in our interpretation of
what you are saying.
Cheers,
TC
T
TC
I think I made that comment to you in regard to a previous post where
you proposed returning a "query criteria string" or somesuch from a
UDF? This implied a usage like the following:
SELECT ... FROM ... WHERE instr ( UDF(..), primarykey ) > 0
which is defintely not efficient.
HTH,
TC
you proposed returning a "query criteria string" or somesuch from a
UDF? This implied a usage like the following:
SELECT ... FROM ... WHERE instr ( UDF(..), primarykey ) > 0
which is defintely not efficient.
HTH,
TC
T
Tom Stoddard
But where do I stop? If that's the case then I have to create junctionJoan Wild said:
tables between tblPermissions and any other table I want to be able to edit.
For example, if I want an updateable query of price lists I now have to
create a junction table between Permissions and PriceLists. I could end up
with 6 or 7 junction tables which I'd then have to maintain. I'd have to
update all of those tables everytime I added a sales rep or user and
everytime I modified any permissions.
Right now I'm working around these restrictions by using parametized queries
and supplying the paremeters either in code or from forms based on queries
which are not updateable. I'm not sure which approach is worse but at least
the queries don't require me to store a whole bunch of redundant data.
Thanks Again!
T
Tom Stoddard
TC said:
Your interpretation is accurate. I do have a restriction on customers,
however, which requires them to have a salesrep assigned. This is a business
rule which my company follows so I thought it made sense to do the same.
Would that make a difference to the updateability of the following query?
The following SQL will return all customers that "Tom" is given permission
to access but it's not updateable:
SELECT tblCustomers.*
FROM (tblSalesReps INNER JOIN tblCustomers ON tblSalesReps.SalesRepID =
tblCustomers.SalesRepID) INNER JOIN tblPermissions ON
tblSalesReps.SalesRepID = blPermissions.SalesRepID WHERE
tblPermissions.UserName = "Tom"
I think I understand why this is so but I don't see an easy way around it.
Joan has suggested creating a "junction" table between Customers and
Permissions. I've tried that and it will work if all I want to edit are
records in the Customers table but if I want to edit records in table
further down the chain (for example, a customer's price list) then I'm right
back where I started.
I'm starting to realize that there is no easy solution here but thinking it
through so thoroughly has certainly helped me to understand it better and to
work around it more efficiently.
Thanks!
T
TC
Tom said:
Ok, so we have the table structure correct
No, it shouldn't make any differnce to that. Just make the SalesRepID
field mandatory in the Customers table.
You are only selecting the fields from one of the tables involved.
IIRC, you may need to select /all/ the relevant fields before the query
is updatable. Change "SELECT tblCustomers.*", to "SELECT *", and see if
that makes it updatable.
Undoubtedly a very common error in Access databases is, an improperly
designed database structure. You have to get the tables, primary keys,
foreign keys, and relationships, all 100% correct, before it will all
work properly.
Tell me if the change suggested above, makes your query updatable. If
it doesn't, tell me whether you'd like me to replicate your structure &
query at home & try it myself. I'm willing to do that /if/ you still
want to get it working.
Cheers,
TC
T
Tom Stoddard
I tried this and still had no success. I just discovered, however, that if I
change the query's recordset type property to Dynaset (inconsistent updates)
that I can edit these fields. I don't recall ever using this setting. Has it
always been available in Access? Is there a good reason not to use it? If
not, it might be my "easy solution". I can't believe I hadn't tried it
before. I guess the term "inconsistent" scared me away. From what I can find
online about it, it sort of bypasses some of the restrictions that
referential integrity enforces. I believe I can use it safely in my
application.
I appreciate the help you're offering but I don't want you to spend too much
time on it. The dialog we're having in this newsgroup is extremely helpful
to me.
Thanks!
J
Joan Wild
Tom said:
That is not true. Your price lists are related to your Customers which in
turn is related to the tblPermissions.
I'm bowing out of this, as TC and I are working at cross-purposes.
T
TC
'Ello, 'ello! I thought you were trying the updatability by opening
the query directly & typing into the datasheet?
A whole lot of other factors come in, if you are opening a recordset.
I'm sure that in principle, your query should be updatable. I don't
have Access on this PC, but I'll copy the details to diskette now, &
see if I can try it myself, sometime in the next few days.
Cheers,
TC
the query directly & typing into the datasheet?
A whole lot of other factors come in, if you are opening a recordset.
I'm sure that in principle, your query should be updatable. I don't
have Access on this PC, but I'll copy the details to diskette now, &
see if I can try it myself, sometime in the next few days.
Cheers,
TC
T
TC
Sorry, didn't mean to confuse the issue! Tom says he is learning from
all the replies, so neither of us are wasting our time here IMHO
TC
all the replies, so neither of us are wasting our time here IMHO
TC
J
Joan Wild
Don't apologize. You're not confusing the issue. It's just that there are
three very different sub-threads here, and I think Tom needs to work on one
thing at a time, since they are all related - get the design right first,
and other things should fall into place.
three very different sub-threads here, and I think Tom needs to work on one
thing at a time, since they are all related - get the design right first,
and other things should fall into place.