Solved

MS Access Query Build

Posted on 2014-09-19
17
134 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 48

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 48

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
Three Reasons Why Backup is Strategic

Backup is strategic to your business because your data is strategic to your business. Without backup, your business will fail. This white paper explains why it is vital for you to design and immediately execute a backup strategy to protect 100 percent of your data.

 

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

Migrating Your Company's PCs

To keep pace with competitors, businesses must keep employees productive, and that means providing them with the latest technology. This document provides the tips and tricks you need to help you migrate an outdated PC fleet to new desktops, laptops, and tablets.

Question has a verified solution.

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

Suggested Solutions

Regardless of which version on MS Access you are using, one of the harder data-entry forms to create is one where most data from previous entries needs to be appended to new records, especially when there are numerous fields and records involved.  W…
As tax season makes its return, so does the increase in cyber crime and tax refund phishing that comes with it
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.
In Microsoft Access, when working with VBA, learn some techniques for writing readable and easily maintained code.

808 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