Access without using a crosstab for the SQL script below

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

Answer 1

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

Answer 2

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.

Answer 3

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]));

Answer 4

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