Results 1 to 9 of 9
  1. #1
    Join Date
    Oct 2004
    Posts
    75

    Unanswered: spreadsheet-style reports

    hey all, I've got a general question about reports that's been bugging me for ages....is it possible to make a spreadsheet style report in Access? An example that I've come across of what I mean is someone who was trying to do a Stock Cost report. This report was originally in Excel, with Item names down the side, and along the top were two sets of headings (or groupings if you will). First there was the location of the stock (since each stock item existed seperately in each location and had a different stock cost), then for each location, there were "sub-columns" for StockQty, StockCost, and TotalValue[StockQty*StockCost]. Obviously there's a M-M relationship between each Stock Item and Location, but the thing about a report like this in Excel is that it's "sorted" by both Item AND Location....a person can look along a row to find out everything there is to know about the Stock Cost for a particular item, or they can look down a column to find out everything there is to know about each item in a particular location.
    But it seems impossible to do this in Access....when making this report, you'd have to either sort by Location or by Item. This seems very restrictive; to get the same usefulness as the Excel sheet, you'd have to make two reports!
    I hope that's explained what I'm talking about. It just seems to me that that would be a huge red cross against changing to a database (from a non-savvy client's point of view) since these kinds of reports become so much less efficient in the process. Any thoughts? If I'm missing something please let me know! Thanks!

  2. #2
    Join Date
    Apr 2004
    Location
    outside the rim
    Posts
    1,011
    Bane - I'm trying to picture what you want - which is hard due to the quality of the wine I have been drinkin!

    First - you always have the option of creating the report in Excel from Access - there are many instances where that is an ideal solution, although it may require a bit of coding to make it look good.

    Second - can you get a Form to do what you want? Forms can be used as reports when necessary, and there are some advantages to doing that. For example, when you export to Excel, the field formatting on a form is honored, but the formatting on a report is not. Also, the Tab Order defines the column order in Excel.

    Third - sounds to me like you want to use sub reports because you said:
    then for each location, there were "sub-columns"
    hope this helps!
    Last edited by tcace; 03-30-06 at 00:03.

  3. #3
    Join Date
    Oct 2004
    Posts
    75
    heh sounds like good wine tcace!
    hmm it does sound like I need to look into how to export to Excel, coz it's seeming less likely that what I'm talking about can be done in Access....it could be done in a form, but unfortunately would not result in a readable printed version
    Just to clarify what I was saying about sub-columns...the report/spreadsheet I was describing has a single Row Heading, the Items. Then for the columns, there is one heading for each Location, then (this is the "sub-column" bit) for each Location column, there are 3 columns (the SAME 3 columns for each Location) showing the Qty, Price and Qty*Price for each Item (Row) at each Location (Main Column)....and I'm not sure this kind of format can be done in Access, rather, it would have to be sorted either by
    Item (with Locations, Qtys etc listed for each Item...resulting in duplicating the Location for each Item), or by
    Location (with Items, Qtys etc listed for each Location...resulting in duplicating the Items for each Location)
    Hmm does that make sense?

  4. #4
    Join Date
    Apr 2004
    Location
    outside the rim
    Posts
    1,011
    but unfortunately would not result in a readable printed version
    on the contrary, it is very easy to setup a form to be printable.
    As I said before - sometimes it is preferable to print a form instead of a report.

    Now, about this spreadhseet. I'm still not clear on how it looks (for the record, I am drinking wine again - a good Australian Shiraz).

    Does it look like this:
    Code:
    	Location 1		Location 2		Location 3		Location 4	
    	Qty	Price	Total	Qty	Price	Total	Qty	Price	Total	Qty	Price	Total
    Item 1	1	 $0.25 	 $0.25 	5	 $0.15 	 $0.75 	2	 $0.18 	 $0.36 	7	 $0.18 	 $1.26 
    Item 2	2	 $0.35 	 $0.70 	2	 $0.22 	 $0.44 	4	 $0.22 	 $0.88 	1	 $0.20 	 $0.20 
    Item 3	1	 $0.30 	 $0.30 	4	 $0.37 	 $1.48 	3	 $0.27 	 $0.81 	6	 $0.19 	 $1.14
    That being asked, I just threw this together in Excel. I assume that the price is dependant on both item and it's location. How is the data in the DB: one table with each item as a record and multiple fields for each location or more dynamic with several tables in a many to many relationship?

    What would help is a sample spreadsheet.

    I'm willing to bet that if the information comes from a relational database, we can get it into a report. Creating reports, even complicated ones, is a specialty of mine

    tc

  5. #5
    Join Date
    Apr 2004
    Location
    metro Detroit
    Posts
    634
    Have you tried playing with a crosstab query and using that as the source of your report?

  6. #6
    Join Date
    Oct 2004
    Posts
    75
    Quote Originally Posted by tcace
    I'm willing to bet that if the information comes from a relational database, we can get it into a report. Creating reports, even complicated ones, is a specialty of mine
    You're just the person I want to talk to then =)

    Yep, what you did there is pretty much what the report looks like. Just to make it even trickier tho, there's also two extra fields on the right after all the Locations....one shows the average of the Price over all Locations, and the other shows the sum of the Qty*Price over all Locations

    The data for the report is stored in two tables...all the data about a particular stock item (I guess only the Name is relevant here) is in one table, and all the data pertaining to one instance of that item in one Location is in a second table (Location, QtyInStock, Price etc)

    I'm still stumped....it's all yours tcace!

    Oh, and I tried using a crosstab query too, but it doesn't look like it's any good for me since it only lets you have one column heading

  7. #7
    Join Date
    Apr 2004
    Location
    outside the rim
    Posts
    1,011
    Looks like you could use queries to divide up the item information by location. Each query would contain the data in the "Location Column".

    From here, you have 2 options, depending on the type of output you want.

    If you want a spreadsheet output (literally an export to a spreadsheet) then you use a final query with each location query outside joined to the item table - this gives you individual location groups aligned by item in each row. Export this query to Excel, and you have created the spreadsheet I inserted. For the extra 2 fields, tack them on at this point.

    If you want a printed report formatted for the printer, you use the final query above, arranging the fields as required.

    In my experience, this type of situation should have a summary in the printed report and the export to Excel for all the data since there appears to be a lot of data in each row and formatting for readability is important.

    Now, if you want to get fancy and have the outputs be dynamic (variable number of locations each time you run it) then coding is required to create the necessary subqueries for you. Using a detailed report would be too much work - I'd rely on the Export to Excel at that point. Personally, I'd make the Export give you everything since it is very easy for the user to remove the locations they don't want simply by deleting the columns in the spreadsheet.

    Good luck!
    have fun!

    Todd S.
    click to email


    Independent Development Consultant
    Biz db & reports - SQL, Access, Crystal, Seradex and more
    Small, custom, unique programs - Office, VBA, .NET


    _________________________________________________
    Favorite message from Windows:
    There was an error displaying the previous error message

    Sadly, there was no error number to look up ...

  8. #8
    Join Date
    Apr 2004
    Location
    outside the rim
    Posts
    1,011
    You could also skip all the queries and use a module with some code that runs through the data as recordsets and creates a flat table with the information divided up the way you want.

    I'm assuming that you are looking for the non-code solution at this point.

  9. #9
    Join Date
    Apr 2004
    Location
    outside the rim
    Posts
    1,011
    It works with the queries.

    Each "Sub Query" looks like this:
    Code:
    SELECT tblStock.ItemKey, tblStock.LocaKey AS Location1, tblStock.QytInStock AS Qty1, tblStock.Price AS Price1
    FROM tblStock
    WHERE (((tblStock.LocaKey)=1));
    Notice that i masked each field with the number.

    The "Mater Query" looks like this:
    Code:
    SELECT tblItems.ItemKey, tblItems.ItemName, 
    qry1.Location1, qry1.Qty1, qry1.Price1, [Qty1]*[Price1] AS Total1,
    qry2.Location2, qry2.Qty2, qry2.Price2, [Qty2]*[Price2] AS Total2,
    qry3.Location3, qry3.Qty3, qry3.Price3, [Qty3]*[Price3] AS Total3,
    qry4.Location4, qry4.Qty4, qry4.Price4, [Qty4]*[Price4] AS Total4,
    (Nz(Qty1)+Nz(Qty2)+Nz(Qty3)+Nz(Qty4)) AS GrandQty,
    (Nz(Total1)+Nz(Total2)+Nz(Total3)+Nz(Total4)) AS GrandTotal
    FROM (((qry1 RIGHT JOIN tblItems ON qry1.ItemKey = tblItems.ItemKey) LEFT JOIN qry2 ON tblItems.ItemKey = qry2.ItemKey) LEFT JOIN qry3 ON tblItems.ItemKey = qry3.ItemKey) LEFT JOIN qry4 ON tblItems.ItemKey = qry4.ItemKey;
    I used the Nz functions so that items that are not found in some locations (and therefore return a null) do not prevent the GrandTotals from calculating.

    I used 3 tables: 1 for items, 1 for locations and 1 for stock.
    The item table has a key and a name.
    The location table has a key and a name
    The stock table has item key, location key, quantity and price

    The output from the query is:
    Code:
    1	Item 1	1	1	$0.25	$0.25	2	5	$0.15	$0.76	3	2	$0.18	$0.36	4	7	$0.18	$1.26	15	2.63
    2	Item 2	1	2	$0.35	$0.70	2	2	$0.22	$0.44	3	4	$0.22	$0.88	4	1	$0.20	$0.20	9	2.22
    3	Item 3	1	1	$0.30	$0.30	2	4	$0.37	$1.48	3	3	$0.27	$0.81	4	6	$0.19	$1.14	14	3.73
    4	Item 4	1	10	$0.13	$1.30					3	11	$0.14	$1.54					21	2.84

    You'll notice I took the sample spreadsheet I created before and used that as the data, but that I also added an Item 4, which only has stock in 2 of the locations.

    Taking this query to a report would be straight forward.

    Have fun!
    have fun!

    Todd S.
    click to email


    Independent Development Consultant
    Biz db & reports - SQL, Access, Crystal, Seradex and more
    Small, custom, unique programs - Office, VBA, .NET


    _________________________________________________
    Favorite message from Windows:
    There was an error displaying the previous error message

    Sadly, there was no error number to look up ...

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •