Solved

MS Access 2007 query

Posted on 2010-09-09
20
192 Views
Last Modified: 2012-05-10
I have a query that I am trying to execute in Access that pulls all users who have completed courses BETWEEN #01-01-2009# AND #12/31/2010# The table was laid out rather odd and I cannot seem to get the date range to work because I am getting results for all years.
SELECT  TblParticipants.LicenseNumber
       ,TblParticipants.LastName
       ,TblParticipants.FirstName
        ,TblParticipants.Group
       ,TblModuleProgress.[1Title]
       ,TblModuleProgress.[1Completed]
       ,TblModuleProgress.[2Title]
       ,TblModuleProgress.[2Completed]
       ,TblModuleProgress.[3Title]
       ,TblModuleProgress.[3Completed]
       ,TblModuleProgress.[4Title]
       ,TblModuleProgress.[4Completed]
       ,TblModuleProgress.[5Title]
       ,TblModuleProgress.[5Completed]
       ,TblModuleProgress.[6Title]
       ,TblModuleProgress.[6Completed]
       ,TblModuleProgress.[7Title]
       ,TblModuleProgress.[7Completed]
       ,TblModuleProgress.[8Title]
       ,TblModuleProgress.[8Completed]
       ,TblModuleProgress.[9Title]
       ,TblModuleProgress.[9Completed]
       ,TblModuleProgress.[10Title]
       ,TblModuleProgress.[10Completed]
       ,TblModuleProgress.[11Title]
       ,TblModuleProgress.[11Completed]
       ,TblModuleProgress.[12Title]
       ,TblModuleProgress.[12Completed]
       ,TblModuleProgress.[13Title]
       ,TblModuleProgress.[13Completed]
       ,TblModuleProgress.[14Title]
       ,TblModuleProgress.[14Completed]
       ,TblModuleProgress.[15Title]
       ,TblModuleProgress.[15Completed]
       ,TblModuleProgress.[16Title]
       ,TblModuleProgress.[16Completed]
           
FROM    TblParticipants
        INNER JOIN TblModuleProgress ON TblParticipants.LicenseNumber = TblModuleProgress.LicenseNumber
WHERE TblModuleProgress.[1Completed] BETWEEN #01-01-2009# AND #12/31/2010#
OR TblModuleProgress.[2Completed] BETWEEN #01-01-2009# AND #12/31/2010#
OR TblModuleProgress.[3Completed] BETWEEN #01-01-2009# AND #12/31/2010#
OR TblModuleProgress.[4Completed] BETWEEN #01-01-2009# AND #12/31/2010#
OR TblModuleProgress.[5Completed] BETWEEN #01-01-2009# AND #12/31/2010#
OR TblModuleProgress.[6Completed] BETWEEN #01-01-2009# AND #12/31/2010#
OR TblModuleProgress.[7Completed] BETWEEN #01-01-2009# AND #12/31/2010#
OR TblModuleProgress.[8Completed] BETWEEN #01-01-2009# AND #12/31/2010#
OR TblModuleProgress.[9Completed] BETWEEN #01-01-2009# AND #12/31/2010#
OR TblModuleProgress.[10Completed] BETWEEN #01-01-2009# AND #12/31/2010#
OR TblModuleProgress.[11Completed] BETWEEN #01-01-2009# AND #12/31/2010#
OR TblModuleProgress.[12Completed] BETWEEN #01-01-2009# AND #12/31/2010#
OR TblModuleProgress.[13Completed] BETWEEN #01-01-2009# AND #12/31/2010#
OR TblModuleProgress.[14Completed] BETWEEN #01-01-2009# AND #12/31/2010#
OR TblModuleProgress.[15Completed] BETWEEN #01-01-2009# AND #12/31/2010#
OR TblModuleProgress.[16Completed] BETWEEN #01-01-2009# AND #12/31/2010#;

Open in new window

0
Comment
Question by:mburk1968
[X]
Welcome to Experts Exchange

Add your voice to the tech community where 5M+ people just like you are talking about what matters.

  • Help others & share knowledge
  • Earn cash & points
  • Learn & ask questions
  • 10
  • 9
