Home | Contact Us | FAQ | Search & Site Map | Link to Us
Sign In | Join | Other 45 Sites in Network
Home
Discussion GroupsFormsForms ProgrammingQueriesModules / DAO / VBAReports / PrintingMacrosDatabase DesignSecurityConversionImporting / LinkingSQL Server / ADPMultiuser / NetworkingReplicationSetup / ConfigurationDeveloper ToolkitsActiveX ControlsNew UsersGeneral 1General 2
Access DirectoryToolsTutorialsUser Groups
Related Topics
SQL ServerOther DB ProductsMS OfficeMore Topics ...

MS Access Forum / Forms Programming / March 2007

Tip: Looking for answers? Try searching our database.

Crosstab Query

Thread view: 
Enable EMail Alerts  Start New Thread
Thread rating: 
Pedro - 23 Mar 2007 21:36 GMT
Hello everyone
I'd like to make a report like this one :

               Jan    Feb   Mar    Apr    ... and so on
Qt             15      25     30      28
Sale      3000   4500  6000  5800
Quota    2500   3500  5800  6500

This is about making several head columns instead of always one, and only one.
Any ideas please?
Tks in advance
Pedro
strive4peace - 25 Mar 2007 23:21 GMT
Hi Pedro,

make a Crosstab query for each of your lines.  In your report, use
subreports based on each crosstab

Warm Regards,
Crystal
 *
     (:  have an awesome day  :)
  *
MVP Access
Remote Programming and Training
strive4peace2006 at yahoo.com
  *

> Hello everyone
> I'd like to make a report like this one :
[quoted text clipped - 8 lines]
> Tks in advance
> Pedro
Pedro - 28 Mar 2007 01:20 GMT
Hey Crystal
Tks a lot for your answer. I'm not very good on access, codes or
programming, that's why i think your idea is great. I'll try it cose i do it
with forms. But. Can i do, for example, the two crosstab qry i need, and
then, another crosstab qry to select  all fields from the 2 crosstab query
and do a report with it? Any future complications? Tks in advance.
Pedro

> Hi Pedro,
>
[quoted text clipped - 23 lines]
> > Tks in advance
> > Pedro
strive4peace - 28 Mar 2007 14:11 GMT
Hi Pedro,

you, you should be able to do that as long as Access doesn't complain
about things being too complicated.

I will caution you ... if you use a crosstab in the source for a
subreport (or subform), do not use LinkMasterFields and
LinklChildFields; set the filter yourself...

Warm Regards,
Crystal
 *
     (:  have an awesome day  :)
  *
MVP Access
Remote Programming and Training
strive4peace2006 at yahoo.com
  *

> Hey Crystal
> Tks a lot for your answer. I'm not very good on access, codes or
[quoted text clipped - 31 lines]
>>> Tks in advance
>>> Pedro
KARL DEWEY - 28 Mar 2007 03:07 GMT
One way is to use three crosstab queries.  
Then in a union query combine like this ---
SELECT X, Jan,    Feb,   Mar,    Apr,  ... Dec, Null AS X1, Null AS Jan1,
Null AS  Feb1, Null AS  Mar1,  Null AS Apr1, ...   Null AS Dec2  
FROM Crosstab1
UNION ALL SELECT Null AS X, Null, Null, Null,  Null, ...    X AS X1, Jan AS
Jan1,  Feb AS  Feb1, Mar AS  Mar1,  Apr AS  Apr1,  ...Dec AS Dec1, ... Null
AS Dec2
FROM Crosstab2
UNION ALL SELECT Null AS X, Null, Null, Null,  Null, ...    Null AS x, Jan
AS Jan1,  Feb AS  Feb1, Mar AS  Mar1,  Apr AS  Apr1,  ...Dec AS Dec2.
FROM Crosstab3;

Then use a totals query to roll it into a single row.  You then have 39
fields for the report.
Signature

KARL DEWEY
Build a little - Test a little

> Hello everyone
> I'd like to make a report like this one :
[quoted text clipped - 8 lines]
> Tks in advance
> Pedro
 
Sign In
Join
My Latest Posts
My Monitored Threads
My Blog
My Photo Gallery
My Profile
My Homepage

Start New Thread
Enable EMail Alerts
Rate this Thread



©2008 Advenet LLC   Privacy Policy - Terms of Use
This website includes both content owned or controlled by Advenet as well as content owned or controlled by third parties.