Query help

  • Thread starter Thread starter PremierConsultant
  • Start date Start date
P

PremierConsultant

I have a list of 65 user template. I need help creating a query that will
compare items in a template table to items in the user table and append the
template name to a applied template column in the user table where all the
criteria match.

Thanks,

Shane
 
Shane

One item? Two? A dozen?

Compare ... ?exact match? Close match? Starts with... match? Ends with...
match?

Table structures?

More info, please!

Regards

Jeff Boyce
Microsoft Office/Access MVP
 
Hi Jeff,

There are about 15 items. The same items live in 2 tables. There is no
common key between the 2 tables. I want to an exact match. For example, I
want [User].[securitycls] where = [Template].[Securitycls] and
[User].[defaultsecCls] where [Template].[defaultsecCls], etc...

Now my queries read( Select [User].[securitycls] where = "Template Security
Class" and [User].[defaultSecCls] where = "defaultSecCls", etc...

The user table is set up

User 1, UserName="EMPONE", Securitycls="Template Security Class",
DefaultSecurity="", etc

Template table is set up

Template 1= Template Name="SecTempOne", Securitycls="Template Security
Class", DefaultSecurity="Default Security Class"
Template 2=Template Name="SecTempTwo", Securitycls="Template Security
Class", DefaultSecurity=Null

If all the criteria match, I want to append the [Template].[TemplateName] to
the cooresponding records in the User Table

I don't want to create 65 different queries unless it's absolutely necessary.

Thanks,

Shane
 
Shane

I'm still having some difficulty visualizing the data structure...

Could you re-post with a description that parallels the rather simplistic
example that follows?

Here's an admittedly simplistic student enrollment example:

tblStudent
StudentID
FirstName
LastName
DateOfBirth

tblClass
ClassID
ClassTitle
ClassDescription

trelEnrollment
EnrollmentID
StudentID
ClassID
DateOfEnrollment

Regards

Jeff Boyce
Microsoft Office/Access MVP
 
I appologize for dragging this out, but here is the table structure.

tblTemplate
TempID
TempName
SecurityModuleOne
SecurityModuleTwo
SecurityModuleThree
SecurityModuleFour
SecurityModuleFive
SecurityModuleSix
SecurityModuleSeven
SecurityModuleEight
SecurityModuleNine
SecurityModuleTen
SecurityModuleEleven
SecurityModuleTwelve
SecurityModuleThirteen
SecurityModuleFourteen
SecurityModuleFifteen

tblUser
UserID
UserName
SecurityModuleOne
SecurityModuleTwo
SecurityModuleThree
SecurityModuleFour
SecurityModuleFive
SecurityModuleSix
SecurityModuleSeven
SecurityModuleEight
SecurityModuleNine
SecurityModuleTen
SecurityModuleEleven
SecurityModuleTwelve
SecurityModuleThirteen
SecurityModuleFourteen
SecurityModuleFifteen
TemplateName

I created a query called qry SecurityTemplate1 against the tblUser. I added
the fields for all the security modules. In the criteria for
tblUser.SecurityModuleONE, I set SecurityModuleOne equal to the value in the
tblTemplate.SecurityModuleONE.
The query reads:
Select SecurityModuleOne,SecurityModuleTwo,SecurityModuleThree, etc...
FROM tblUser
WHERE SecurityModuleOne ="Allow Function One", SecurityModuleTwo = "Allowed
Funciton Two", SecurityModuleThree = is Null, etc...
 
When I see "repeating columns" ([SecurityModuleOne], [SecurityModuleTwo],
....) I immediately think of ... spreadsheets!

Before you try anything else, I urge you to spend the time to come up to
speed on the process of normalization. Access is a relational database, and
"expects" well-normalized relational data, not 'sheet data.

With the design you've posted, you have a maintenance nightmare. I'm
guessing that if you ever need to add just one more Security Module, you
need to modify your tables' structures, your queries, forms, reports, code,
macros ...?!

In a well-normalized design, adding (or removing, or editing) a security
module would require just one (new/deleted/altered) row.

If "normalization" and "relational database" aren't familiar terms, plan on
learning more about them if you want you (and Access) to work easier...

Good luck

Regards

Jeff Boyce
Microsoft Office/Access MVP
 
Yes the data comes from spread sheets through a linked table. I may have to
import the data instead of linking.

Thanks for you time.

Shane

Jeff Boyce said:
When I see "repeating columns" ([SecurityModuleOne], [SecurityModuleTwo],
....) I immediately think of ... spreadsheets!

Before you try anything else, I urge you to spend the time to come up to
speed on the process of normalization. Access is a relational database, and
"expects" well-normalized relational data, not 'sheet data.

With the design you've posted, you have a maintenance nightmare. I'm
guessing that if you ever need to add just one more Security Module, you
need to modify your tables' structures, your queries, forms, reports, code,
macros ...?!

In a well-normalized design, adding (or removing, or editing) a security
module would require just one (new/deleted/altered) row.

If "normalization" and "relational database" aren't familiar terms, plan on
learning more about them if you want you (and Access) to work easier...

Good luck

Regards

Jeff Boyce
Microsoft Office/Access MVP


PremierConsultant said:
I appologize for dragging this out, but here is the table structure.

tblTemplate
TempID
TempName
SecurityModuleOne
SecurityModuleTwo
SecurityModuleThree
SecurityModuleFour
SecurityModuleFive
SecurityModuleSix
SecurityModuleSeven
SecurityModuleEight
SecurityModuleNine
SecurityModuleTen
SecurityModuleEleven
SecurityModuleTwelve
SecurityModuleThirteen
SecurityModuleFourteen
SecurityModuleFifteen

tblUser
UserID
UserName
SecurityModuleOne
SecurityModuleTwo
SecurityModuleThree
SecurityModuleFour
SecurityModuleFive
SecurityModuleSix
SecurityModuleSeven
SecurityModuleEight
SecurityModuleNine
SecurityModuleTen
SecurityModuleEleven
SecurityModuleTwelve
SecurityModuleThirteen
SecurityModuleFourteen
SecurityModuleFifteen
TemplateName

I created a query called qry SecurityTemplate1 against the tblUser. I
added
the fields for all the security modules. In the criteria for
tblUser.SecurityModuleONE, I set SecurityModuleOne equal to the value in
the
tblTemplate.SecurityModuleONE.
The query reads:
Select SecurityModuleOne,SecurityModuleTwo,SecurityModuleThree, etc...
FROM tblUser
WHERE SecurityModuleOne ="Allow Function One", SecurityModuleTwo =
"Allowed
Funciton Two", SecurityModuleThree = is Null, etc...
Click to expand...
Click to expand...
 
Shane

Hold on, "importing" doesn't change the structure any more than "linking"
does.

Taking 'sheet data and converting into well-normalized tables is something
best done by USB ... using someone's brain!

Regards

Jeff Boyce
Microsoft Office/Access MVP

PremierConsultant said:
Yes the data comes from spread sheets through a linked table. I may have
to
import the data instead of linking.

Thanks for you time.

Shane

Jeff Boyce said:
When I see "repeating columns" ([SecurityModuleOne], [SecurityModuleTwo],
....) I immediately think of ... spreadsheets!

Before you try anything else, I urge you to spend the time to come up to
speed on the process of normalization. Access is a relational database,
and
"expects" well-normalized relational data, not 'sheet data.

With the design you've posted, you have a maintenance nightmare. I'm
guessing that if you ever need to add just one more Security Module, you
need to modify your tables' structures, your queries, forms, reports,
code,
macros ...?!

In a well-normalized design, adding (or removing, or editing) a security
module would require just one (new/deleted/altered) row.

If "normalization" and "relational database" aren't familiar terms, plan
on
learning more about them if you want you (and Access) to work easier...

Good luck

Regards

Jeff Boyce
Microsoft Office/Access MVP


in
message news:[email protected]...
I appologize for dragging this out, but here is the table structure.

tblTemplate
TempID
TempName
SecurityModuleOne
SecurityModuleTwo
SecurityModuleThree
SecurityModuleFour
SecurityModuleFive
SecurityModuleSix
SecurityModuleSeven
SecurityModuleEight
SecurityModuleNine
SecurityModuleTen
SecurityModuleEleven
SecurityModuleTwelve
SecurityModuleThirteen
SecurityModuleFourteen
SecurityModuleFifteen

tblUser
UserID
UserName
SecurityModuleOne
SecurityModuleTwo
SecurityModuleThree
SecurityModuleFour
SecurityModuleFive
SecurityModuleSix
SecurityModuleSeven
SecurityModuleEight
SecurityModuleNine
SecurityModuleTen
SecurityModuleEleven
SecurityModuleTwelve
SecurityModuleThirteen
SecurityModuleFourteen
SecurityModuleFifteen
TemplateName

I created a query called qry SecurityTemplate1 against the tblUser. I
added
the fields for all the security modules. In the criteria for
tblUser.SecurityModuleONE, I set SecurityModuleOne equal to the value
in
the
tblTemplate.SecurityModuleONE.
The query reads:
Select SecurityModuleOne,SecurityModuleTwo,SecurityModuleThree, etc...
FROM tblUser
WHERE SecurityModuleOne ="Allow Function One", SecurityModuleTwo =
"Allowed
Funciton Two", SecurityModuleThree = is Null, etc...



:

Shane

I'm still having some difficulty visualizing the data structure...

Could you re-post with a description that parallels the rather
simplistic
example that follows?

Here's an admittedly simplistic student enrollment example:

tblStudent
StudentID
FirstName
LastName
DateOfBirth

tblClass
ClassID
ClassTitle
ClassDescription

trelEnrollment
EnrollmentID
StudentID
ClassID
DateOfEnrollment

Regards

Jeff Boyce
Microsoft Office/Access MVP
Click to expand...
Click to expand...
Click to expand...
 
Back
Top