If this is your first visit, be sure to check out the FAQ by clicking the link above. You may have to register before you can post: click the register link above to proceed. To start viewing messages, select the forum that you want to visit from the selection below. |
|
|
Thread Tools | Display Modes |
#1
|
|||
|
|||
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 |
#2
|
|||
|
|||
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 |
Thread Tools | |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Thread Starter | Forum | Replies | Last Post |
Show in groups | lisa | General Discussion | 6 | August 20th, 2004 10:39 PM |
PP 2003 crashes on slide show | J Galt | Powerpoint | 6 | July 1st, 2004 03:21 PM |
Disabling Show in Groups on all folders | andreas gammel | General Discussion | 0 | June 8th, 2004 02:21 PM |
How to Show shortcut keys in toolbar buttons ScreenTips? | Dmitriy Kopnichev | Setting up and Configuration | 2 | March 24th, 2004 02:16 PM |
Do you have what it takes... | darno | Worksheet Functions | 0 | February 22nd, 2004 08:01 PM |