From: MsWatts on
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));

From: Jerry Whittle on
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" wrote:

> 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));
>
From: MsWatts on
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" wrote:

> 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" wrote:
>
> > 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));
> >
From: Jerry Whittle on
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" wrote:

> 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" wrote:
>
> > 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" wrote:
> >
> > > 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));
> > >
From: John W. Vinson on
On Mon, 22 Mar 2010 09:27:01 -0700, MsWatts
<MsWatts(a)discussions.microsoft.com> wrote:

>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/community/en-us/default.mspx?dg=microsoft.public.access.tablesdbdesign&lang=en&cr=US

--

John W. Vinson [MVP]