?
Solved

MS Access 2007 query

Posted on 2010-09-09
20
Medium Priority
?
199 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
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!

 

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 2000 total points
ID: 33638695
0

Featured Post

NFR key for Veeam Backup for Microsoft Office 365

Veeam is happy to provide a free NFR license (for 1 year, up to 10 users). This license allows for the non‑production use of Veeam Backup for Microsoft Office 365 in your home lab without any feature limitations.

Question has a verified solution.

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

It’s been over a month into 2017, and there is already a sophisticated Gmail phishing email making it rounds. New techniques and tactics, have given hackers a way to authentically impersonate your contacts.How it Works The attack works by targeti…
Did you know that more than 4 billion data records have been recorded as lost or stolen since 2013? It was a staggering number brought to our attention during last week’s ManageEngine webinar, where attendees received a comprehensive look at the ma…
Using Microsoft Access, learn some simple rules for how to construct tables in a relational database. Split up all multi-value fields into single values: Split up fields that belong to other things into separate tables: Make sure that all record…
Have you created a query with information for a calendar? ... and then, abra-cadabra, the calendar is done?! I am going to show you how to make that happen. Visualize your data!  ... really see it To use the code to create a calendar from a q…
Suggested Courses

770 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