Query Help

  • Thread starter Thread starter MsWatts
  • Start date Start date
M

MsWatts

Good Morning,

I have come here for help a few times but due to security access at the time
I was unable to post the info needed to get my issues resolved. I hope
someone can help.

I am currently creting a training database that will list
1. All Employees
2. All Equipment
3. The Employee's training level on each piece of equipment. (Which can be
different)

I am creating a form that will list this information and to keep track of
the training status of all employees. This will keep us informed on which
training each employee needs.

I have also created a query that we can use to rotate employess based on the
training they have. However each time I try to run the query it pulls the
data multiple times giving me duplicate info or the info won't pull at all.
Please help me resolve this. I am new to Access and barely know what I am
doing so please bare with me.

Please see the SQL below (yes it's very long I'm sorry):
SELECT [Employee Data].PSID, [Employee Data].LastName, [Employee
Data].FirstName, Shifts.Shifts, [Certification status].[Certification
Status], [Packaging Equipment].[Packaging Equipment]
FROM [Packaging Equipment] INNER JOIN (([Packaging Data 4] INNER JOIN
((([Certification status] INNER JOIN [Packaging Data] ON [Certification
status].CertificationID = [Packaging Data].CertificationID) INNER JOIN
[Packaging Data 2] ON [Certification status].CertificationID2 = [Packaging
Data 2].CertificationID2) INNER JOIN [Packaging Data 3] ON [Certification
status].CertificationID3 = [Packaging Data 3].CertificationID3) ON [Packaging
Data 4].CertificationID4 = [Certification status].CertificationID4) INNER
JOIN ([Employee Data] INNER JOIN Shifts ON [Employee Data].Shift =
Shifts.Shifts) ON ([Packaging Data].PSID = [Employee Data].PSID) AND
([Packaging Data 2].PSID = [Employee Data].PSID) AND ([Packaging Data 4].PSID
= [Employee Data].PSID) AND ([Packaging Data 3].PSID = [Employee Data].PSID))
ON ([Packaging Equipment].PackagingID4 = [Packaging Data 4].PackagingID4) AND
([Packaging Equipment].PackagingID3 = [Packaging Data 3].PackagingID3) AND
([Packaging Equipment].PackagingID2 = [Packaging Data 2].PackagingID2) AND
([Packaging Equipment].PackagingID = [Packaging Data].PackagingID)
WHERE (((Shifts.Shifts)=[forms]![packaging query form]![shifts] Or
[forms]![packaging query form]![shifts] Is Null) AND (([Certification
status].[Certification Status])=[forms]![packaging query form]![certification
status] Or [forms]![packaging query form]![certification status] Is Null) AND
(([Packaging Equipment].[Packaging Equipment])=[forms]![packaging query
form]![equipment] Or [forms]![packaging query form]![equipment] Is Null));
 
The root of the database's current and future problems is this: Packaging
Data 1, Packaging Data 2, Packaging Data 3, and Packaging Data 4. It
certainly looks like you have 4 tables doing the job of one.

You really, really need to redesign your basic table structure. I highly
recommend getting some relational database training or reading "Database
Design for Mere Mortals" by Hernandez before proceeding any further on this
database.
--
Jerry Whittle, Microsoft Access MVP
Light. Strong. Cheap. Pick two. Keith Bontrager - Bicycle Builder.


MsWatts said:
Good Morning,

I have come here for help a few times but due to security access at the time
I was unable to post the info needed to get my issues resolved. I hope
someone can help.

I am currently creting a training database that will list
1. All Employees
2. All Equipment
3. The Employee's training level on each piece of equipment. (Which can be
different)

I am creating a form that will list this information and to keep track of
the training status of all employees. This will keep us informed on which
training each employee needs.

I have also created a query that we can use to rotate employess based on the
training they have. However each time I try to run the query it pulls the
data multiple times giving me duplicate info or the info won't pull at all.
Please help me resolve this. I am new to Access and barely know what I am
doing so please bare with me.

