Results 1 to 7 of 7
  1. #1
    Grizz14 is offline Novice
    Windows 7 64bit Access 2007
    Join Date
    Sep 2016
    Posts
    7

    Multiple relationships to 1 table without combining data

    Good afternoon all,



    I am relatively new to Access and could use some assistance. I have 2 tables of information one is Prior year data, and the other is Current Year data. There then is a 3rd tab that I have called grouping. On the Grouping table I have a listing of Account numbers and a desired category that I want account numbers grouped into. This account table is then linked the Prior year table as well as the Current year table by account number.

    What I am trying to accomplish, is to run a query that lists each grouping category once, and then sum all of the dollar amounts into each corresponding account group. In short, I'm building a consolidation based off of a set of grouping. Which I have build and it works when only looking at 1 data set.

    Here's my issue, I need Column A of my query to be the account groupings, (done), column B to be prior year data (done), and column C to be current year data. (issue). In the query design, when I bring in current year data, it alters my Prior year data. My thought is that each data table is linked through the account grouping table. and when trying to consolidate separately into 2 different columns they are getting merged bc the database thinks that they are related...

    I am not currently familiar with SQL which may make things more difficult in finding a solution.

    Any help would be appreciated.

  2. #2
    Micron is offline Virtually Inert Person
    Windows 7 32bit Access 2007
    Join Date
    Jun 2014
    Location
    Ontario, Canada
    Posts
    12,737
    In the query design, when I bring in current year data, it alters my Prior year data.
    I have no idea what this means.
    You add the current year field to the design and it does something to the prior year field while still in design view?
    Maybe post a pic of your query design...
    The more we hear silence, the more we begin to think about our value in this universe.
    Paraphrase of Professor Brian Cox.

  3. #3
    Grizz14 is offline Novice
    Windows 7 64bit Access 2007
    Join Date
    Sep 2016
    Posts
    7
    Click image for larger version. 

Name:	Query Design.png 
Views:	20 
Size:	28.2 KB 
ID:	25902

    Here's the image of my query design.

    The result that I am after is to have 1 line item of each category (DD_FinalizedGrouping) and then 2 columns of totals. The first having sumif results from table Current Year and the second sumif results from table Detail.

  4. #4
    Micron is offline Virtually Inert Person
    Windows 7 32bit Access 2007
    Join Date
    Jun 2014
    Location
    Ontario, Canada
    Posts
    12,737
    when I bring in current year data, it alters my Prior year data
    STILL don't know what this means.
    Maybe you could explain this as well, so we can get a better idea of what's going on...
    it works when only looking at 1 data set.
    Maybe you need a Crosstab query instead of a Totals query. Would be good to see either some data or a zipped copy of your db.

  5. #5
    Grizz14 is offline Novice
    Windows 7 64bit Access 2007
    Join Date
    Sep 2016
    Posts
    7
    Thanks for your attention, Micron. I have made some progress and figured out that I believe a Union Query is a better fit for what I am trying to accomplish here. Below is my SQL Code so far:

    SELECT [Account Grouping].[Grouping Category], Sum([FY15 TB with Orgs].[FY15 AMOUNT]) AS [SumOfFY15 AMOUNT]
    FROM [Account Grouping] LEFT JOIN [FY15 TB with Orgs] ON [Account Grouping].[Account Number] = [FY15 TB with Orgs].ACCOUNT
    GROUP BY [Account Grouping].[Grouping Category]

    UNION SELECT [Account Grouping].[Grouping Category], Sum([FY16 TB with Orgs].[FY16 AMOUNT]) AS [SumOfFY16 AMOUNT]
    FROM [Account Grouping] LEFT JOIN [FY16 TB with Orgs] ON [Account Grouping].[Account Number] = [FY16 TB with Orgs].ACCOUNT
    GROUP BY [Account Grouping].[Grouping Category];

    This code is currently doing what I need it to do, however could you provide me some assistance in breaking out the two data sources into two separate columns. What I mean by this is that in the first column i want my grouping category, in column 2 i want my FY15 results and in column 3, i would like the results for FY16.

    Currently, my results are being presented as follows, for example, in column 1 i have 2 "rent records" the first being fy15 results and the second being fy16 results.

  6. #6
    Micron is offline Virtually Inert Person
    Windows 7 32bit Access 2007
    Join Date
    Jun 2014
    Location
    Ontario, Canada
    Posts
    12,737
    If you're concerned about presenting the data that way in a query I think you'll have to separate the selects and join them in a third query by account number, putting each output into its own field. Methinks a form would be more appropriate as a means of displaying your information; not a query.
    The more we hear silence, the more we begin to think about our value in this universe.
    Paraphrase of Professor Brian Cox.

  7. #7
    Grizz14 is offline Novice
    Windows 7 64bit Access 2007
    Join Date
    Sep 2016
    Posts
    7
    In case anyone searches and finds this thread and has a similar desire; I was able to solve the issue in a 2 step method. I took my Union Query:

    SELECT [Account Grouping].[Grouping Category], Sum([FY15 TB with Orgs].[FY15 AMOUNT]) AS [SumOfFY15 AMOUNT], "FY15"
    FROM [Account Grouping] LEFT JOIN [FY15 TB with Orgs] ON [Account Grouping].[Account Number] = [FY15 TB with Orgs].ACCOUNT
    GROUP BY [Account Grouping].[Grouping Category]

    UNION SELECT [Account Grouping].[Grouping Category], Sum([FY16 TB with Orgs].[FY16 AMOUNT]) AS [SumOfFY16 AMOUNT], "FY16"
    FROM [Account Grouping] LEFT JOIN [FY16 TB with Orgs] ON [Account Grouping].[Account Number] = [FY16 TB with Orgs].ACCOUNT
    GROUP BY [Account Grouping].[Grouping Category];

    From here, I ran a Crosstab Query using the Union Query and was able to produce what I needed.
    This 2 step process may not be the most direct way of getting the job done. However, it is a solved issue for the time being, until someone else wants to recommend a more direct solution.

    -Grizz

Please reply to this thread with any new information or opinions.

Similar Threads

  1. Replies: 5
    Last Post: 03-06-2015, 02:04 PM
  2. Replies: 14
    Last Post: 07-22-2013, 12:39 PM
  3. Combining data from multiple listboxes
    By dshillington in forum Access
    Replies: 1
    Last Post: 10-04-2012, 11:53 AM
  4. How to create multiple table relationships
    By robi212 in forum Access
    Replies: 1
    Last Post: 03-30-2012, 07:59 AM
  5. Combining N:N with 1:N relationships
    By Autoclave in forum Database Design
    Replies: 5
    Last Post: 10-11-2011, 05:06 AM

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