Please Help! Make-table loop?

[KrissyB]

I have a table called INVUPC with no Primary Key that lists invalid UPC for products. They are sorted by Vendor. I want to be able to seperate the records by vendor into different tables, or into different excel files.

TABLE could consist of only INVUPC and VENDORNUMBER. This is dynamic data so I would like a macro to be able to sort these into different files.

Any info would be greatly appreciated, I have only recently begun working with Access but am familiar with SQL.

 

[jdegeorge]

KrissyB

You should start out by creating a query that will at least select what you're looking for. Once you show the folks here that you've gotten that far they'll help you with the rest.

 

[KrissyB]

One of my first attempts at VBA:

Option Compare Database

Function Vendors()
Dim CurComm As New ADODB.Connection
Dim RS As New ADODB.Recordset
Dim VendorID
Dim i

Set curDB = CurrentDb
Set CurrConn = New ADODB.Connection

With CurConn
.Provider = "Microsoft.Jet.OLEDB.4.0"
.ConnectionString = "datasource=" & curDB.Name
End With

Set RS = New ADODB.Recordset
RS.CursorType = adOpenDynamic
RS.LockType = adLockOptimistic
RS.Open "Select * From INVUPC;", CurConn, , , adCmdText
i = 0
Do Until RS.EOF
For Each VENDOR In RS.Fields
VendorID(i) = VENDOR.Value
i = i + 1
Next
RS.MoveNext
Loop
RS.Close
CurConn.Close

End Function

What I want to do next is to create a loop to get result sets in a select format kinda like this:

SELECT Vendor, UPC from INVUPC WHERE Vendor=(VendorID from above loop) and output these results to seperate tables.

Any help would be great.

 

[jdegeorge]

Krissy

So as not to leave you hanging overnight, here's what I started working on:

Dim db As Database
Dim rs As Recordset
Dim rs1 As Recordset
Dim strSQL As String
Dim xVendorNumber As String ' string to contain VENDORNUMBER

Set db = CurrentDb

'first of all insert list of projects into table TempTable
strSQL = ("SELECT tblINVUPC.* FROM tblINVUPC&quot

Set rs = db.OpenRecordset(strSQL)


Do Until rs.EOF = True
'for each VENDORNUMBER create a string of INVUPC
xVendorNumber = ""

Dim sql As String
sql = "SELECT INVUPC FROM rs WHERE xVendorNumber ='" & rs!VENDORNUMBER & "'"

Set rs1 = db.OpenRecordset(sql)
'inner loop to create a concatenated string of stores

Do While rs1.EOF = False
db.Execute "INSERT INTO str$(xVendorNumber) SELECT * FROM rs1;"
rs1.MoveNext
Loop

rs.MoveNext
Loop

'release the memory
Set rs = Nothing
Set rs1 = Nothing
Set db = Nothing

It doesn't work yet, but it let's you see where I'm going with this. I hope to have more time to work on this tomorrow.

Jim DeGeorge

 

[KrissyB]

Thanks so much for your help on this thus far...
I have been working with this in Access and have had no luck yet but will definitely keep trying. If I could get this to work the user app would be soo much easier!

Thanks again!

 

[jdegeorge]

Krissy

One question...why do you need to separate the data into different tables? The idea behind relational databases is to keep like items together without duplicating information. In your case, it sounds like you have a table with invalid UPC codes and Vendor numbers. There probably is another table with Vendor detail that you can link to the Vendor number in the 1st table.

By using some simple queries, you can always get a list of vendor information and their invalid UPC codes.

Please clarify why you need to do this. I'm still trying to figure it out, but in the process, it would be nice to know if all this effort is really necessary. Thanks.

Jim DeGeorge

 

[KrissyB]

OK here goes.

I have a million P.O's and these are the ones that include Invalid UPC's. I need to send each vendors its own UPCs, via a template we have for the vendors. It is not really for me, its a means to gain the information on these UPC's from the vendors. So unfortunately it is imparative to either manually or automatically generate a way to seperate the data and send via email. If you have any other suggestions on how to go about this it would be great. Hope this helps!

 

[jdegeorge]

Krissy

If you give me your email address, I can send you the database shell. Got some good help from some folks on line here. Turns out I wasn't too far off from what was needed (codewise).

Jim DeGeorge

 

[KrissyB]

Sure Jim.

I'm at cbotham@indigo.ca

Would be fantastic! Thanks for your continued help!

Christine

 

[jdegeorge]

On the way

Jim DeGeorge