Query Help

[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));

 

[Jerry Whittle]

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.

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));

 

[MsWatts]

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

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?

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.

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));

 

[Jerry Whittle]

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.

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

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?

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.

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));

 

[John W. Vinson]

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?

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

 

[MsWatts]

Thank you both so much! I'm going to read them now.