20 Comments
 
LVL 120

Expert Comment

by:Rey Obrero (Capricorn1)
ID: 33637237
what is the data type of field TblModuleProgress.[1Completed] and the other like fields?

what is the format of field TblModuleProgress.[1Completed] and the other like fields?
0
 

Author Comment

by:mburk1968
ID: 33637277
TblModuleProgress.[1Completed] and TblModuleProgress.[2Completed] etc are Date/Time.

FORMAT is ShortDate
0
 
LVL 120

Expert Comment

by:Rey Obrero (Capricorn1)
ID: 33637296
are you using US date or another date format?
0
Online Training Solution

Drastically shorten your training time with WalkMe's advanced online training solution that Guides your trainees to action. Forget about retraining and skyrocket knowledge retention rates.

 

Author Comment

by:mburk1968
ID: 33637366
Dates are being stored like "11/9/2007"
Access.JPG
0
 
LVL 120

Expert Comment

by:Rey Obrero (Capricorn1)
ID: 33637397
<Dates are being stored like "11/9/2007"> is this mm/dd/yyyy or dd/mm/yyyy


post more values so we can see..
0
 

Author Comment

by:mburk1968
ID: 33637488
Example attached
Book3.xlsx
0
 

Author Comment

by:mburk1968
ID: 33637623
Made some changes but got the same result.
SELECT  TblParticipants.LicenseNumber
       ,TblParticipants.LastName
       ,TblParticipants.FirstName
       ,TblParticipants.Group
       ,TblModuleProgress.[1Title]
       ,TblModuleProgress.[1Completed]
       ,TblModuleProgress.[2Title]
       ,TblModuleProgress.[2Completed]
       ,TblModuleProgress.[3Title]
       ,TblModuleProgress.[3Completed]
       ,TblModuleProgress.[4Title]
       ,TblModuleProgress.[4Completed]
       ,TblModuleProgress.[5Title]
       ,TblModuleProgress.[5Completed]
       ,TblModuleProgress.[6Title]
       ,TblModuleProgress.[6Completed]
       ,TblModuleProgress.[7Title]
       ,TblModuleProgress.[7Completed]
       ,TblModuleProgress.[8Title]
       ,TblModuleProgress.[8Completed]
       ,TblModuleProgress.[9Title]
       ,TblModuleProgress.[9Completed]
       ,TblModuleProgress.[10Title]
       ,TblModuleProgress.[10Completed]
       ,TblModuleProgress.[11Title]
       ,TblModuleProgress.[11Completed]
       ,TblModuleProgress.[12Title]
       ,TblModuleProgress.[12Completed]
       ,TblModuleProgress.[13Title]
       ,TblModuleProgress.[13Completed]
       ,TblModuleProgress.[14Title]
       ,TblModuleProgress.[14Completed]
       ,TblModuleProgress.[15Title]
       ,TblModuleProgress.[15Completed]
       ,TblModuleProgress.[16Title]
       ,TblModuleProgress.[16Completed]
           
FROM    TblParticipants
        INNER JOIN TblModuleProgress ON TblParticipants.LicenseNumber = TblModuleProgress.LicenseNumber
WHERE TblModuleProgress.[1Completed] BETWEEN #1/1/2007# AND #12/31/2008#
OR TblModuleProgress.[2Completed] BETWEEN #1/1/2007# AND #12/31/2008#
OR TblModuleProgress.[3Completed] BETWEEN #1/1/2007# AND #12/31/2008#
OR TblModuleProgress.[4Completed] BETWEEN #1/1/2007# AND #12/31/2008#
OR TblModuleProgress.[5Completed] BETWEEN #1/1/2007# AND #12/31/2008#
OR TblModuleProgress.[6Completed] BETWEEN #1/1/2007# AND #12/31/2008#
OR TblModuleProgress.[7Completed] BETWEEN #1/1/2007# AND #12/31/2008#
OR TblModuleProgress.[8Completed] BETWEEN #1/1/2007# AND #12/31/2008#
OR TblModuleProgress.[9Completed] BETWEEN #1/1/2007# AND #12/31/2008#
OR TblModuleProgress.[10Completed] BETWEEN #1/1/2007# AND #12/31/2008#
OR TblModuleProgress.[11Completed] BETWEEN #1/1/2007# AND #12/31/2008#
OR TblModuleProgress.[12Completed] BETWEEN #1/1/2007# AND #12/31/2008#
OR TblModuleProgress.[13Completed] BETWEEN #1/1/2007# AND #12/31/2008#
OR TblModuleProgress.[14Completed] BETWEEN #1/1/2007# AND #12/31/2008#
OR TblModuleProgress.[15Completed] BETWEEN #1/1/2007# AND #12/31/2008#
OR TblModuleProgress.[16Completed] BETWEEN #1/1/2007# AND #12/31/2008#
ORDER BY  TblParticipants.LicenseNumber;

