Page 1 of 2 12 LastLast
Results 1 to 15 of 17
  1. #1
    kennethnaga is offline Novice
    Windows 10 Access 2016
    Join Date
    Oct 2019
    Posts
    24

    Question Query field which displays record for specific facility from previous year (See Table)

    Facility ID Reporting Year Balance Previous Balance
    101A 2019 5000 4000
    101B 2019 4500 3500
    101A 2018 4000 -
    101B 2018 3500 -



    Hi there,

    I'm very new to Access/VBA/SQL and need some help creating the previous balance field in the table above. Please note: I do not know SQL and have been using the expression field in design view of the query. If this solution requires SQL, I would need instructions on how to implement it.

    I'm trying to make a query which cross-checks values, in this case it's the balance field. In the previous balance field, I want to display the balance from the previous year for a particular Facility ID. In Excel, the SUMIFS function was used, but as far as I'm aware this does not exist in Access. Any help regarding this issue is greatly appreciated and if anything is unclear please let me know, thank you!

    Edit: The pattern of FacilityID and Reporting Year is not consistent (does not alternate back and forth between 101A and 101B as shown).

  2. #2
    Bob Fitz's Avatar
    Bob Fitz is offline Access Developer
    Windows 10 Access 2016
    Join Date
    May 2011
    Location
    Essex UK
    Posts
    3,530
    You could try using DLookup() function with criteria to select the same Facility ID and the Reporting year -1.
    If this helped, please click the star at the bottom left of this posting and add to my reputation . Many thanks.
    Bob Fitzpatrick

  3. #3
    kennethnaga is offline Novice
    Windows 10 Access 2016
    Join Date
    Oct 2019
    Posts
    24
    Hi there, thanks for your help. I tried the DLookup and I'm getting the same values in Previous Balance as Balance. Is there something wrong with my code?

    Previous Balance: DLookUp([Balance SCUs],"tbl_Sch2 Summary",[tbl_Sch1].[Index&Facility] And [tbl_Sch1].[Reporting Year]-1)

    I know I shouldn't have symbols/spaces in object names, I'm working on an existing dB that I did not create. Thank you!

  4. #4
    Bob Fitz's Avatar
    Bob Fitz is offline Access Developer
    Windows 10 Access 2016
    Join Date
    May 2011
    Location
    Essex UK
    Posts
    3,530
    can you post a copy of the db with just the tables/queries used for this
    If this helped, please click the star at the bottom left of this posting and add to my reputation . Many thanks.
    Bob Fitzpatrick

  5. #5
    Bob Fitz's Avatar
    Bob Fitz is offline Access Developer
    Windows 10 Access 2016
    Join Date
    May 2011
    Location
    Essex UK
    Posts
    3,530
    Would need to know your table name and the name of the required fields and their data types to help further with the expression needed.

    I have attached a simple db as an example though. HTH
    Attached Files Attached Files
    If this helped, please click the star at the bottom left of this posting and add to my reputation . Many thanks.
    Bob Fitzpatrick

  6. #6
    kennethnaga is offline Novice
    Windows 10 Access 2016
    Join Date
    Oct 2019
    Posts
    24
    I'm unable to upload my tables/queries. Sorry about that.

    In qry_SCUCalcs I have 3 tables (tbl_Sch1, tbl_Sch2, tbl_Sch2 Summary) joined and each are related through Index&Facility and Reporting Year, this query is only taking data from these tables and not other queries.

    This is my updated DLookUp...

    Previous Balance: DLookUp([Balance SCUs],"tbl_Sch2 Summary","[tbl_Sch2 Summary].[Reporting Year]=" & [tbl_Sch2 Summary].[Reporting Year]-1)

  7. #7
    Bob Fitz's Avatar
    Bob Fitz is offline Access Developer
    Windows 10 Access 2016
    Join Date
    May 2011
    Location
    Essex UK
    Posts
    3,530
    kennethnaga

    Did you see my post #5 with example
    If this helped, please click the star at the bottom left of this posting and add to my reputation . Many thanks.
    Bob Fitzpatrick

  8. #8
    kennethnaga is offline Novice
    Windows 10 Access 2016
    Join Date
    Oct 2019
    Posts
    24
    Yes, I have. Unfortunately I have a meeting at the moment, sorry I won't be able to reply for a couple hours.

  9. #9
    kennethnaga is offline Novice
    Windows 10 Access 2016
    Join Date
    Oct 2019
    Posts
    24
    Previous Balance: Nz(DLookUp("Balance SCUs","tbl_Sch2_Summary","tbl_Sch2_Summary.[Index&Facility] = '" & [tbl_Sch2_Summary].[Index&Facility] & "' And tbl_Sch2_Summary.[Reporting Year] = '" & ([tbl_Sch2_Summary].[Reporting Year]-1) & "'"),"Not Available")

    I used your expression and changed them to the corresponding field names, I am receiving #error for all records under this field. Thank you for all your help and being patient by the way!

    Edit: As you can see there are many tables with the same field name

  10. #10
    Bob Fitz's Avatar
    Bob Fitz is offline Access Developer
    Windows 10 Access 2016
    Join Date
    May 2011
    Location
    Essex UK
    Posts
    3,530
    What is the Data Type of your fields
    If this helped, please click the star at the bottom left of this posting and add to my reputation . Many thanks.
    Bob Fitzpatrick

  11. #11
    kennethnaga is offline Novice
    Windows 10 Access 2016
    Join Date
    Oct 2019
    Posts
    24
    Index&Facility = Short Text
    Reporting Year = Number
    Balance SCUs = Number

  12. #12
    Bob Fitz's Avatar
    Bob Fitz is offline Access Developer
    Windows 10 Access 2016
    Join Date
    May 2011
    Location
    Essex UK
    Posts
    3,530
    In the example I gave you, all fields were text fields and therefore needed single quotes as delimiters around the criteria. As your field [Reporting Year] as actually numeric the delimiters are not required.

    Try the following expression:

    Previous Balance: Nz(DLookUp("Balance SCUs","tbl_Sch2_Summary","tbl_Sch2_Summary.[Index&Facility] = '" & [tbl_Sch2_Summary].[Index&Facility] & "' And tbl_Sch2_Summary.[Reporting Year] = " & ([tbl_Sch2_Summary].[Reporting Year]-1)),"Not Available")
    If this helped, please click the star at the bottom left of this posting and add to my reputation . Many thanks.
    Bob Fitzpatrick

  13. #13
    kennethnaga is offline Novice
    Windows 10 Access 2016
    Join Date
    Oct 2019
    Posts
    24
    Unfortunately this expression is still returning #Error

  14. #14
    Bob Fitz's Avatar
    Bob Fitz is offline Access Developer
    Windows 10 Access 2016
    Join Date
    May 2011
    Location
    Essex UK
    Posts
    3,530
    Quote Originally Posted by kennethnaga View Post
    Unfortunately this expression is still returning #Error
    Could you cut/paste from your db the expression you are now trying
    If this helped, please click the star at the bottom left of this posting and add to my reputation . Many thanks.
    Bob Fitzpatrick

  15. #15
    kennethnaga is offline Novice
    Windows 10 Access 2016
    Join Date
    Oct 2019
    Posts
    24
    Previous Balances: Nz(IIf("tbl_Sch2_Summary.[Index&Facility]= '" & [tbl_Sch2_Summary].[Index&Facility] & "' And tbl_Sch2_Summary.[Reporting Year] = " & ([tbl_Sch2_Summary].[Reporting Year]-1),[Balance SCUs],"Not Available"))

Page 1 of 2 12 LastLast
Please reply to this thread with any new information or opinions.

Similar Threads

  1. save-previous-next facility
    By mesab66 in forum Programming
    Replies: 5
    Last Post: 06-29-2015, 01:52 AM
  2. Replies: 7
    Last Post: 05-16-2014, 02:25 PM
  3. Replies: 3
    Last Post: 02-11-2014, 11:42 AM
  4. Previous Year Data Query Expression
    By NotReese in forum Queries
    Replies: 4
    Last Post: 11-22-2013, 04:54 PM
  5. Query that displays previous row as well
    By mccarthy in forum Queries
    Replies: 5
    Last Post: 02-20-2011, 05:31 PM

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •  
Other Forums: Microsoft Office Forums