Hi Douglas,
Thanks for your reply
that’s right, I missed that part
actually I replaced it ... I putted a values between InDate and OutDate in the table of dates.
I thought that will be enough ... sorry for misunderstood.
Thanks Allen and Douglas,
you are really great and your help is highly appreciated
best regards
Your Query1 is incorrect. It should be something like:
SELECT DatesTbl.Dates, Items.ItemName, Items.Amount
FROM DatesTbl, Items
WHERE DatesTbl.Dates Between Items.[InDate] And Items.[OutDate]
ORDER BY DatesTbl.Dates, Items.ItemName;
(I think you missed step 3 in Allen's instructions)
--
Doug Steele, Microsoft Access MVP
(no private e-mails, please)
thanks Douglas for reply
kindly find below the SQL's,
Query1:
SELECT DatesTbl.Dates, Items.ItemName, Items.Amount
FROM DatesTbl, Items
ORDER BY DatesTbl.Dates, Items.ItemName;
Crosstab Query:
TRANSFORM Sum(Query1.Amount) AS SumOfAmount
SELECT Query1.ItemName
FROM Query1
GROUP BY Query1.ItemName
ORDER BY Format([Dates],"dd/mm")
PIVOT Format([Dates],"dd/mm");
thanks and best regards
Fadi
What's the SQL of your query?
--
Doug Steele, Microsoft Access MVP
(no private e-mails, please)
Thanks Allen for your respond,
it works great but with the following error:
data in table is as I previously posted
ItemName InDate OutDate Amount
--------------------------------------------------------------
Item1 01/09 05/09 7
Item2 03/09 07/09 4
the result I get is:
Query1_Crosstab ItemName Total Of Amount 01/09 02/09 03/09 04/09 05/09 06/09 07/09 08/09 09/09 10/09 28/08 29/08 30/08 31/08
Item1 98 7 7 7 7 7 7 7 7 7 7 7 7 7 7
Item2 56 4 4 4 4 4 4 4 4 4 4 4 4 4 4
the result I must get is:
ItemName Total Of Amount 01/09 02/09 03/09 04/09 05/09 06/09 07/09 08/09 09/09 10/09 28/08 29/08 30/08 31/08
Item1 35 7 7 7 7 7 0 0 0 0 0 0 0 0 0
Item2 20 0 0 4 4 4 4 4 0 0 0 0 0 0 0
did I done something wrong??!!
Please advice
thanks and best regards
Fadi
Allen Browne said:
So you first need to create a record for each date between your InDate and
OutDate?
1. The dates have to come from somewhere, so you will need a table of dates.
Create a table with just one field of type Date/Time. Make it the primary
key. Save the table with a name such as tblDate.
2. Enter a record for each date in the range you need, or you can use the
function below to populate the table.
3. Create a query using your exising table and tblDate. There should be no
line joining the 2 tables in the upper pane of table design. Drag the date
field from tblDate into the grid. In the Criteria row under this field,
enter:
Between [InDate] And [OutDate]
This generates a record for every date.
Save the query. Close.
4. Now create your crosstab query, using the query you just created as an
input "table." The date field will be your Column Heading. The Item will be
your RowHeading. And the Amount will be the Value.
Here's the function to populdate the date table:
Function MakeDates(dtStart As Date, dtEnd As Date) As Long
Dim dt As Date
Dim rs As DAO.Recordset
Set rs = DBEngine(0)(0).OpenRecordset("tblDate")
With rs
For dt = dtStart To dtEnd
.AddNew
!TheDate = dt
.Update
Next
End With
rs.Close
Set rs = Nothing
End Function
--
Allen Browne - Microsoft MVP. Perth, Western Australia
Reply to group, rather than allenbrowne at mvps dot org.
Fadi said:
Hi All,
Kindly help me on this
i have a table like this one:
ItemName InDate OutDate Amount
--------------------------------------------------------------
Item1 01/09 05/09 7
Item2 03/09 07/09 4
i want a query to get result like the following:
ItemName 01/09 02/09 03/09 04/09 05/09 06/09 07/09 08/09
-------------------------------------------------------------------------------------
Item1 7 7 7 7 7 0 0 0
Item2 0 0 4 4 4 4 4 0
-------------------------------------------------------------------------------------
Total (If possible) 7 7 13 13 13 4 4 0
can any one help me on this?
Thanks in advance
Fadi
