Results 1 to 8 of 8
  1. #1
    5 Star Lounger
    Join Date
    Nov 2001
    Location
    Toronto Canada
    Posts
    920
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Access 2003 (The Melding of the Three)

    Edited by HansV to display data in table format

    The Melding of the Three

    Files Used and their respective Fields:

    qry Inventory - 00
    Cheers,
    Andy

  2. #2
    Plutonium Lounger
    Join Date
    Mar 2002
    Posts
    84,353
    Thanks
    0
    Thanked 29 Times in 29 Posts

    Re: Access 2003 (The Melding of the Three)

    We're going to create a union query.

    Create a query in design view.
    Don't add any tables.
    Switch to SQL view.
    Enter the following:

    SELECT Inv_Rcvd_MstSysKey AS SysKey, "Received" AS TranType, Inv_Rcvd_Created AS Date, Inv_Rcvd_Qty AS Qty
    FROM [qry Inventory - 01 - Received]
    UNION
    SELECT Inv_Issued_MstSysKey, "Issued", Inv_Issued_Created, Inv_Issued_Qty
    FROM [qry Inventory - 02 - Issued]
    UNION
    SELECT Inv_Returned_MstSysKey, "Returned", Inv_Returned_Created, Inv_Returned_Qty
    FROM [qry Inventory - 03 - Returned]

    Save this query and use it as the record source for your report. You can specify the sorting and grouping in the ... Sorting and Grouping window of the report.

    Note: you cannot view a union query in design view, only in SQL view and in datasheet view.

  3. #3
    5 Star Lounger
    Join Date
    Nov 2001
    Location
    Toronto Canada
    Posts
    920
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: Access 2003 (The Melding of the Three)

    Thanks Hans,

    Have to leave the office for a couple of hours but will get back to you this afternoon. I apologize for the formatting particularly as you once told me how to do it correctly, but unfortunately, I couldn't find that specific post.

    Cheers,
    Cheers,
    Andy

  4. #4
    5 Star Lounger
    Join Date
    Nov 2001
    Location
    Toronto Canada
    Posts
    920
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: Access 2003 (The Melding of the Three)

    Hi Hans,

    OK I
    Cheers,
    Andy

  5. #5
    Plutonium Lounger
    Join Date
    Mar 2002
    Posts
    84,353
    Thanks
    0
    Thanked 29 Times in 29 Posts

    Re: Access 2003 (The Melding of the Three)

    Ah yes, the word Date is a reserved word. Put it between square brackets:

    ..., Inv_Rcvd_Created AS [Date], ...

    or use an alias that is not a reserved word such as CreateDate:

    ..., Inv_Rcvd_Created AS CreateDate, ...

  6. #6
    5 Star Lounger
    Join Date
    Nov 2001
    Location
    Toronto Canada
    Posts
    920
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: Access 2003 (The Melding of the Three)

    Hans,

    I changed DATE to KDATE and it worked quite nicely.

    Without sounding repetitious but only to save you the trouble of cleaning up my posts in the future,

    How did you tablize ( is there such a word ) my data and how can I force "tabs" in my notes. Like I mentioned earlier, you did explain this before, but it is lost somewhere in my past posts. Sorry for the trouble,

    Cheers,
    Cheers,
    Andy

  7. #7
    Plutonium Lounger
    Join Date
    Mar 2002
    Posts
    84,353
    Thanks
    0
    Thanked 29 Times in 29 Posts

    Re: Access 2003 (The Melding of the Three)

    If you want to preserve spacing and indentation in the text just as you type it, insert <!t>[pre]<!/t> before the text and <!t>[/pre]<!/t> after it (these tags are also available from the 1-Click TagPanel).

    If you want to convert a table in Word, Excel or Access to a format that can be pasted into a post, you need some VBA code. See the thread starting at <post:=164,109>post 164,109</post:>. I copied the text from your first post in this thread into Excel, cleaned it up slightly, then ran the Excel macro from the thread I referred to and pasted the result back into your post.

  8. #8
    5 Star Lounger
    Join Date
    Nov 2001
    Location
    Toronto Canada
    Posts
    920
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: Access 2003 (The Melding of the Three)

    Hans,

    Thanks kindly for the help. This post is being cut and pasted in to my documentation binder.

    Cheers,
    Cheers,
    Andy

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •