Results 1 to 15 of 15
  1. #1
    DigitalAdrenaline is offline Competent Performer
    Windows 10 Access 2016
    Join Date
    Dec 2012
    Posts
    100

    Perform a Union All Query For Two Columns in the Same Table

    Hi, am I able to 'Union All' two columns of data from the same table? I have OpenDate and CloseDate columns in the same table that I'd like to combine and then sort in order of date to produce a Transaction report. I know this is done in SQL but have only seen examples combining from two tables, not one. Thanks.

  2. #2
    June7's Avatar
    June7 is offline VIP
    Windows 10 Access 2010 32bit
    Join Date
    May 2011
    Location
    The Great Land
    Posts
    52,815
    Yes.
    SELECT OpenDate AS Dates FROM table
    UNION ALL SELECT CloseDate FROM table;

    However, I don't really see how this is helpful to produce transactions report.
    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.

  3. #3
    DigitalAdrenaline is offline Competent Performer
    Windows 10 Access 2016
    Join Date
    Dec 2012
    Posts
    100
    Hi June, I just realised something. I need to create two Query tables.

    Table 1: TradeID, OpenDate, Stock, Units.
    Table 2: TradeID, CloseDate, Stock, Units.

    Then I Union All the two Date columns in SQL. That should produce the transaction report, ordered by Date. I hope that's correct.

  4. #4
    isladogs's Avatar
    isladogs is offline MVP / VIP
    Windows 10 Access 2010 32bit
    Join Date
    Jan 2014
    Location
    Somerset, UK
    Posts
    5,954
    What exactly does combine the two dates mean? And why do it? It's unnecessary
    Just base your report on the table, sorting by OpenDate and then by ClosedDate

    You don't need to concatenate or create a union query and you certainly don't need two tables.
    Colin, Access MVP, Website, email
    The more I learn, the more I know I don't know. When I don't know, I keep quiet!
    If I don't know that I don't know, I don't know whether to answer

  5. #5
    DigitalAdrenaline is offline Competent Performer
    Windows 10 Access 2016
    Join Date
    Dec 2012
    Posts
    100
    Hi ridders,

    by default, mr records are ordered by OpenDate with the CloseDate spread months apart at different row points. With hundreds of transactions, I'm scrolling back and forth to check the sequence of transactions to amend/check transaction data. I'm doing this in Excel and it consumes time. Checking records by data entry order of date helps me identify anomolies quickly like doubled-up missed data.

  6. #6
    June7's Avatar
    June7 is offline VIP
    Windows 10 Access 2010 32bit
    Join Date
    May 2011
    Location
    The Great Land
    Posts
    52,815
    The open and close dates for a TradeID are not in same record? If your wish is to get them on the same line in a query, UNION won't be useful. But if UNION and sorting by date meets your requirement then certainly do it. Two intermediate queries are not needed as shown by my earlier example, just include whatever additional fields are needed. Can even create a field: "open" AS Category
    Last edited by June7; 08-18-2018 at 12:04 PM.
    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.

  7. #7
    isladogs's Avatar
    isladogs is offline MVP / VIP
    Windows 10 Access 2010 32bit
    Join Date
    Jan 2014
    Location
    Somerset, UK
    Posts
    5,954
    I assumed the two date were different fields for the same record.
    If so, as I said, just use the sorting and grouping feature of your report.

    If these are for some reason separate records, a suitable query needs to be done to get them on the same record then use the report as before

    To repeat what has already been written a union query will be no use here
    Colin, Access MVP, Website, email
    The more I learn, the more I know I don't know. When I don't know, I keep quiet!
    If I don't know that I don't know, I don't know whether to answer

  8. #8
    DigitalAdrenaline is offline Competent Performer
    Windows 10 Access 2016
    Join Date
    Dec 2012
    Posts
    100
    Sorry guys, yes, the OpenDate and CloseDate are both in the same record. In fact, I've just realised to set the date format to display date and time to create a secondary unique tradeID but am unsure how to format it to display 22-Sep-18 12:53:21pm. It's not available from the current properties dropdown menu.

    If you see the screenshot below from a third-party portfolio program, they show a clear transaction flow creating a great audit trail. I was simply wishing to recreate this similar report rather than manually filtering back and forth between two date columns to double check entries. This screenshot below is just much faster to eyeball your flow of transactions to double-check data. The point was just to save time. Filtering back and forth between two date columns can be a tad tedious when you're cross checking a dozen data points from a few months ago across differing dates.

    Click image for larger version. 

