I am needing to take data from one data set that consists of a list of parts and their stock information. Then I am needing to show any transactions that the individual parts may have had during the time that user defined through the filters.
This requires me to have two data sets since I can't do a join between the stocking information and the transactions due to the fact that it is very possible that every part won't have a transaction for each month of each year. Doing a left join still filters down the data too much so thus the need for two data sets.
I have these parameters:
Vendor, Year, Month, Type of request
Vendor and Type of Request is handled through StockInfoDataset. I then need to update the table showing 0 or transaction amount from TransactionDataset filtered by the ItemNumber and WarehouseCode that is in StockInfoDataset. Since it seems List and Table only allow one dataset in them, I am at a loss as to how I would go about doing this.
Explaining this is quite difficult in a forum post, but hopefully someone would be able to shine some light on possibly avenues to follow here....thank you in advance.
Josh
I'm not sure I understand 100% what you're facing, but here's how I would try to tackle this...
Each parameter can have a separate dataset, for lookup purposes.
Then the main dataset can be based on the selected report parameters.
Finally, I would use either a view or a stored procedure to return the needed dataset.
Using the view/sp provides a lot of flexibility that direct table queries do not, including the ability to join, summarize, and otherwise process values from multiple tables.
I hope that helps.
|||Your solution may very well work, but I have never done it that way so I am a bit at a loss.....
Unfortunately, unless there is something I am missing, I can't use a view or stored procedure to derive this information....here is an example of what I am wanting to do
Vendor = ABC, Year = 2006, Month = 07, Type = Qty On Hand > 0
Table A
[Column AA] [Column AB] [Column AC]
Item1 Whs1 QtyOnHand1
Item2 Whs1 QtyOnHand2
Item3 Whs2 QtyOnHand3
Item4 Whs1 QtyOnHand0
Table B
[Columnb BA] [ColumnBB] [Column BC] [ColumnBD]
Item1 Whs1 06/01/06 QtySold1
Item1 Whs1 07/01/06 QtySold2
Item2 Whs1 05/01/06 QtySold3
Item3 Whs2 06/01/06 QtySold4
Item4 Whs1 07/01/06 QtySold5
So when I do
SELECT * FROM [TableA] LEFT OUTER JOIN [TableB] ON [TableA].[Column AA] = [TableB].[Column BA] AND [TableA].[Column AB] = [TableB].[Column BB] WHERE [TableA].[QtyOnHand] > 0
I am going to get a result set of
Item1 Whs1 QtyOnHand1 Item1 Whs1 06/01/06 QtySold1
Item1 Whs1 QtyOnHand1 Item1 Whs1 07/01/06 QtySold1
Item2 Whs1 QtyOnHand2 Item2 Whs1 05/01/06 QtySold3
Item3 Whs2 QtyOnHand3 Item3 Whs2 06/01/06 QtySold4
This is essentially what my View Does that I am selecting from....
Now in my report I have them select year and month......to filter what transactions are going to be summed.
My problem is that I still need to show the qtyOnhand and such even though there my be no transactions for the time period they select....so using the parameters above. This is what I get then after that I'll show what I want...
This is what I get:
Item1 Whs1 QtyOnHand1 Item1 Whs1 07/01/06 SUM(QtySold1)
This is what I want:
Item1 Whs1 QtyOnHand1 Item1 Whs1 06/01/06 0
Item1 Whs1 QtyOnHand1 Item1 Whs1 07/01/06 SUM(QtySold1)
Item2 Whs1 QtyOnHand2 Item2 Whs1 05/01/06 0
Item3 Whs2 QtyOnHand3 Item3 Whs2 06/01/06 0
I hope this clarifies things a bit....
Thank you,
Josh
|||It seems like it's possibly to query what you need and get it in 1 dataset, then maybe doing some filtering or special exclusions, but first,
can you please give your relevant tables and their column names? I know you were trying to be discrete, but COLUMNAA, COLUMNAB is hard to put into motion. After that, I'm sure someone can get you a query that will do the job (full outer join might be necessary).
