Solved

MS Access Query Build

Posted on 2014-09-19
17
135 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 49

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 49

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
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: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 50

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 50

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 50

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 50

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 50

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 50

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 50

Expert Comment

by:Gustav Brock
ID: 40334370
You are welcome!

/gustav
0

Featured Post

Back Up Your Microsoft Windows Server®

Back up all your Microsoft Windows Server – on-premises, in remote locations, in private and hybrid clouds. Your entire Windows Server will be backed up in one easy step with patented, block-level disk imaging. We achieve RTOs (recovery time objectives) as low as 15 seconds.

Question has a verified solution.

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

In a multiple monitor setup, if you don't want to use AutoCenter to position your popup forms, you have a problem: where will they appear?  Sometimes you may have an additional problem: where the devil did they go?  If you last had a popup form open…
You need to know the location of the Office templates folder, so that when you create new templates, they are saved to that location, and thus are available for selection when creating new documents.  The steps to find the Templates folder path are …
Show developers how to use a criteria form to limit the data that appears on an Access report. It is a common requirement that users can specify the criteria for a report at runtime. The easiest way to accomplish this is using a criteria form that a…
What’s inside an Access Desktop Database. Will look at the basic interface, Navigation Pane (Database Container), Tables, Queries, Forms, Report, Macro’s, and VBA code.

685 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