Page 2 of 3 FirstFirst 123 LastLast
Results 16 to 30 of 33
  1. #16
    Tom Carp is offline Advanced Beginner
    Windows 10 Access 2016
    Join Date
    Sep 2020
    Posts
    35
    Hi Steve



    Because this is personal financial data, I want to create a new db with a subset of the tables using sample data to share. I'll get back to you. It may also be useful to see the work done in the Excel journals, where there's a pretty good amount of "normalization". I have one spreadsheet linked to every journal that contains data like Accounts, Categories, etc. Account and category data is selected via pull-downs, and other data is selected based on those entries.

    Access queries add another 6 or so fields, creating the final transactions records. Union queries are used to merge the records, either in total, or in some cases a subset depending on the analysis and reporting. The final queries are linked to via Excel, where the financial analysis and reporting is done.

  2. #17
    Tom Carp is offline Advanced Beginner
    Windows 10 Access 2016
    Join Date
    Sep 2020
    Posts
    35
    Built a simplified version of the system. It has the same error as the full system (i.e. asking for an Account field). Not sure how to attach the db. Attempted but it said the file was too large.

  3. #18
    Micron is offline Virtually Inert Person
    Windows 10 Access 2016
    Join Date
    Jun 2014
    Location
    Ontario, Canada
    Posts
    12,737
    See how to attach files in forum header if needed. Did you compact then zip db? Almost always have to.
    The more we hear silence, the more we begin to think about our value in this universe.
    Paraphrase of Professor Brian Cox.

  4. #19
    Tom Carp is offline Advanced Beginner
    Windows 10 Access 2016
    Join Date
    Sep 2020
    Posts
    35
    Will try again with a zipped db. Not sure if I inserted it. If not, let me know and I'll try again.

    The db is a simple sample of the real db. The main difference is the number of linked journals. In the sample, there are 6; in the real system there are 70, thus the reason for multiple Union queries.

    Interestingly, the same issue that started the thread is reproduced in the sample, namely, 3 Unions (current, inactive, archive) run successfully, but the Union that selects those 3 Unions fails (i.e. asks for Account field data). The other test is that the final query (that selects the final Union) runs successfully.
    Attached Files Attached Files

  5. #20
    June7's Avatar
    June7 is online now VIP
    Windows 10 Access 2010 32bit
    Join Date
    May 2011
    Location
    The Great Land
    Posts
    52,820
    accdb file was attached but since everything is linked Excel and that was not provided, really nothing can do.
    How to attach file: http://www.accessforums.net/showthread.php?t=70301 To provide db: copy, remove confidential data, run compact & repair, zip w/Windows Compression.

  6. #21
    Tom Carp is offline Advanced Beginner
    Windows 10 Access 2016
    Join Date
    Sep 2020
    Posts
    35
    So sorry; should have known. The attached is the folder with the linked Excel files. Note, that the files CPI, SP500, and BondBill are not used in this test. The real system uses those files for some financial reporting later in the flow.
    Attached Files Attached Files

  7. #22
    Tom Carp is offline Advanced Beginner
    Windows 10 Access 2016
    Join Date
    Sep 2020
    Posts
    35
    I did test swapping the tables in the archive union and the inactive union. The field request still comes from archive.

    Also tested changing the name of archive to zarchive in case Access was sorting the select names. The field request came from zarchive.

    The strangest test was removing archive completely from the q1Union All. When run, the message is still looking for an Account field entry for archive.

    FYI, this Access db was created from scratch, i.e. nothing copied from the real db.

  8. #23
    Join Date
    Jan 2017
    Location
    Swansea,South Wales,UK
    Posts
    4,861
    Can you just link them to C:\temp please. I seem to have to specify location for each file and not even know which one it is asking about?
    Please use # icon on toolbar when posting code snippets.
    Cross Posting: https://www.excelguru.ca/content.php?184
    Debugging Access: https://www.youtube.com/results?sear...bug+access+vba

  9. #24
    June7's Avatar
    June7 is online now VIP
    Windows 10 Access 2010 32bit
    Join Date
    May 2011
    Location
    The Great Land
    Posts
    52,820
    In my first test, I removed q1UnionArchive from the q1UnionAll and still get the popup.

    I built a new q1UnionAll and it works. Sometimes weirdness is corruption that just can't be explained.

    BTW, advise not to use space in object names, including queries.

    I couldn't get the Linked Table Manager to work so I used VBA procedure I already had to reset links. I delete the three linked tables that didn't have worksheets.
    Code:
    Public Sub NormalizeTableLinks()
    'Use the variables provided to do the manipulations desired
    
    Dim td As TableDef
    Dim db As DAO.Database
    Dim strOld As String
    Dim strNew As String
    
    'replace the following strings as needed
    strOld = "Users\junkc\Documents\!Tom\Finance\System\dbDatabase\"
    strNew = "Users\Owner\June\Forums\"
    Set db = CurrentDb
    For Each td In db.TableDefs
            td.Connect = Replace(td.Connect, strOld, strNew)
            td.RefreshLink
    Next td
    db.TableDefs.Refresh
    
    End Sub
    How to attach file: http://www.accessforums.net/showthread.php?t=70301 To provide db: copy, remove confidential data, run compact & repair, zip w/Windows Compression.

  10. #25
    Join Date
    Jan 2017
    Location
    Swansea,South Wales,UK
    Posts
    4,861
    Thanks June7, I'll copy that.


    Quote Originally Posted by June7 View Post
    In my first test, I removed q1UnionArchive from the q1UnionAll and still get the popup.

    I built a new q1UnionAll and it works. Sometimes weirdness is corruption that just can't be explained.

    BTW, advise not to use space in object names, including queries.

    I couldn't get the Linked Table Manager to work so I used VBA procedure I already had to reset links. I delete the three linked tables that didn't have worksheets.
    Code:
    Public Sub NormalizeTableLinks()
    'Use the variables provided to do the manipulations desired
    
    Dim td As TableDef
    Dim db As DAO.Database
    Dim strOld As String
    Dim strNew As String
    
    'replace the following strings as needed
    strOld = "Users\junkc\Documents\!Tom\Finance\System\dbDatabase\"
    strNew = "Users\Owner\June\Forums\"
    Set db = CurrentDb
    For Each td In db.TableDefs
            td.Connect = Replace(td.Connect, strOld, strNew)
            td.RefreshLink
    Next td
    db.TableDefs.Refresh
    
    End Sub
    Please use # icon on toolbar when posting code snippets.
    Cross Posting: https://www.excelguru.ca/content.php?184
    Debugging Access: https://www.youtube.com/results?sear...bug+access+vba

  11. #26
    Tom Carp is offline Advanced Beginner
    Windows 10 Access 2016
    Join Date
    Sep 2020
    Posts
    35
    My thanks too; and sorry for not being very skilled at sending sample data.

    Unfortunately, I'm not able to reproduce the solution with my limited Access skills. I deleted q1Union_All, replaced it with a copy of q1Union_Current, and edited the Select statements to the 3 q1Unions. q1Union_Current, q1Union_Inactive, and q1Union_Archive run fine, but q1Union_All gets the field popup.

    What's also interesting is that q1Query_All, that just selects q1Union_All, runs successfully.

    I'll rebuild the db from scratch, this time without the linked Excel tables. Don't think that's contributing to the issue.

  12. #27
    Tom Carp is offline Advanced Beginner
    Windows 10 Access 2016
    Join Date
    Sep 2020
    Posts
    35
    Attached is a new db w/o linked Excel files. There are 6 journal tables, a date range setting table, and 4 field lookup tables. (Took the blanks out of the query names.) q1Union_Current, _Inactive, and _Archive unions 2 journals each. q1Union_All unions those 3 union queries. Finally, q1Query_All builds the final records using all the fields in q1Union_All, along with selected fields from the lookup tables, selecting with the date range from the settings table.

    q1Union_Current, _Inactive, and _Archive run fine, as does q1Query_All. q1Union_All asks for the Account field in q1Union_Archive.

    You said you "built" a new q1Query_All. I started with a copy of sql since I couldn't remember how to create a query starting there vs. a relationship diagram.
    Attached Files Attached Files

  13. #28
    June7's Avatar
    June7 is online now VIP
    Windows 10 Access 2010 32bit
    Join Date
    May 2011
    Location
    The Great Land
    Posts
    52,820
    I opened query builder, switched to SQLView and built UNION statement. Actually, I copy/pasted the SQL statement from the original query into the new one and it worked. So somewhere in the design properties of the first q1Query_All was a reference to q1Union_Archive.Account or corruption.
    How to attach file: http://www.accessforums.net/showthread.php?t=70301 To provide db: copy, remove confidential data, run compact & repair, zip w/Windows Compression.

  14. #29
    ssanfu is offline Master of Nothing
    Windows 10 Access 2010 32bit
    Join Date
    Sep 2010
    Location
    Anchorage, Alaska, USA
    Posts
    9,664
    @Tom,

    I don't know if you are going to switch from Excel to Access, but be aware that in Access, these are reserved words and shouldn't be used for object names:
    Type
    Group
    Date
    Form
    Description
    Format
    Memo
    Class
    Start
    End

    For a more complete list of reserved words see Problem names and reserved words in Access


    Also, you:
    Should not use spaces in object names
    Should not use special characters or punctuation in object names (#Trans)
    Should not have field names that begin with a number. The Access Gnomes get persnickety at times; plus the fields with just a number are not very descriptive.......

  15. #30
    Tom Carp is offline Advanced Beginner
    Windows 10 Access 2016
    Join Date
    Sep 2020
    Posts
    35
    Thanks for reminding me how to get to SQL in query builder. I'll experiment to see if your method fixes the issue. Because the queries are few and small, I'll rebuild them all from scratch. In fact, I'll start with a new db filed. Will post back results either way.

Page 2 of 3 FirstFirst 123 LastLast
Please reply to this thread with any new information or opinions.

Similar Threads

  1. Replies: 4
    Last Post: 09-14-2017, 01:35 PM
  2. Replies: 4
    Last Post: 08-09-2017, 12:06 PM
  3. Replies: 17
    Last Post: 08-11-2016, 08:26 AM
  4. Replies: 5
    Last Post: 05-14-2014, 01:17 PM
  5. Union query- 9 Queries
    By JessieBee in forum Queries
    Replies: 3
    Last Post: 07-14-2012, 05:46 AM

Tags for this Thread

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