Please see the SQL below (yes it's very long I'm sorry):
SELECT [Employee Data].PSID, [Employee Data].LastName, [Employee
Data].FirstName, Shifts.Shifts, [Certification status].[Certification
Status], [Packaging Equipment].[Packaging Equipment]
FROM [Packaging Equipment] INNER JOIN (([Packaging Data 4] INNER JOIN
((([Certification status] INNER JOIN [Packaging Data] ON [Certification
status].CertificationID = [Packaging Data].CertificationID) INNER JOIN
[Packaging Data 2] ON [Certification status].CertificationID2 = [Packaging
Data 2].CertificationID2) INNER JOIN [Packaging Data 3] ON [Certification
status].CertificationID3 = [Packaging Data 3].CertificationID3) ON [Packaging
Data 4].CertificationID4 = [Certification status].CertificationID4) INNER
JOIN ([Employee Data] INNER JOIN Shifts ON [Employee Data].Shift =
Shifts.Shifts) ON ([Packaging Data].PSID = [Employee Data].PSID) AND
([Packaging Data 2].PSID = [Employee Data].PSID) AND ([Packaging Data 4].PSID
= [Employee Data].PSID) AND ([Packaging Data 3].PSID = [Employee Data].PSID))
ON ([Packaging Equipment].PackagingID4 = [Packaging Data 4].PackagingID4) AND
([Packaging Equipment].PackagingID3 = [Packaging Data 3].PackagingID3) AND
([Packaging Equipment].PackagingID2 = [Packaging Data 2].PackagingID2) AND
([Packaging Equipment].PackagingID = [Packaging Data].PackagingID)
WHERE (((Shifts.Shifts)=[forms]![packaging query form]![shifts] Or
[forms]![packaging query form]![shifts] Is Null) AND (([Certification
status].[Certification Status])=[forms]![packaging query form]![certification
status] Or [forms]![packaging query form]![certification status] Is Null) AND
(([Packaging Equipment].[Packaging Equipment])=[forms]![packaging query
form]![equipment] Or [forms]![packaging query form]![equipment] Is Null));
Click to expand...
 
Uh Oh! this MUST be bad! You referred me to a book which leads me to believe
I can't be helped LoL :o)

That's definitely not what I was intending to do at all. I orignally had one
table but I decided to break it down to 4.

I definitely need training. Luckily I catch on pretty quickly if I'm given a
little guidance. I have no problem starting over. Do you have any internet
links that would be useful?

Jerry Whittle said:
The root of the database's current and future problems is this: Packaging
Data 1, Packaging Data 2, Packaging Data 3, and Packaging Data 4. It
certainly looks like you have 4 tables doing the job of one.

You really, really need to redesign your basic table structure. I highly
recommend getting some relational database training or reading "Database
Design for Mere Mortals" by Hernandez before proceeding any further on this
database.
--
Jerry Whittle, Microsoft Access MVP
Light. Strong. Cheap. Pick two. Keith Bontrager - Bicycle Builder.


MsWatts said:
Good Morning,

I have come here for help a few times but due to security access at the time
I was unable to post the info needed to get my issues resolved. I hope
someone can help.

I am currently creting a training database that will list
1. All Employees
2. All Equipment
3. The Employee's training level on each piece of equipment. (Which can be
different)

I am creating a form that will list this information and to keep track of
the training status of all employees. This will keep us informed on which
training each employee needs.

I have also created a query that we can use to rotate employess based on the
training they have. However each time I try to run the query it pulls the
data multiple times giving me duplicate info or the info won't pull at all.
Please help me resolve this. I am new to Access and barely know what I am
doing so please bare with me.

Please see the SQL below (yes it's very long I'm sorry):
SELECT [Employee Data].PSID, [Employee Data].LastName, [Employee
Data].FirstName, Shifts.Shifts, [Certification status].[Certification
Status], [Packaging Equipment].[Packaging Equipment]
FROM [Packaging Equipment] INNER JOIN (([Packaging Data 4] INNER JOIN
((([Certification status] INNER JOIN [Packaging Data] ON [Certification
status].CertificationID = [Packaging Data].CertificationID) INNER JOIN
[Packaging Data 2] ON [Certification status].CertificationID2 = [Packaging
Data 2].CertificationID2) INNER JOIN [Packaging Data 3] ON [Certification
status].CertificationID3 = [Packaging Data 3].CertificationID3) ON [Packaging
Data 4].CertificationID4 = [Certification status].CertificationID4) INNER
JOIN ([Employee Data] INNER JOIN Shifts ON [Employee Data].Shift =
Shifts.Shifts) ON ([Packaging Data].PSID = [Employee Data].PSID) AND
([Packaging Data 2].PSID = [Employee Data].PSID) AND ([Packaging Data 4].PSID
= [Employee Data].PSID) AND ([Packaging Data 3].PSID = [Employee Data].PSID))
ON ([Packaging Equipment].PackagingID4 = [Packaging Data 4].PackagingID4) AND
([Packaging Equipment].PackagingID3 = [Packaging Data 3].PackagingID3) AND
([Packaging Equipment].PackagingID2 = [Packaging Data 2].PackagingID2) AND
([Packaging Equipment].PackagingID = [Packaging Data].PackagingID)
WHERE (((Shifts.Shifts)=[forms]![packaging query form]![shifts] Or
[forms]![packaging query form]![shifts] Is Null) AND (([Certification
status].[Certification Status])=[forms]![packaging query form]![certification
status] Or [forms]![packaging query form]![certification status] Is Null) AND
(([Packaging Equipment].[Packaging Equipment])=[forms]![packaging query
form]![equipment] Or [forms]![packaging query form]![equipment] Is Null));
Click to expand...
Click to expand...
 
Sure you can be helped especially if you want to. Getting a database designed
correctly in the first place will save you tons of grief in the long run.

Here's a good paper
http://www.deeptraining.com/litwin/dbdesign/FundamentalsOfRelationalDatabaseDesign.aspx

