• Status: Solved
  • Priority: Medium
  • Security: Public
  • Views: 207
  • Last Modified:

MS Access 2007 query

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
mburk1968
Asked:
mburk1968
  • 10
  • 9
1 Solution
 
Rey Obrero (Capricorn1)Commented:
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
 
mburk1968Author Commented:
TblModuleProgress.[1Completed] and TblModuleProgress.[2Completed] etc are Date/Time.

FORMAT is ShortDate
0
 
Rey Obrero (Capricorn1)Commented:
are you using US date or another date format?
0
Independent Software Vendors: We Want Your Opinion

We value your feedback.

Take our survey and automatically be enter to win anyone of the following:
Yeti Cooler, Amazon eGift Card, and Movie eGift Card!

 
mburk1968Author Commented:
Dates are being stored like "11/9/2007"
Access.JPG
0
 
Rey Obrero (Capricorn1)Commented:
<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
 
mburk1968Author Commented:
Example attached
Book3.xlsx
0
 
mburk1968Author Commented:
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
 
Rey Obrero (Capricorn1)Commented:
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
 
mburk1968Author Commented:
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
 
Rey Obrero (Capricorn1)Commented:
did you check the sample db i uploaded?

create a .mdb and import the tables to it  and upload here.
0
 
mburk1968Author Commented:
Imported both tables.
DB-Q-26461802-QueryDate.mdb
0
 
Rey Obrero (Capricorn1)Commented:
the query you last posted seem to work.

can you tell me why you think the query is not returning the correct records?
0
 
mburk1968Author Commented:
Because I am seeing dates outside of the range #1/1/2007# AND #12/31/2008#
0
 
Rey Obrero (Capricorn1)Commented:
it is because of the OR statement in the query
0
 
mburk1968Author Commented:
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
 
mburk1968Author Commented:
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
 
Rey Obrero (Capricorn1)Commented:
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
 
David Christal CISSPCommented:
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
 
Rey Obrero (Capricorn1)Commented:
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
 
Rey Obrero (Capricorn1)Commented:
0

Featured Post

Industry Leaders: We Want Your Opinion!

We value your feedback.

Take our survey and automatically be enter to win anyone of the following:
Yeti Cooler, Amazon eGift Card, and Movie eGift Card!

  • 10
  • 9
Tackle projects and never again get stuck behind a technical roadblock.
Join Now