cross tab query and excel sheet

[newnewprog]

Hi Everyone,
I created the query to get the data in Ms Access 2000. Those fields are date(Mon Dec1 …), field1,field2,field3
The output needs to be populated like below by weekly for month.
week one
Mon Nov1 Tue Nov2 Wed Nov3 thu Nov4 Fri Nov5
--------------------------------------------------------
field1,field2,field3|field1,field2,field3|field1,field2,field3,

Week II Mon Nov8 Tue Nov9 Wed Nov10 thu Nov11 Fri NoV12

How to populate the data from Ms Access query to Excel sheet like above format. Or how to create crosstab query and create the report like above format.

 

[dhookom]

I'm not sure why you are asking a crosstab query question in the Reports forum. There are FAQs in the queries forum that pertain to crosstabs. It looks like you want a multiple value crosstab. There as solution in the FAQs.

If you can't figure this out, post some actual table and field names, sample records, and desired output of the sample records.

Duane
Hook'D on Access
MS Access MVP

 

[SkipVought]

newnewprog,

For your information, many people consider exporting a REPORT to Excel an anoyance, insulting and stupid! I am one of them. If you must export a crosstab report, just make it a .pdf!

If however, you want to allow the recipient(s) the ability to actually DO SOMETHING with the data that you feel is important, also send them the TABLE of data that sources the crosstab, which might mean doing a join or something. The caveat might be the number or rows in the source data. Certainly with 2007, the number of available rows has greatly increased, so maybe including the SQL for reference might be acceptable.

That's just my HUMBLE opinion. ;-)


Skip,
[sub]
Just traded in my old subtlety...
for a NUANCE![/sub]

 

[newnewprog]

Hi dhookom,

The data is stored in the table, I created the query and runt the report it is printing Like below

Date Status, Type , Location,
Mon Nov1
completed Part1 CA
Open breaks IL

Tues Nov2
Uncompleted Parts-II IL
--------------------------------------------------
I need to print output format
week 1
Mon Nov1 Tue Nov2 Wed Thurs Friday
-----------------------------------------------------
Status, Type , Location |Status, Type , Location,
completed Part1 CA | Uncompleted Parts-II IL
Open breaks IL |
______________________________________________________
week 2
Mon Nov7 Tue Nov8 Wed Nov9


Until 5 weeks

Above format need to print in Excel or in MS access 2000
report.

Thanks for reply. Please let me know.

 

[dhookom]

To get an access report with that layout (I think since it's confusing with you don't format with TGML) you can create a main report record source of just the unique weeks.

Then create a 5 column subreport for the dates and other values. Set the Link Master/Child to the week values.

Duane
Hook'D on Access
MS Access MVP

 

[newnewprog]

I tried your suggestion but I am unsuccessful. I am doing something wrong. Could you please give me more details on how to create a main report record source of just the unique weeks.

Is it the print layout?:
Mon Nov1 Tues Nove2 Wed Nov3 Thu Nov4 Fri Nov5

How do I create a sub report on that layout?

 

[dhookom]

Can you provide your actual table and field names and sample records as asked earlier?

Duane
Hook'D on Access
MS Access MVP