Solved

MS Access Query Build

Posted on 2014-09-19
17
133 Views
Last Modified: 2014-09-20
Hi all,

I have a query in MS Access. It holds two fields:
StartDate
NCyearActual

I want a third field that calculates a value based on the two fields above. I need it to do it based on this method:

If the NCyearActual is 'N1' and the StartDate is between 1st September to 31st December (in this calendar year) then show 'Nursery 2' - else, show the value held in the NCyearActual field.

I've tried a few things but I can't get it to work.

Can someone help me with this one please?

Your help would be much appreciated.

Thanks,

Tom
0
Comment
Question by:tom_optimum
  • 7
  • 7
  • 2
  • +1
17 Comments
 
LVL 8

Expert Comment

by:Ganapathi
ID: 40332386
Try this.
SELECT
   StartDate,
   NCyearActual,
   IIf([NCyearActual]='N1' And [StartDate] between '01/09/2014' And '12/31/2014', Nursery 2, NCyearActual) AS Column3
FROM
   mytable

Open in new window

0
 
LVL 47

Expert Comment

by:Vitor Montalvão
ID: 40332388
Just add a new column with something like this:
iif ( N1=1, iif ( StartDate BETWEEN #09-01-2014# AND #12-31-2014#, 'Nursery 2', NCyearActual), NCyearActual)

Open in new window

0
 
LVL 47

Expert Comment

by:Vitor Montalvão
ID: 40332402
For the current year you need to build the date on runtime:
iif ( N1=1, iif ( StartDate BETWEEN DateValue("09-01-" + Str(YEAR(Date()))) AND DateValue("12-31-" + Str(YEAR(Date()))), 'Nursery 2', NCyearActual), NCyearActual)

Open in new window

0
Ransomware-A Revenue Bonanza for Service Providers

Ransomware – malware that gets on your customers’ computers, encrypts their data, and extorts a hefty ransom for the decryption keys – is a surging new threat.  The purpose of this eBook is to educate the reader about ransomware attacks.

 

Author Comment

by:tom_optimum
ID: 40332415
Great - thanks. I'm just about to go into a meeting. I will try these when I am out.

I do need it to work with current calendar year because I don't want to have to update the code each year.
0
 
LVL 49

Expert Comment

by:Gustav Brock
ID: 40332697
Never use string handling for dates:

SELECT
   StartDate,
   NCyearActual,
       IIf(N1 = 1,
           IIf(StartDate BETWEEN DateSerial(Year(Date()), 9, 1) AND DateSerial(Year(Date()), 12, 31)),
           'Nursery 2', NCyearActual), NCyearActual) AS NC
FROM
    tblYourTable

/gustav
0
 

Author Comment

by:tom_optimum
ID: 40333564
Hi all,

None of these work.. I get some form of error for them all.

For:

iif ( N1=1, iif ( StartDate BETWEEN #09-01-2014# AND #12-31-2014#, 'Nursery 2', NCyearActual), NCyearActual)
                                          

Open in new window


I get this error:

01.PNG
For:

iif ( N1=1, iif ( StartDate BETWEEN DateValue("09-01-" + Str(YEAR(Date()))) AND DateValue("12-31-" + Str(YEAR(Date()))), 'Nursery 2', NCyearActual), NCyearActual)
                                          

Open in new window

I get this:

02.PNG
And for this one:

SELECT
   StartDate,
   NCyearActual,
       IIf(N1 = 1,
           IIf(StartDate BETWEEN DateSerial(Year(Date()), 9, 1) AND DateSerial(Year(Date()), 12, 31)),
           'Nursery 2', NCyearActual), NCyearActual) AS NC
FROM
    tblYourTable

Open in new window


I get this:

03.PNG
Also, I think the code saying things like "N1 = 1" is wrong... I dont have a field called N1 my field is called NCyearActual and one of the values is 'N1'.

Does this help?

Cheers

Tom
0
 
LVL 49

Expert Comment

by:Gustav Brock
ID: 40333593
This should do:

SELECT
   StartDate,
   NCyearActual,
       IIf(NCyearActual = 'N1',
           IIf(StartDate BETWEEN DateSerial(Year(Date()), 9, 1) AND DateSerial(Year(Date()), 12, 31),
           'Nursery 2', NCyearActual), NCyearActual) AS NC
FROM
    tblYourTable

/gustav
0
 

Author Comment

by:tom_optimum
ID: 40333631
Hi Gustav,

I am pasting this into the syntax box in the query view. So, I right click in the new coloumn > click Build > and paste into the syntax box.

I get this error:

01.PNG
I am using this code for the table name:

FROM
[BasicPupilData]

Any ideas?

Cheers

Tom
0
 
LVL 49

Expert Comment

by:Gustav Brock
ID: 40333649
Try this:

SELECT
    StartDate,
    NCyearActual,
        IIf(NCyearActual = 'N1' And (StartDate BETWEEN DateSerial(Year(Date()), 9, 1) AND DateSerial(Year(Date()), 12, 31)), 'Nursery 2', NCyearActual) AS NC
 FROM
     BasicPupilData

/gustav
0
 

Author Comment

by:tom_optimum
ID: 40333663
Hi,

Im still getting this error:

01.PNG
Tom
0
 
LVL 49

Expert Comment

by:Gustav Brock
ID: 40333678
But there is no subquery. Could you paste your full SQL please?

/gustav
0
 

Author Comment

by:tom_optimum
ID: 40333741
Hi,

Here it is:

SELECT CTFImport.UPN, CTFImport.Surname, CTFImport.Forename, CTFImport.DOB, Get_FSM_Calc.FSM AS FSM, YearGroupLookUp.OTrackYearGroup AS NCyearActual, CTFImport.Gender, CTFImport.Ethnicity, EALLookUp.OTrack_EAL, Max(CTFImport.StartDate) AS MaxOfStartDate, Get_InCare_Calc.InCareStatus
FROM (((CTFImport LEFT JOIN EALLookUp ON CTFImport.EnglishAsAdditionalLanguage = EALLookUp.CTF_EAL) LEFT JOIN Get_FSM_Calc ON CTFImport.UPN = Get_FSM_Calc.UPN) LEFT JOIN Get_InCare_Calc ON CTFImport.UPN = Get_InCare_Calc.UPN) LEFT JOIN YearGroupLookUp ON CTFImport.NCyearActual = YearGroupLookUp.CTFYearGroup
GROUP BY CTFImport.UPN, CTFImport.Surname, CTFImport.Forename, CTFImport.DOB, Get_FSM_Calc.FSM, YearGroupLookUp.OTrackYearGroup, CTFImport.Gender, CTFImport.Ethnicity, EALLookUp.OTrack_EAL, Get_InCare_Calc.InCareStatus, CTFImport.EnglishAsAdditionalLanguage
ORDER BY YearGroupLookUp.OTrackYearGroup;

Open in new window

0
 
LVL 49

Accepted Solution

by:
Gustav Brock earned 500 total points
ID: 40334103
Well, that was quite different. Still, try this:

SELECT
    CTFImport.UPN,
    CTFImport.Surname,
    CTFImport.Forename,
    CTFImport.DOB,
    Get_FSM_Calc.FSM AS FSM,
    YearGroupLookUp.OTrackYearGroup AS NCyearActual,
    CTFImport.Gender,
    CTFImport.Ethnicity,
    EALLookUp.OTrack_EAL,
    Max(CTFImport.StartDate) AS MaxOfStartDate,
    Get_InCare_Calc.InCareStatus,
    IIf(YearGroupLookUp.OTrackYearGroup = 'N1'
        And (StartDate BETWEEN DateSerial(Year(Date()), 9, 1) AND DateSerial(Year(Date()), 12, 31)),
        'Nursery 2', YearGroupLookUp.OTrackYearGroup) AS NC
    FROM
       (((CTFImport
       LEFT JOIN EALLookUp
           ON CTFImport.EnglishAsAdditionalLanguage = EALLookUp.CTF_EAL)
           LEFT JOIN Get_FSM_Calc
           ON CTFImport.UPN = Get_FSM_Calc.UPN)
               LEFT JOIN Get_InCare_Calc
               ON CTFImport.UPN = Get_InCare_Calc.UPN)
                   LEFT JOIN YearGroupLookUp
                   ON CTFImport.NCyearActual = YearGroupLookUp.CTFYearGroup
GROUP BY
    CTFImport.UPN,
    CTFImport.Surname,
    CTFImport.Forename,
    CTFImport.DOB,
    Get_FSM_Calc.FSM,
    YearGroupLookUp.OTrackYearGroup,
    IIf(YearGroupLookUp.OTrackYearGroup = 'N1'
        And (StartDate BETWEEN DateSerial(Year(Date()), 9, 1) AND DateSerial(Year(Date()), 12, 31)),
        'Nursery 2', YearGroupLookUp.OTrackYearGroup)
   CTFImport.Gender,
    CTFImport.Ethnicity,
    EALLookUp.OTrack_EAL,
    Get_InCare_Calc.InCareStatus,
    CTFImport.EnglishAsAdditionalLanguage
ORDER BY
    YearGroupLookUp.OTrackYearGroup;

/gustav
0
 

Author Comment

by:tom_optimum
ID: 40334357
Hi Gustav,

Thanks for your help.

I am now getting this error:

Capture.PNG
0
 
LVL 49

Assisted Solution

by:Gustav Brock
Gustav Brock earned 500 total points
ID: 40334365
I forgot a comma after the IIf sentence.

/gustav
0
 

Author Closing Comment

by:tom_optimum
ID: 40334368
Thanks foyour help on this - very impressed.

Thanks

Tom
0
 
LVL 49

Expert Comment

by:Gustav Brock
ID: 40334370
You are welcome!

/gustav
0

Featured Post

Courses: Start Training Online With Pros, Today

Brush up on the basics or master the advanced techniques required to earn essential industry certifications, with Courses. Enroll in a course and start learning today. Training topics range from Android App Dev to the Xen Virtualization Platform.

Question has a verified solution.

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

Suggested Solutions

Introduction When developing Access applications, often we need to know whether an object exists.  This article presents a quick and reliable routine to determine if an object exists without that object being opened. If you wanted to inspect/ite…
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…
In Microsoft Access, learn different ways of passing a string value within a string argument. Also learn what a “Type Mis-match” error is about.
With Microsoft Access, learn how to start a database in different ways and produce different start-up actions allowing you to use a single database to perform multiple tasks. Specify a start-up form through options: Specify an Autoexec macro: Us…

776 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