Reports

[zyabc]

Hello All,

I have created a cross-tab query wherein the "date column" is the column heading & the "city column" as the row heading. It is working perfectly. But when i implement this query in the reports it is not able to display the data in a single page. It is getting split( sometimes a single data is being split) & shown in 2 different pages. Is there anyway to overcome this problem.

Pls help me out.

Thanks & Regards,
zyabc

 

[dcurtis]

Try decreasing your margine size.

 

[dhookom]

I personally don't feel the number of columns when using dates needs to be dynamic. Create a few reports that might create columns by month or quarter or day and set a specific number of columns in design view. If users want more dates, they can run more reports. You could also check the desired date range and then automate the running of the report multiple times.

I always create columns that are "relative" dates. I compare a date field to a date entered on a form finding the difference. The column headings would be created by an expression like:
ColHead:"Day" & DateDiff("d",[DateFld],Forms!frmA!txtEnd)

If you truly need a dynamic number of columns, there is a crosstab report sample at

http://www.invisibleinc.com/divFiles.cfm?divDivID=4.

Duane
MS Access MVP

 

[zyabc]

Hello Dhookom,

Thanks for the tip. I downloaded the file & it worked fine. I created a new database & imported the file into the database since i am not able the view the source. I have Office XP with me. When i try to execute the new file I get the error saying .Edit is not found. Which reference should be added. I added DAO 3.6. Still i get the error. If i run the downloaded file it works fine. Please help me out.





Thanks & Regards,
zyabc

 

[zyabc]

Dhookom i tried to import CrossTab.mdb file.

Thanks & Regards,
zyabc

 

[dhookom]

You may need to go to the code and find and replace
Dim db As Database
with
Dim db As DAO.Database
and
Dim rs as Recordset
with
Dim rs as DAO.Recordset
and
Dim qdf as QueryDef
with
Dim qdf as DAO.QueryDef
The defaults in your code are still probably creating ADO rather than DAO. This requires the code to be more explicit.


Duane
MS Access MVP

 

[zyabc]

Thanks Duane very much. I am able to import the file & execute it successfully. Hope i am able to implement this concept. Will certainly inform you if it comes out well.



Thanks & Regards,
zyabc

 

[zyabc]

Hello Duane,

I want my report to look somewhat like this with no Employee ID.

Customer 51 Customer 52
22/01/2000 12 15
23/01/2000 12 20

The 12,15,20 are the sum of SalesAmt.

Using the below query i am able to get the date & the sum of each customer

SELECT tblSales.SaleDate, tblSales.SaleAmt, tblSales.CustomerID
FROM tblCustomers INNER JOIN tblSales ON tblCustomers.CustomerID = tblSales.CustomerID
GROUP BY tblSales.SaleDate, tblSales.SaleAmt, tblSales.CustomerID;

In the report which you have kindly provided the sub-report consists of customer_name. This is got via from the dummy table. Since the reports will be viewed by multiple people at the same given time... I think it is impossible. However can i create a simple query for getting the customer name & create a sub-report. Is it the right way. The Customer Query is: SELECT tblCustomers.CustomerName
FROM tblCustomers;

How can i link the Sub-report with the main report in this case?

Pls advice on this. Pls do also give some ideas. I will be very much grateful.


Thanks & Regards,
zyabc

 

[dhookom]

The dynamic crosstab report link that I posted earlier should work. You should have a separate front end mdb for each user. Only the back end mdb should be shared on a server.

The Dates as column headings is much simpler than the Customers as column headings. Are you sure you want Customer headings?

Duane
MS Access MVP