Open in new window

0
 
LVL 120

Expert Comment

by:Rey Obrero (Capricorn1)
ID: 33637912
create a query against table  TblModuleProgress (no join)  and see if you will get a good result

see this sample db, place the excel file in one table

run query1


DB-Q-26461802-QueryDate.mdb
0
 

Author Comment

by:mburk1968
ID: 33638004
I get the same result.
SELECT  TblModuleProgress.LicenseNumber
       ,TblModuleProgress.[1Title]
       ,TblModuleProgress.[1Completed]
       ,TblModuleProgress.[2Title]
       ,TblModuleProgress.[2Completed]
       ,TblModuleProgress.[3Title]
       ,TblModuleProgress.[3Completed]
       ,TblModuleProgress.[4Title]
       ,TblModuleProgress.[4Completed]
       ,TblModuleProgress.[5Title]
       ,TblModuleProgress.[5Completed]
       ,TblModuleProgress.[6Title]
       ,TblModuleProgress.[6Completed]
       ,TblModuleProgress.[7Title]
       ,TblModuleProgress.[7Completed]
       ,TblModuleProgress.[8Title]
       ,TblModuleProgress.[8Completed]
       ,TblModuleProgress.[9Title]
       ,TblModuleProgress.[9Completed]
       ,TblModuleProgress.[10Title]
       ,TblModuleProgress.[10Completed]
       ,TblModuleProgress.[11Title]
       ,TblModuleProgress.[11Completed]
       ,TblModuleProgress.[12Title]
       ,TblModuleProgress.[12Completed]
       ,TblModuleProgress.[13Title]
       ,TblModuleProgress.[13Completed]
       ,TblModuleProgress.[14Title]
       ,TblModuleProgress.[14Completed]
       ,TblModuleProgress.[15Title]
       ,TblModuleProgress.[15Completed]
       ,TblModuleProgress.[16Title]
       ,TblModuleProgress.[16Completed]
           
FROM    TblModuleProgress
        
WHERE TblModuleProgress.[1Completed] BETWEEN #1/1/2007# AND #12/31/2008#
OR TblModuleProgress.[2Completed] BETWEEN #1/1/2007# AND #12/31/2008#
OR TblModuleProgress.[3Completed] BETWEEN #1/1/2007# AND #12/31/2008#
OR TblModuleProgress.[4Completed] BETWEEN #1/1/2007# AND #12/31/2008#
OR TblModuleProgress.[5Completed] BETWEEN #1/1/2007# AND #12/31/2008#
OR TblModuleProgress.[6Completed] BETWEEN #1/1/2007# AND #12/31/2008#
OR TblModuleProgress.[7Completed] BETWEEN #1/1/2007# AND #12/31/2008#
OR TblModuleProgress.[8Completed] BETWEEN #1/1/2007# AND #12/31/2008#
OR TblModuleProgress.[9Completed] BETWEEN #1/1/2007# AND #12/31/2008#
OR TblModuleProgress.[10Completed] BETWEEN #1/1/2007# AND #12/31/2008#
OR TblModuleProgress.[11Completed] BETWEEN #1/1/2007# AND #12/31/2008#
OR TblModuleProgress.[12Completed] BETWEEN #1/1/2007# AND #12/31/2008#
OR TblModuleProgress.[13Completed] BETWEEN #1/1/2007# AND #12/31/2008#
OR TblModuleProgress.[14Completed] BETWEEN #1/1/2007# AND #12/31/2008#
OR TblModuleProgress.[15Completed] BETWEEN #1/1/2007# AND #12/31/2008#
OR TblModuleProgress.[16Completed] BETWEEN #1/1/2007# AND #12/31/2008#

