Show all publications

Hi All

I hope someone can help me out as I'm a bit stumped with this...

I have 3 tables to record information about clients and their associated
publications

publications { pub_name, pub_id}
clients {client_name, client_id}
press_cuttings {cutting_id, pub_id, client_id, pub_date, page_fraction,
notes}

I need to create a query that for each client will list
1. the total page_fraction per month (the easy bit!)
2. publication names in the database, even those that have not covered the
client

So the output would be something like this:

Client A Jan Feb etc
----------
Publication 1 .5 .6 etc
Publication 2 .2 .3 etc
Publication 3
Publication 4 .24 .5 etc

where Publication 3 has not had any coverage of Client A. Its important to
see which publications Client A hasn't been in as well as those he has been
in.

I hope this makes sense!

Mo

1. Create a query into tables Publications and Press_Cuttings.

2. Double-click the line joining the 2 tables in the upper pane in query
design. Access pops up a dialog with 3 options. Choose the one that says:
All records from Publications, and any matches from ...
This is an "outer join".

3. Type this into a fresh column in the Field row:
TheYear: Year([pub_date])
and in the next column:
TheMonth: Month([pub_date])
The calculated fields will give you the grouping by month.

4. Drag these fields into the grid: Client_ID, Pub_Name, Page_Fraction.

5. Change it to a Totals query (View menu). Access adds a Total row to the
grid. In the Total run under Page_Fraction, change it to Sum. Accept "Group
By" under the other fields.

6. Save the query. Close.

7. Create another query using the first one as an input "table".

8. Change it to a Crosstab query. (Query menu).

9. Add these fields to the grid, and set them up like this:
Row Header: TheYear
Row Header: ClientID
Row Header: Pub_ID
Row Header: Pub_Name
Column Header: TheMonth
Value: SumOfPage_Fraction

10. Open the Properties box (View menu). In the Properties for the query
(not for a field), set the Column Headings to:
1,2,3,4,5,6,7,8,9,10,11,12
This ensures the query supplies columns for all 12 months, so the report
gets what it expects.

11. Save the query.

12. Create a report based on the crosstab query. (This will be a subreport).
Presumably it will have a Group Header for TheYear.

13. Create a main report based on your clients. This ensures all clients are
listed, and gives you access to all the client details you wish to show. The
subreport will list their publications.

Hopefully that approach will get you to where you want to go. :-)

--
Allen Browne - Microsoft MVP. Perth, Western Australia.
Tips for Access users - http://allenbrowne.com/tips.html
Reply to group, rather than allenbrowne at mvps dot org.

"no" wrote in message ...
Hi All

I hope someone can help me out as I'm a bit stumped with this...

I have 3 tables to record information about clients and their associated
publications

publications { pub_name, pub_id}
clients {client_name, client_id}
press_cuttings {cutting_id, pub_id, client_id, pub_date, page_fraction,
notes}

I need to create a query that for each client will list
1. the total page_fraction per month (the easy bit!)
2. publication names in the database, even those that have not covered the
client

So the output would be something like this:

Client A Jan Feb etc
----------
Publication 1 .5 .6 etc
Publication 2 .2 .3 etc
Publication 3
Publication 4 .24 .5 etc

where Publication 3 has not had any coverage of Client A. Its important to
see which publications Client A hasn't been in as well as those he has
been in.

I hope this makes sense!

Mo