Still celebrating National IT Professionals Day with 3 months of free Premium Membership. Use Code ITDAY17

x
?
Solved

MS Access Query Build

Posted on 2014-09-19
17
Medium Priority
?
141 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
[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
  • 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 52

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 52

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
Concerto Cloud for Software Providers & ISVs

Can Concerto Cloud Services help you focus on evolving your application offerings, while delivering the best cloud experience to your customers? From DevOps to revenue models and customer support, the answer is yes!

Learn how Concerto can help you.

 

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 51

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 51

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 51

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 51

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 51

Accepted Solution

by:
Gustav Brock earned 2000 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 51

Assisted Solution

by:Gustav Brock
Gustav Brock earned 2000 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 51

Expert Comment

by:Gustav Brock
ID: 40334370
You are welcome!

/gustav
0

Featured Post

On Demand Webinar: Networking for the Cloud Era

Ready to improve network connectivity? Watch this webinar to learn how SD-WANs and a one-click instant connect tool can boost provisions, deployment, and management of your cloud connection.

Question has a verified solution.

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

As tax season makes its return, so does the increase in cyber crime and tax refund phishing that comes with it
In earlier versions of Windows (XP and before), you could drag a database to the taskbar, where it would appear as a taskbar icon to open that database.  This article shows how to recreate this functionality in Windows 7 through 10.
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…
Polish reports in Access so they look terrific. Take yourself to another level. Equations, Back Color, Alternate Back Color. Write easy VBA Code. Tighten space to use less pages. Launch report from a menu, considering criteria only when it is filled…

688 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