ORDER BY  TblModuleProgress.LicenseNumber;

Open in new window

0
 
LVL 120

Expert Comment

by:Rey Obrero (Capricorn1)
ID: 33638050
did you check the sample db i uploaded?

create a .mdb and import the tables to it  and upload here.
0
 

Author Comment

by:mburk1968
ID: 33638100
Imported both tables.
DB-Q-26461802-QueryDate.mdb
0
 
LVL 120

Expert Comment

by:Rey Obrero (Capricorn1)
ID: 33638195
the query you last posted seem to work.

can you tell me why you think the query is not returning the correct records?
0
 

Author Comment

by:mburk1968
ID: 33638228
Because I am seeing dates outside of the range #1/1/2007# AND #12/31/2008#
0
 
LVL 120

Expert Comment

by:Rey Obrero (Capricorn1)
ID: 33638331
it is because of the OR statement in the query
0
 

Author Comment

by:mburk1968
ID: 33638338
Using the query below I am getting the following dates in TblModuleProgress.[1Completed]
1/4/2009, 3/15/2009, 12/3/2009, 3/11/2010
SELECT  TblParticipants.LicenseNumber
       ,TblParticipants.LastName
       ,TblParticipants.FirstName
       ,TblParticipants.Group
       ,TblModuleProgress.[1Title]
       ,TblModuleProgress.[1Completed]
       ,TblModuleProgress.[2Title]
       ,TblModuleProgress.[2Completed]
       ,TblModuleProgress.[3Title]
       ,TblModuleProgress.[3Completed]
       ,TblModuleProgress.[4Title]
       ,TblModuleProgress.[4Completed]
       ,TblModuleProgress.[5Title]
       ,TblModuleProgress.[5Completed]
       ,TblModuleProgress.[6Title]
       ,TblModuleProgress.[6Completed]
       ,TblModuleProgress.[7Title]
       ,TblModuleProgress.[7Completed]
       ,TblModuleProgress.[8Title]
       ,TblModuleProgress.[8Completed]
       ,TblModuleProgress.[9Title]
       ,TblModuleProgress.[9Completed]
       ,TblModuleProgress.[10Title]
       ,TblModuleProgress.[10Completed]
       ,TblModuleProgress.[11Title]
       ,TblModuleProgress.[11Completed]
       ,TblModuleProgress.[12Title]
       ,TblModuleProgress.[12Completed]
       ,TblModuleProgress.[13Title]
       ,TblModuleProgress.[13Completed]
       ,TblModuleProgress.[14Title]
       ,TblModuleProgress.[14Completed]
       ,TblModuleProgress.[15Title]
       ,TblModuleProgress.[15Completed]
       ,TblModuleProgress.[16Title]
       ,TblModuleProgress.[16Completed]
           
FROM    TblParticipants
        INNER JOIN TblModuleProgress ON TblParticipants.LicenseNumber = TblModuleProgress.LicenseNumber
WHERE TblModuleProgress.[1Completed] BETWEEN #1/1/2007# AND #12/31/2008#
OR TblModuleProgress.[2Completed] BETWEEN #1/1/2007# AND #12/31/2008#
OR TblModuleProgress.[3Completed] BETWEEN #1/1/2007# AND #12/31/2008#
OR TblModuleProgress.[4Completed] BETWEEN #1/1/2007# AND #12/31/2008#
OR TblModuleProgress.[5Completed] BETWEEN #1/1/2007# AND #12/31/2008#
OR TblModuleProgress.[6Completed] BETWEEN #1/1/2007# AND #12/31/2008#
OR TblModuleProgress.[7Completed] BETWEEN #1/1/2007# AND #12/31/2008#
OR TblModuleProgress.[8Completed] BETWEEN #1/1/2007# AND #12/31/2008#
OR TblModuleProgress.[9Completed] BETWEEN #1/1/2007# AND #12/31/2008#
OR TblModuleProgress.[10Completed] BETWEEN #1/1/2007# AND #12/31/2008#
OR TblModuleProgress.[11Completed] BETWEEN #1/1/2007# AND #12/31/2008#
OR TblModuleProgress.[12Completed] BETWEEN #1/1/2007# AND #12/31/2008#
OR TblModuleProgress.[13Completed] BETWEEN #1/1/2007# AND #12/31/2008#
OR TblModuleProgress.[14Completed] BETWEEN #1/1/2007# AND #12/31/2008#
OR TblModuleProgress.[15Completed] BETWEEN #1/1/2007# AND #12/31/2008#
OR TblModuleProgress.[16Completed] BETWEEN #1/1/2007# AND #12/31/2008#