Name:	PS Transaction Audit Trail.jpg 
Views:	13 
Size:	34.9 KB 
ID:	35167

  9. #9
    isladogs's Avatar
    isladogs is offline MVP / VIP
    Windows 10 Access 2010 32bit
    Join Date
    Jan 2014
    Location
    Somerset, UK
    Posts
    5,954
    Quote Originally Posted by DigitalAdrenaline View Post
    Sorry guys, yes, the OpenDate and CloseDate are both in the same record. In fact, I've just realised to set the date format to display date and time to create a secondary unique tradeID but am unsure how to format it to display 22-Sep-18 12:53:21pm. It's not available from the current properties dropdown menu.
    Format([YourDateField],"dd-mmm-yy hh:nn:ss ampm") replacing with the name of your date field
    Colin, Access MVP, Website, email
    The more I learn, the more I know I don't know. When I don't know, I keep quiet!
    If I don't know that I don't know, I don't know whether to answer

  10. #10
    June7's Avatar
    June7 is offline VIP
    Windows 10 Access 2010 32bit
    Join Date
    May 2011
    Location
    The Great Land
    Posts
    52,815
    Okay, starting to understand what you mean by transactions report and how UNION is relevant. If you provide your actual table and field names could be more help.
    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.

  11. #11
    DigitalAdrenaline is offline Competent Performer
    Windows 10 Access 2016
    Join Date
    Dec 2012
    Posts
    100
    Hi June,

    here is a copy of the main table fields.

    Click image for larger version. 

Name:	TradeDataTable.jpg 
Views:	12 
Size:	171.6 KB 
ID:	35172

  12. #12
    June7's Avatar
    June7 is offline VIP
    Windows 10 Access 2010 32bit
    Join Date
    May 2011
    Location
    The Great Land
    Posts
    52,815
    SELECT TradeNumber, AssetTickerFK, TradeID, "Entry" AS Category, EntryDate AS TransDate, EntryPrice AS TransPrice FROM tblTradeData
    UNION SELECT TradeNumber, AssetTickerFK, TradeID, "Exit", ExitDate, ExitPrice FROM tblTradeData;

    Include any other fields of interest.
    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.

  13. #13
    DigitalAdrenaline is offline Competent Performer
    Windows 10 Access 2016
    Join Date
    Dec 2012
    Posts
    100
    Thanks June, will run that and see how I go. I Appreciate your assistance. Cheers.

  14. #14
    June7's Avatar
    June7 is offline VIP
    Windows 10 Access 2010 32bit
    Join Date
    May 2011
    Location
    The Great Land
    Posts
    52,815
    The UNION will sort by the first column. Or use an ORDER BY clause in the last SELECT. Or build another query with the UNION as source. Or use UNION as source for form or report and set OrderBy property.
    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.

  15. #15
    DigitalAdrenaline is offline Competent Performer
    Windows 10 Access 2016
    Join Date
    Dec 2012
    Posts
    100
    Hi June, many thanks. I came across a YT video that that showed a Query build putting context to the SQL syntax (I've not written SQL before). I've now produced exactly what I'm after but had to create two extra fields: AuditEntry and AuditExit. I'm now understanding your SQL above a little better. Here's the final SQL here:

    SELECT tblTradeData.TradeNumber AS ID, tblTradeData.EntryDate AS TradeDate, tblTradeData.EntryAudit AS Audit, tblAssetTicker.AssetTicker, tblTradeData.Quantity, tblTradeData.OpenPrice AS Price
    FROM tblAssetTicker LEFT JOIN tblTradeData ON tblAssetTicker.AssetTickerID = tblTradeData.AssetTickerFK
    UNION SELECT tblTradeData.TradeNumber AS ID, tblTradeData.ExitDate AS TradeDate, tblTradeData.ExitAudit AS Audit, tblAssetTicker.AssetTicker, tblTradeData.Quantity, tblTradeData.ExitPrice AS Price
    FROM tblAssetTicker LEFT JOIN tblTradeData ON tblAssetTicker.AssetTickerID = tblTradeData.AssetTickerFK;


    Click image for larger version. 

Name:	QueryRun.jpg 
Views:	6 
Size:	20.0 KB 
ID:	35176

    Many thanks for your patience with this.

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

Similar Threads

  1. Replies: 5
    Last Post: 10-12-2017, 12:12 PM
  2. Replies: 4
    Last Post: 08-09-2017, 12:06 PM
  3. Replies: 4
    Last Post: 12-20-2015, 02:35 PM
  4. Replies: 2
    Last Post: 10-26-2015, 06:14 AM
  5. Replies: 9
    Last Post: 07-06-2012, 10:43 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