Query Building

  • Thread starter Thread starter GERRYM
  • Start date Start date
G

GERRYM

I have a db with 59,000 records. Two fields one is a dept
code and the other a permission code. I also have a
matrix (on paper) of acceptable combinations of dept code
and permission codes. I want to write a query to show
where the combinations are wrong by saying =dept code
= "DLR" and Permission <> "OPen" and Permission <> "close"
or dept = "ACC" and Permission <> "Modify" and Permission
<> "FINAL" and so on. The problem is that the linit of
the string in the query builder is too small. Can any one
suggest another way of achieving this.

Thanks
 
GERRYM said:
I have a db with 59,000 records. Two fields one is a dept
code and the other a permission code. I also have a
matrix (on paper) of acceptable combinations of dept code
and permission codes. I want to write a query to show
where the combinations are wrong by saying =dept code
= "DLR" and Permission <> "OPen" and Permission <> "close"
or dept = "ACC" and Permission <> "Modify" and Permission
<> "FINAL" and so on. The problem is that the linit of
the string in the query builder is too small. Can any one
suggest another way of achieving this.
Click to expand...
Hi Gerry,

How many dept and permission codes are there?

If it is a small, discrete set,
I might create a 2 field table "CanBe"

Dept Permission
DLR Modify
DLR FINAL
ACC Open
ACC close

If this is doable, then just left join
your original table to CanBe on
Dept and Permission fields, with
criteria under CanBe.Permission

IS NULL

for example:

SELECT t.*
FROM originaltable As t
LEFT JOIN CanBe
ON
t.Dept = CanBe.Dept
AND
t.Permission = CanBe.Permission
WHERE
CanBe.Permission IS NULL;

This will return all the records from your original
table that "cannot be."

(change "originaltable" to name of your table,
as well as field names if different)

Setting up a "CannotBe" table may be easier
to enter data depending upon your situation,
but, either way, I strongly suggest this "table
method" so you can easily edit when changes
need to be made (as always happens).

Please respond back if I have misunderstood
or was not clear about something.

Good luck,

Gary Walter
 
Hi Gary

In total I have 9 departments and 16 possible
permissions. In this matrix there is a possible 144
combinations, however only 36 are valid and what I want to
do is catch the invalid setting. This would seem like I
need to create a table as you suggest but with cannot be
permissions.

What do you think

Thanks
Gerry
 
Gary

The second table now seems to work. So now I have another
added problem as I said I have a table with Department and
permission codes, this table also has one more field
called portfolio and where part of the name of a portfolio
is "CASH-SA" then a department which would not normally be
allowed have permission "CREATE" is allowed to have it.

Hope this is clear

Thanks again for your help

Gerry
 
Hi Gerry,

Maybe through no fault of your own, you
have a poorly-designed db. I have (many
times) been forced to work with data like
this because it was a "printer dump," or a
"web log," etc., and the goal of the business
was to *immediately* "slice-and-dice it."

But if that is not the case, then I would be
giving short-shrift if I did not advice you
that your data is not normalized.

What does that mean?

John Vinson once posted the following links:

ACC2002: Database Normalization Basics
http://support.microsoft.com/?id=283878

Database Normalization Tips by Luke Chung
http://www.fmsinc.com/tpapers/genaccess/databasenorm.html

Data Normalization Fundamentals by Luke Chung
http://www.fmsinc.com/tpapers/datanorm/index.html

324613 - Support WebCast: Database Normalization Basics
http://support.microsoft.com/?id=324613

Other maybe-helpful KB articles include:

164172 - "Understanding Relational Database Design Available on MSL"
175939 - "White Paper: Avoiding the Top Ten Design Mistakes"
100139 - "Database Normalization Basics"
234208 - "Database Normalization Basics"
234208 - ACC2000: "Understanding Relational Database Design" Document Available in
Download Center
209534 - ACC2000: Database Normalization Basics

Some helpful books include:

Michael Hernandez's "Database Design for Mere Mortals"
Michael's and John Viescas's "SQL Queries for Mere Mortals"

There also is a wizard in Access that may offer suggestions
(Tools/Analyze/Table)
140636 ACC: How the Table Analyzer Wizard Works

A *very brief* overview breaks down normalization into
"steps of normalization completion":

1st Normal

Only store one data value in a field
No repeating fields

2nd Normal

Every non-key field in a table must depend only on the primary key

3rd Normal

Fields in the record depend on the *whole* primary key and not on any one part of
the key

"The key, the whole key, and nothing but the key, so help me Codd"

4th Normal

A record contains data that belong together in a meaningful way.

(there is a 5th Normal form, but I don't have a "one-sentence
summation" for it)

These are all "itemized rules" that will help you reduce
redundant data and make your db more efficient.

The point being that if you have the option to take the
time to design a db so it is normalized, queries become
a lot easier (you don't end up with a field like portfolio
storing one word within a string that determines whether
the record is valid).

Having said all this, if you are in one of those
business situations where you "just need to get
it done," one solution comes to mind.

For now, you could add a field to your table
(say "ValidCode" type Yes/No, default value=0)

run an update query setting ValidCode to -1 (Yes)
based on your table (did you create a "CanBe"
or a "CannotBe" table).

then run a second update query updating
ValidCode to -1

WHERE
Department IN ('DLR', 'ACC')
AND
permissioncode = "CREATE"
AND
portfolio Like "*" & "CASH-SA" & "*";

(only you know what depts go in the
"IN (...) list, or maybe you don't even
need this part of the WHERE clause
if only "CREATE/CASH-SA" are what
is important)

or...in your query move the "Join" to the
WHERE clause and include above as an
additional "OR" clause.

For instance, if you used a "CannotBe" table,
and you want to return invalid records...

SELECT DISTINCT t.*
FROM originaltable As t, CannotBe
WHERE
(t.Dept = CannotBe.Dept
AND
t.Permission = CannotBe.Permission)
OR
(Dept IN ('DLR', 'ACC')
AND
permissioncode <> "CREATE"
AND
portfolio Like "*" & "CASH-SA" & "*");

Again, maybe you don't need the
"Dept IN(...)" part. Plus this is untested.

It might just "in the background" move
the WHERE up into the JOIN anyway,
so (if you don't need "Dept IN (..)"),
you might type out a SQL like:

SELECT t.*
FROM originaltable As t
INNER JOIN CannotBe
ON
(t.Dept = CannotBe.Dept
AND
t.Permission = CannotBe.Permission)
OR
(t.permissioncode = "CREATE"
AND
t.portfolio Not Like "*" & "CASH-SA" & "*");

I'm sorry I cannot be of more help.

If I had the time, I would rather get an
attachment from you and see if we could
redesign your table, but we are in crunch time
here at work.

Good luck,

Gary Walter
 
Gary

Many Thanks I will try some of your suggestions. I know
the db is not normalised but I must use it as it is. It is
a dump from a db and it shows which permissions users have
to particular features, so if I change the Dump Auditors
will not be happy as I will not be working from original
data.

Again Many thanks

Gerry
 
Back
Top