Report with vertical columns
in [Reports]
|
From: mnitin73 on 5 Jul 2008 23:51 I have a database for monitoring a small shop. Need help in creating the report for the stock status. I have the following tables 1. Stock - ItemID, Item Name, Price 2. Supply - ItemID, Date, Quantity 3. Sale - ItemID, Date, Quantity I need a report with 1. Item Name on column headers 2. Each item with three sub columns - Number Sold, Number Bought, Total Remaining (a running total) 3. One row with details above(2) for every date in range (even if no transaction) Need to do this soon as a stock muster is due. Would really appreciate any help I can get. Thanks
From: Allen Browne on 6 Jul 2008 06:38 There are several steps to get this to work. Firstly, use totals queries to get the total supplied and sold: 1. Create a query using the Supply table. 2. Depress the Total button on the toolbar Access adds a total row to the grid. 3. In the total row under ItemID, accept Group By. 4. In the Total row under Quantity, choose Sum. In the Field row in front of Quantity, type an alias for the column name, e.g.: SupplyQty: Quantity 5. In the Field row, enter an expression that represents your date period. For example, to report by quarter, you would enter this in one column: TheYear: Year(Supply.[Date]) and then in the Field row in the next column: TheQuarter: DatePart("q", Supply.[Date]) Accept Group By under both fields. 6. Test: the query should show the total quantity supplied per item per period. Save the query with a name such as qrySupplySummary. 7. Repeat the process for the Sale table, to get qrySaleSummary. 8. Now, you say you want a row output for every every product in every period, whether there were sales or not. The dates must come from somewhere, so you will need to create a table of the periods. The 2 fields to match the example above would be: TheYear Number Integer TheQuarter Number Integer with the combination of the 2 being the primary key. Save as (say) tblQuarter. 9. Create a query using your Stock and tblQuarter tables. There should be no line joining these to in the upper pane of query design (a Cartesian product.) This gives every possible combination (i.e. every quarter for every stock item.) The query will need to output 4 fields: ItemID, [Item Name], TheYear, and TheQuarter. Save as (say) qryStockQuarter. 10. Create a new query, using the 3 queries you created as input 'tables.' Join them on the fields: StockID, TheYear, and TheMonth. 11. Double-click the line joining ItemID in qryStockQuarter to ItemID in qrySupplySummary. Access pops up a dialog with 3 choices. Choose the one that says: All items from qryStockQuarter, and any matches from qrySupplySummary. Repeat for TheYear and TheMonth. (This is called an outer join.) 12. Repeat step 10 and 11, to make an outer join between qryStockQuarter.ItemID and qrySaleSummary.ItemID as well. 13. To get the difference, type an expression like this into a fresh column in the Field row: Diff: IIf([SupplyQty] Is Null, 0, [SupplyQty]) - IIf([SaleQty] Is Null, 0, [SaleQty]) That gets you every combination of stock item and date period, with the totals supplied and sold, and the difference. At this point, you have a query that gives you the figures you wanted, so you can create report. There are a couple of ways to to this. One is to create the columns in the report (Page Setup dialog.) The other is to use a crosstab query. That's going to get tricky, since you want 3 values and not just one for each item. If you want to proceed down that path, see: http://allenbrowne.com/ser-67.html#MultipleValues -- 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. "mnitin73" <u44681(a)uwe> wrote in message news:86b95cc202b7a(a)uwe... >I have a database for monitoring a small shop. Need help in creating the > report for the stock status. I have the following tables > 1. Stock - ItemID, Item Name, Price > 2. Supply - ItemID, Date, Quantity > 3. Sale - ItemID, Date, Quantity > > I need a report with > 1. Item Name on column headers > 2. Each item with three sub columns - Number Sold, Number Bought, Total > Remaining (a running total) > 3. One row with details above(2) for every date in range (even if no > transaction) > > Need to do this soon as a stock muster is due. Would really appreciate any > help I can get. > Thanks
From: mnitin73 via AccessMonster.com on 7 Jul 2008 13:56 Thank you very much. The steps were very well explained. Solved my problem. As for the report, I have used a pivottable instead. Seems to cater for my requirement. Thanks again, Nitin Allen Browne wrote: >There are several steps to get this to work. > >Firstly, use totals queries to get the total supplied and sold: >1. Create a query using the Supply table. > >2. Depress the Total button on the toolbar >Access adds a total row to the grid. > >3. In the total row under ItemID, accept Group By. > >4. In the Total row under Quantity, choose Sum. >In the Field row in front of Quantity, type an alias for the column name, >e.g.: > SupplyQty: Quantity > >5. In the Field row, enter an expression that represents your date period. >For example, to report by quarter, you would enter this in one column: > TheYear: Year(Supply.[Date]) >and then in the Field row in the next column: > TheQuarter: DatePart("q", Supply.[Date]) >Accept Group By under both fields. > >6. Test: the query should show the total quantity supplied per item per >period. Save the query with a name such as qrySupplySummary. > >7. Repeat the process for the Sale table, to get qrySaleSummary. > >8. Now, you say you want a row output for every every product in every >period, whether there were sales or not. The dates must come from somewhere, >so you will need to create a table of the periods. The 2 fields to match the >example above would be: > TheYear Number Integer > TheQuarter Number Integer >with the combination of the 2 being the primary key. >Save as (say) tblQuarter. > >9. Create a query using your Stock and tblQuarter tables. There should be no >line joining these to in the upper pane of query design (a Cartesian >product.) This gives every possible combination (i.e. every quarter for >every stock item.) The query will need to output 4 fields: ItemID, [Item >Name], TheYear, and TheQuarter. Save as (say) qryStockQuarter. > >10. Create a new query, using the 3 queries you created as input 'tables.' >Join them on the fields: StockID, TheYear, and TheMonth. > >11. Double-click the line joining ItemID in qryStockQuarter to ItemID in >qrySupplySummary. Access pops up a dialog with 3 choices. Choose the one >that says: > All items from qryStockQuarter, and any matches from qrySupplySummary. >Repeat for TheYear and TheMonth. >(This is called an outer join.) > >12. Repeat step 10 and 11, to make an outer join between >qryStockQuarter.ItemID and qrySaleSummary.ItemID as well. > >13. To get the difference, type an expression like this into a fresh column >in the Field row: > Diff: IIf([SupplyQty] Is Null, 0, [SupplyQty]) - IIf([SaleQty] Is Null, >0, [SaleQty]) > >That gets you every combination of stock item and date period, with the >totals supplied and sold, and the difference. > >At this point, you have a query that gives you the figures you wanted, so >you can create report. There are a couple of ways to to this. One is to >create the columns in the report (Page Setup dialog.) The other is to use a >crosstab query. That's going to get tricky, since you want 3 values and not >just one for each item. If you want to proceed down that path, see: > http://allenbrowne.com/ser-67.html#MultipleValues > >>I have a database for monitoring a small shop. Need help in creating the >> report for the stock status. I have the following tables >[quoted text clipped - 12 lines] >> help I can get. >> Thanks -- Message posted via http://www.accessmonster.com
|
Pages: 1 Prev: Converting from MS-Access 2003 to 2007 Next: change a reports control backcolor |