Multiple Datasets but needs to be tied together

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).