I wish I could tell by what you have provided, how you want to join these
two queries, and what you want as the value in your crosstab query. I'll
give you a start, and if you need more information, post back with a better
explaination of how you intend to join these two tables, and what value you
want in the crosstab. To ensure you get a list of all the countries, and
all the dates in a given month, you will have to create a query that looks
something like the following. I assume that your [Dates] table contains all
dates. BTW, Date is a reserved word in Access, so you should probably try
to avoid using it as a field name.
Query3. This query will give you a list of all the countries, and all the
dates. I added a where clause in this query to limit the dates to the
previous month. You can modify this to get the list of dates you want.
SELECT DISTINCT [SO4 Master List].Country, [Dates].Date as [Ship-Date]
FROM [SO4 Master List], [Dates]
WHERE [Dates].[Date] BETWEEN DateSerial(YEAR(Date()), Month(Date()), 1)
AND DateAdd("m", -1,
DateSerial(YEAR(Date()), Month(Date()), 1))
If you want to avoid having to maintain a table of dates (this can be a real
pain), try the following.:
1. Create a table (tbl_Numbers) which contains a single field (intValue).
Add ten records (0, 1, 2, 3, 4, 5, 6, 7, 8, 9) to this table
2. Create a query (qry_Number). I usually create this query to include the
number from 0 - 999
SELECT Hundreds.intValue * 100 + Tens.intValue * 10 + Ones.intValue as
intNumber
FROM tbl_Numbers as Hundreds, tbl_Numbers as Tens, tbl_Numbers as Ones
Now to get a list of dates, all you have to do is use the DateAdd function,
in combination with this query. For example, to get a list of days in 2005,
your query would look like:
SELECT DateAdd("d", [intNumber], DateSerial(2005, 1, 1)) as [Ship-Date]
FROM qry_Numbers
WHERE Year(DateAdd("d", [intNumber], DateSerial(2005, 1, 1))) = 2005
HTH
Dale
gearhead said:
I'm no SQL expert, but I thought I'd include the SQL for those that are:
Here's the first query:
SELECT commshipcty.[Ship-Date], [SO4 Master List].[HP SubRegion], [SO4
Master List].Country, commshipcty.[Delay factor Group],
Sum(commshipcty.value) AS SumOfvalue
FROM [SO4 Master List] LEFT JOIN commshipcty ON [SO4 Master List].[SAP
Country Code] = commshipcty.[Country key]
GROUP BY commshipcty.[Ship-Date], [SO4 Master List].[HP SubRegion], [SO4
Master List].Country, commshipcty.[Delay factor Group]
ORDER BY commshipcty.[Ship-Date], [SO4 Master List].[HP SubRegion], [SO4
Master List].Country, commshipcty.[Delay factor Group];
Here's the second query:
SELECT Dates.Date, ShipJanwithRegion.[HP SubRegion],
ShipJanwithRegion.Country, ShipJanwithRegion.[Delay factor Group],
ShipJanwithRegion.SumOfvalue
FROM Dates LEFT JOIN ShipJanwithRegion ON Dates.Date =
ShipJanwithRegion.[Ship-Date]
ORDER BY Dates.Date, ShipJanwithRegion.[HP SubRegion],
ShipJanwithRegion.Country, ShipJanwithRegion.[Delay factor Group];
Thanks,
Ian
gearhead said:
I am trying to join two queries to create an output file that has a complete
list of dates for the month, and countries in a region. The data changes
daily, but I want the output (a crosstab table) to have the same size and
number of rows and columns every time.
Currently I am joining a master list of countries with the data query (many
to one join). Then I join that query with a master list of dates (again many
to one join).
At the end I have a query with entries for the full list of dates in the
month, but any blank countries no longer show up.
Any suggestiosn on how to get all dates and all countries to show in the
final query, even if there is no data for a date or country?
Thanks in advance,
Ian Lomax
