Showing dates like in a Excel worksheet

  • Thread starter Thread starter Nicola M
  • Start date Start date
N

Nicola M

Hi all! Access 2003.
How can I show dates, or better, days in sequence (
1,2,3,...,30,31,1,2,...,30,1,2,... grouping par 3 months) so to get a report
like a tipical excel worksheet with days in cells Bn,Cn,Dn,... and data on
rows A1,A2,A3 etc. Any table in my DB contains all these days but only short
periods using the fields (date/time) From and To. Periods will be show like
an X when the column match the relevant row.
Sorry for my bad English. By the way I hope to have been clear.

Thank you all in advance for tips, advices and suggestions.

Have a wonderful and Happy new year!

Nicola M
 
OK! I get the "relative" Header of the columns. Now, but the example doesn't
explain how, I need to put in the right column my data. How can I do this?
 
First, you need a control (text box) on a form to set the reporting time
period. Then you create a crosstab query of your data that results in the
column heading being a relative date based on the control on the form. Then
you create the report based on the crosstab.

If you get lost, come back with how far you got, your table structure, your
form and control name, your SQL of the attempt at the crosstab.
 
Sorry! I'm working for test on an Italian Access 2003 and I didn't remember
the translation for crosstab that is (Query a campi incrociati). Now I found
it and statrs working on.
Thanks again...
Nicola M
 
Duane Hookom ha scritto:
Let me know if you have any trouble making this solution work for you.
Click to expand...

Yes I have. :-(
First, you need a control (text box) on a form to set the reporting time
period.
Click to expand...

Actually I can't understand this. Why should I need a Form? (Mask) I
already have the date to working on in a table (table T-People).
Then you create a crosstab query of your data that results in the
column heading being a relative date based on the control on the form.
Click to expand...

I create the crosstab query using dateofbirth but I get only the days in
which someone became older ;-) while I'd like to get the days without
birthdays too, in a manner similar to the below schema (the dash is only
a markplace, a 0 digit or a null field are also welcome, then i will
format them in the report)

title (this is not a problem): January 2009

Days: 3 4 5 6 7 8 9 10
Nicola - - - - 39 - - -
Duane - - 35 - - - - -
Smith - - - - - - 28 -
....
Then you create the report based on the crosstab.
Click to expand...

I create the report also but only with the 5,7 and 9 columns :-(
If you get lost, come back with how far you got, your table structure, your
form and control name, your SQL of the attempt at the crosstab.
Click to expand...

Here the code for the crosstab

TRANSFORM First([Copia di QSEL-Birthdays].Age) AS PrimoDiAge
SELECT [Copia di QSEL-Birthdays].Surname
FROM [Copia di QSEL-Birthdays]
GROUP BY [Copia di QSEL-Birthdays].Surname
PIVOT [Copia di QSEL-Birthdays].Day;

QSEL-Birthdays is a simple query uses T-People table to select and sort
the surnames of all personnel's birthdays included in a range of dates.
The range is from today (day(date() till 7 days later
(day(DateAdd("d",7,Date()).

Please do not consider me extremely stubborn in order to find a solution
for this simple issue only. This is a trainig: if I understand it very
well I'll use my new knowledge to manage a more complex Leave days
application.

Thank you again for the help and the patience showed.
Hope have quoted correctly.
 
Back
Top