ORDER BY  TblParticipants.LicenseNumber;

Open in new window

0
 

Author Comment

by:mburk1968
ID: 33638372
This is my criteria. create a query in the PA database that will pull only the users that completed courses between 1/1/07 and 12/31/08 (and which courses).

Are you saying that with the way the table is laid out that I cannot do this? Using AND does not work either.
0
 
LVL 120

Expert Comment

by:Rey Obrero (Capricorn1)
ID: 33638413
yes, because your table TblModuleProgress is not normalized.

you have to break this table into two tables ? perhaps
see this link to understand normalized table

Description of the database normalization basics
http://support.microsoft.com/?kbid=283878
0
 
LVL 5

Expert Comment

by:David Christal CISSP
ID: 33638473
This should give you the idea:

SELECT TblParticipants.LastName
, TblParticipants.FirstName, v.Number, v.Mailed, v.Completed, v.CertCent
FROM (SELECT TblModuleProgress.Number, TblModuleProgress.LicenseNumber, TblModuleProgress.[1Mailed] as Mailed, TblModuleProgress.[1Completed] as Completed , TblModuleProgress.[1CertSent] as CertCent FROM TblModuleProgress where  TblModuleProgress.[1Completed] between  #01-01-2009# AND #12/31/2010#

union
SELECT TblModuleProgress.Number, TblModuleProgress.LicenseNumber, TblModuleProgress.[2Mailed], TblModuleProgress.[2Completed], TblModuleProgress.[2CertSent] FROM TblModuleProgress where  TblModuleProgress.[2Completed] between  #01-01-2009# AND #12/31/2010#
union
SELECT TblModuleProgress.Number, TblModuleProgress.LicenseNumber, TblModuleProgress.[3Mailed], TblModuleProgress.[3Completed], TblModuleProgress.[3CertSent] FROM TblModuleProgress where  TblModuleProgress.[3Completed] between  #01-01-2009# AND #12/31/2010#



)  AS v INNER JOIN TblParticipants ON v.LicenseNumber = TblParticipants.LicenseNumber;
0
 
LVL 120

Expert Comment

by:Rey Obrero (Capricorn1)
ID: 33638515
the union query will work, but you have to always modify to include additional fields as the table is growing horizontally and vertically.
this table design is a big NO in database app.
0
 
LVL 120

Accepted Solution

by:
Rey Obrero (Capricorn1) earned 500 total points
ID: 33638695
0

Featured Post

Free Tool: Site Down Detector

Helpful to verify reports of your own downtime, or to double check a downed website you are trying to access.

One of a set of tools we are providing to everyone as a way of saying thank you for being a part of the community.

Question has a verified solution.

If you are experiencing a similar issue, please ask a related question

Phishing attempts can come in all forms, shapes and sizes. No matter how familiar you think you are with them, always remember to take extra precaution when opening an email with attachments or links.
AutoNumbers should increment automatically, without duplicates.  But sometimes something goes wrong, and the next AutoNumber value is a duplicate.  This article shows how to recover from this problem.
What’s inside an Access Desktop Database. Will look at the basic interface, Navigation Pane (Database Container), Tables, Queries, Forms, Report, Macro’s, and VBA code.
Access reports are powerful and flexible. Learn how to create a query and then a grouped report using the wizard. Modify the report design after the wizard is done to make it look better. There will be another video to explain how to put the final p…

717 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question