-
I have a table that I am trying to generate a report for. In the table is a customer Id and a vendor Id. In the vendor ID I get two results for the same customer ID. A negative number before the order is processed and a postive number when the item has made full circle. I need to extract every matching customer ID with its corresponding negative and positive number:
right now if i do a search say on customer '10050' i get two vendor numbers on with a negative and one with a postive like this:
10050 -1000
10050 0
I really need it to be:
10050 -1000 0
10051 -1000 0
I need to return all records in a certain time frame not just '10050'
Do I need a temp table. An example would be nice - thank you!!!
This is done in SQL using and SQL database
-
Hi,
I don't know if your are in VB or directly in SQL SERVER but I may have a solution for you...
Make a copy of your table (same field names...) and name it "temp".
1) INSERT INTO temp (SELECT * FROM Table1 WHERE
Vendor_ID < 0)
You have just splitted your data in two table with 1 beeing
still the original and the other having all -.
SELECT * FROM Table1 INNER JOIN temp ON Table1.CustumerID =
temp.CustumerID
not tested but I think it could work...
Hope I helped...
-
Is it possible to use a subquery instead? doing a sort of self-join?
-
I tried this, but I am getting an error that says error around the temp word or whatever any ideas?
I think this is on the right track; anyone know?
