?
Solved

MS Access Query Build

Posted on 2014-09-19
17
Medium Priority
?
147 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 53

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 53

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
Never miss a deadline with monday.com

The revolutionary project management tool is here!   Plan visually with a single glance and make sure your projects get done.

 

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 53

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 53

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 53

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 53

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 53

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 53

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 53

Expert Comment

by:Gustav Brock
ID: 40334370
You are welcome!

/gustav
0

Featured Post

Never miss a deadline with monday.com

The revolutionary project management tool is here!   Plan visually with a single glance and make sure your projects get done.

Question has a verified solution.

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

Microsoft Access has a limit of 255 columns in a single table; SQL Server allows tables with over 255 columns, but reading that data is not necessarily simple.  The final solution for this task involved creating a custom text parser and then reading…
When we develop an application in Ms Access 2016 we should also try to protect the queries, macros and table links. I know I may not have a permanent solution but for novice users, they will not manage to break your application. Below is the detail …
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…
Get the source code for a fully functional Access application shell with several popular security features that Access VBA application developers desire, but find difficult or impossible to figure out how to code. You get the source code for managi…
Suggested Courses
Course of the Month3 days, 7 hours left to enroll

598 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