Query Help
in [Queries]
|
From: MsWatts on 22 Mar 2010 11:47 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 22 Mar 2010 12:11 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 22 Mar 2010 12:27 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 22 Mar 2010 13:11 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 22 Mar 2010 13:16
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] |