Here's a document from Microsoft:
http://support.microsoft.com/kb/283698
--
Jerry Whittle, Microsoft Access MVP
Light. Strong. Cheap. Pick two. Keith Bontrager - Bicycle Builder.


MsWatts said:
Uh Oh! this MUST be bad! You referred me to a book which leads me to believe
I can't be helped LoL :o)

That's definitely not what I was intending to do at all. I orignally had one
table but I decided to break it down to 4.

I definitely need training. Luckily I catch on pretty quickly if I'm given a
little guidance. I have no problem starting over. Do you have any internet
links that would be useful?

Jerry Whittle said:
The root of the database's current and future problems is this: Packaging
Data 1, Packaging Data 2, Packaging Data 3, and Packaging Data 4. It
certainly looks like you have 4 tables doing the job of one.

You really, really need to redesign your basic table structure. I highly
recommend getting some relational database training or reading "Database
Design for Mere Mortals" by Hernandez before proceeding any further on this
database.
--
Jerry Whittle, Microsoft Access MVP
Light. Strong. Cheap. Pick two. Keith Bontrager - Bicycle Builder.


MsWatts said:
Good Morning,

I have come here for help a few times but due to security access at the time
I was unable to post the info needed to get my issues resolved. I hope
someone can help.

I am currently creting a training database that will list
1. All Employees
2. All Equipment
3. The Employee's training level on each piece of equipment. (Which can be
different)

I am creating a form that will list this information and to keep track of
the training status of all employees. This will keep us informed on which
training each employee needs.

I have also created a query that we can use to rotate employess based on the
training they have. However each time I try to run the query it pulls the
data multiple times giving me duplicate info or the info won't pull at all.
Please help me resolve this. I am new to Access and barely know what I am
doing so please bare with me.

Please see the SQL below (yes it's very long I'm sorry):
SELECT [Employee Data].PSID, [Employee Data].LastName, [Employee
Data].FirstName, Shifts.Shifts, [Certification status].[Certification
Status], [Packaging Equipment].[Packaging Equipment]
FROM [Packaging Equipment] INNER JOIN (([Packaging Data 4] INNER JOIN
((([Certification status] INNER JOIN [Packaging Data] ON [Certification
status].CertificationID = [Packaging Data].CertificationID) INNER JOIN
[Packaging Data 2] ON [Certification status].CertificationID2 = [Packaging
Data 2].CertificationID2) INNER JOIN [Packaging Data 3] ON [Certification
status].CertificationID3 = [Packaging Data 3].CertificationID3) ON [Packaging
Data 4].CertificationID4 = [Certification status].CertificationID4) INNER
JOIN ([Employee Data] INNER JOIN Shifts ON [Employee Data].Shift =
Shifts.Shifts) ON ([Packaging Data].PSID = [Employee Data].PSID) AND
([Packaging Data 2].PSID = [Employee Data].PSID) AND ([Packaging Data 4].PSID
= [Employee Data].PSID) AND ([Packaging Data 3].PSID = [Employee Data].PSID))
ON ([Packaging Equipment].PackagingID4 = [Packaging Data 4].PackagingID4) AND
([Packaging Equipment].PackagingID3 = [Packaging Data 3].PackagingID3) AND
([Packaging Equipment].PackagingID2 = [Packaging Data 2].PackagingID2) AND
([Packaging Equipment].PackagingID = [Packaging Data].PackagingID)
WHERE (((Shifts.Shifts)=[forms]![packaging query form]![shifts] Or
[forms]![packaging query form]![shifts] Is Null) AND (([Certification
status].[Certification Status])=[forms]![packaging query form]![certification
status] Or [forms]![packaging query form]![certification status] Is Null) AND
(([Packaging Equipment].[Packaging Equipment])=[forms]![packaging query
form]![equipment] Or [forms]![packaging query form]![equipment] Is Null));
Click to expand...
Click to expand...
Click to expand...
 
I definitely need training. Luckily I catch on pretty quickly if I'm given a
little guidance. I have no problem starting over. Do you have any internet
links that would be useful?
Click to expand...

Here's my list... I'm sure Jerry will forgive my butting in...

Jeff Conrad's resources page:
http://www.accessmvp.com/JConrad/accessjunkie/resources.html

The Access Web resources page:
http://www.mvps.org/access/resources/index.html

Roger Carlson's tutorials, samples and tips:
http://www.rogersaccesslibrary.com/

A free tutorial written by Crystal:
http://allenbrowne.com/casu-22.html

A video how-to series by Crystal:
http://www.YouTube.com/user/LearnAccessByCrystal

MVP Allen Browne's tutorials:
http://allenbrowne.com/links.html#Tutorials

If you wish, you might want to repost your current table structure, with some
description of the meaning of the tables and the fields if that's not obvious;
posting in the "database design" forum might be better than here:

http://www.microsoft.com/office/com...ft.public.access.tablesdbdesign&lang=en&cr=US
 
Back
Top