none
Access without using a crosstab for the SQL script below RRS feed

  • Question

  • How do I take the script below and make the horizontal. For instance I want 

    Source Table to have the information presented like without using a crosstab wizard is this possible

    Dates-----Your Number ---Cash4table---- Ga5FiveTable----- MoonphaseTable(going across like this)

    PARAMETERS [Enter Date:] DateTime, [Enter YourNumber:] IEEEDouble;

    SELECT "YourTable" AS SourceTable, YourDate, YourNumber
    FROM YourTable
    WHERE YourDate BETWEEN [Enter Date:]-1 AND [Enter Date:]+1
    UNION ALL
    SELECT "Cash4Table", YourDate, YourNumber
    FROM Cash4Table
    WHERE YourDate BETWEEN [Enter Date:]-1 AND [Enter Date:]+1
    UNION ALL
    SELECT "GAFiveTable", YourDate, YourNumber
    FROM GAFiveTable
    WHERE YourDate BETWEEN [Enter Date:]-1 AND [Enter Date:]+1
    UNION ALL SELECT "MoonPhaseTable", YourDate, YourNumber
    FROM MoonPhaseTable
    WHERE YourDate BETWEEN [Enter Date:]-4 AND [Enter Date:]+1
    ORDER BY SourceTable, YourDate;

    Thank you

    Friday, May 20, 2016 4:13 PM

Answers

  • You need to JOIN the tables, not use a UNION ALL operation.  If you can guarantee that every date in the range is represented in each table then it's merely a matter of a series of INNER JOINs on the YourDate column.  You can then restrict the query to the date range on any one of the tables.

    If  you cannot guarantee that every date in the range is represented in each table, first create an auxiliary calendar table of all dates over a suitable period of time, and LEFT JOIN this to each of the tables on the date columns.

    In either case you can then return the YourNumber columns from each table as a separate column in the query's result table, giving each column an appropriate alias.  Restrict the date column from the calendar table to the range.

    For means of creating calendar tables see Calendar.zip in my public databases folder at:

    https://onedrive.live.com/?cid=44CC60D7FEA42912&id=44CC60D7FEA42912!169

    Note that if you are using an earlier version of Access you might find that the colour of some form objects such as buttons shows incorrectly and you will need to amend the form design accordingly.  

    If you have difficulty opening the link copy its text (NB, not the link location) and paste it into your browser's address bar.

    Ken Sheridan, Stafford, England


    • Edited by Ken Sheridan Friday, May 20, 2016 4:43 PM Typo corrected.
    • Marked as answer by David_JunFeng Tuesday, June 7, 2016 8:01 AM
    Friday, May 20, 2016 4:43 PM
  • You can't enter more than one parameter via a single system-generated prompt; you'd either need to reference two controls in an unbound dialogue form, which is the best way, or have separate system-generated prompts, which is a very crude approach.

    The expression in the WHERE clause for a range from two days before to two days after the date entered as the parameter is as you did previously:

        YourDate BETWEEN [Enter Date:]-2 AND [Enter Date:]+2

    Are you really using column names like YourDate and YourNumber?  I only used these as generic names in my original replies, expecting you to substitute your actual column names, which should reflect the nature of the attribute which the column represents.

    Ken Sheridan, Stafford, England

    Friday, May 27, 2016 5:08 PM

All replies

  • You need to JOIN the tables, not use a UNION ALL operation.  If you can guarantee that every date in the range is represented in each table then it's merely a matter of a series of INNER JOINs on the YourDate column.  You can then restrict the query to the date range on any one of the tables.

    If  you cannot guarantee that every date in the range is represented in each table, first create an auxiliary calendar table of all dates over a suitable period of time, and LEFT JOIN this to each of the tables on the date columns.

    In either case you can then return the YourNumber columns from each table as a separate column in the query's result table, giving each column an appropriate alias.  Restrict the date column from the calendar table to the range.

    For means of creating calendar tables see Calendar.zip in my public databases folder at:

    https://onedrive.live.com/?cid=44CC60D7FEA42912&id=44CC60D7FEA42912!169

    Note that if you are using an earlier version of Access you might find that the colour of some form objects such as buttons shows incorrectly and you will need to amend the form design accordingly.  

    If you have difficulty opening the link copy its text (NB, not the link location) and paste it into your browser's address bar.

    Ken Sheridan, Stafford, England


    • Edited by Ken Sheridan Friday, May 20, 2016 4:43 PM Typo corrected.
    • Marked as answer by David_JunFeng Tuesday, June 7, 2016 8:01 AM
    Friday, May 20, 2016 4:43 PM
  • Hi TheSpecialistErika,

    Since less information, we are not clear about your issue, could you provide more information about it, for example table structure, screenshot etc., that will help us reproduce and resolve it.

    Thanks for your understanding.
    Tuesday, May 24, 2016 7:00 AM
  • Hi ken,

    If I want to add anther paramenter to the box, how would I do this ?

    I just want to enter a date For instance 5/1/2016 and I want to show all the numbers that came on this date and 2 days before and 2 days after. This is what I have below. Thank you

    SELECT [All Table Summary].MoonPhaseYourNumber, [All Table Summary].YourDate, [All Table Summary].Cash4YourNumber, [All Table Summary].GAFiveYourNumber, [All Table Summary].YourTableYourNumber
    FROM [All Table Summary]
    WHERE ((([All Table Summary].MoonPhaseYourNumber)=[Enter Moon PhaseNumber]));


    Friday, May 27, 2016 4:42 PM
  • You can't enter more than one parameter via a single system-generated prompt; you'd either need to reference two controls in an unbound dialogue form, which is the best way, or have separate system-generated prompts, which is a very crude approach.

    The expression in the WHERE clause for a range from two days before to two days after the date entered as the parameter is as you did previously:

        YourDate BETWEEN [Enter Date:]-2 AND [Enter Date:]+2

    Are you really using column names like YourDate and YourNumber?  I only used these as generic names in my original replies, expecting you to substitute your actual column names, which should reflect the nature of the attribute which the column represents.

    Ken Sheridan, Stafford, England

    Friday, May 27, 2016 5:08 PM