Access & ASP - SQL Server Forums

Please start any new threads on our new site at https://forums.sqlteam.com. We've got lots of great SQL Server experts to answer whatever question you can come up with.

All Forums

Other Forums

MS Access

Access & ASP

Author

Topic

matt.orme
Starting Member

23 Posts

Posted - 2009-02-12 : 13:44:26

I am trying to put together a list of related items (SKUs). Items are defined as related if they were purchased at the same time, which is signified by them being on the same "ticket".

I have a table in Access 2003 called "Related" which contains a list of tickets and the items sold on the ticket. The table is formatted with each SKU being unique to a ticket. For example:

L39729 ; 11012460 ;
L39729 ; 190370 ;
L39729 ; 11024110 ;
L39729 ; 11024090 ;
L39729 ; 61816290 ;
L39729 ; 10567455 ;
L39729 ; 10289820 ;
L39729 ; 45368 ;
L39729 ; 199010 ;
L39729 ; 10314200 ;
L39729 ; 10327810 ;
L39729 ; 11132160 ;
L39729 ; 10326940 ;
L39729 ; 02022008 ;
L39729 ; 01014008 ;
L39729 ; 10326580 ;

This table is 980,000 records long.

Also, I have a table containing all of the unique SKUs sold, called "Individual". This table is approximately 53,000 rows.

My plan was to use vbscript through ASP, which is all I have any real exposure to, to loop through all the unique SKUs, checking for the tickets that they are on and then running through the raw data to collect all the products on those tickets. The problem is that this job is huge and causes a session timeout.

This is the code I have been running.

<%
Dim objConn
Set objConn = Server.CreateObject("ADODB.Connection")
objConn.ConnectionString = "DSN=Related.dsn"
objConn.Open
Server.ScriptTimeout = 60000

Dim individualRS, relatedRS, ticketRS, individualSQL, relatedSQL, ticketSQL
'Individual is a table in which I have all the unique items I need to collect related product data for
individualSQL = "SELECT * FROM Individual"
Set individualRS = Server.CreateObject("ADODB.Recordset")
individualRS.Open individualSQL, objConn
'If I comment out this loop, I get results for a single item in a relatively timely manner
Do While Not individualRS.eof
'ticketRS serves as a place to list all of the tickets on which the item currently being examined was sold
'Related is the raw sales data
Set ticketRS = Server.CreateObject("ADODB.Recordset")
ticketSQL = "SELECT * FROM Related WHERE SKU = '" & individualRS("SKU") & "'"
ticketRS.Open ticketSQL, objConn
Do While Not ticketRS.eof
'relatedRS is the raw sales data which lists each item sold on each ticket
Set relatedRS = Server.CreateObject("ADODB.Recordset")
relatedSQL = "SELECT SKU FROM Related WHERE Ticket = '" & ticketRS("Ticket") & "'"
relatedRS.Open relatedSQL, objConn
Do While Not relatedRS.eof
Response.Write individualRS("SKU") & " ; " & ticketRS("Ticket") & " ; " & relatedRS("SKU") & " ; <br>"
relatedRS.movenext
Loop
relatedRS.close
Set relatedRS = Nothing
ticketRS.movenext
Loop
ticketRS.close
Set ticketRS = Nothing
individualRS.movenext
Loop
individualRS.close
Set individualRS = Nothing
objConn.close
Set objConn = Nothing
%>

My question is not IF there is a better way, but what the better way is and how to implement it. My exposure to Access is really limited, so I don't know what it is capable of. I only know that using basic SQL statements, I cannot think how to do what I want in a batch format. Getting results for a single SKU is easy, both with vbscript and with Access, but getting results for every SKU has been a hurdle.

Any help would be greatly appreciated, as I have been banging my head on this for a while.

SwePeso
Patron Saint of Lost Yaks

30421 Posts

Posted - 2009-02-12 : 15:06:38

You now process records "line-by-line", with putting together as recordset built on other resultset data.
Think set-based!

E 12°55'05.63"
N 56°04'39.26"

matt.orme
Starting Member

23 Posts

Posted - 2009-02-13 : 17:30:20

Are you referring to using stored procedures? If so, I am under the impression that they are not supported in Access 2003.

Also, I have no real exposure to VBA, which seems like it may be able to handle this problem of mine fairly well. Any further recommendations would be greatly appreciated.

matt.orme
Starting Member

23 Posts

Posted - 2009-02-17 : 19:07:44

Shameless bump on my part.

I have been banging my head against it since it was posted. I assume the previous poster was telling me to use stored procedures, but I am completely unaware of how to do this, especially in Access. My only desk reference is for SQL server, which I do not have access to.

If someone could give me a hand with the code, I would be eternally grateful. Failing that, if someone could point me to a good resource for how to learn how to write and implement stored procedures for Access, that would be great also.

Sorry again for the bump, but this problem has me at wits end.

robvolk
Most Valuable Yak

15732 Posts

Posted - 2009-02-17 : 21:53:53

Beyond the code issues, there's some logic that's not clear from your description:

quote:
Items are defined as related if they were purchased at the same time, which is signified by them being on the same "ticket"

Let's say I have SKU's A, B and C, and they all appear on the same ticket 123. Is it enough to say A->B, A->C, and B->C (combinations), or do you also need B->A, C->A, C->B as well (permutations)? It's important because if you need permutations you'll be dealing with A LOT more data...twice as much in this 3-row example, imagine a ticket with 10 items. And the way the code is written now you'll also get A->A, B->B, and C->C (reflexive).

Also, what happens if A,B,C also appear on ticket 456? Do you count those again? Do the tickets really matter in what you're trying to present? You're putting them in your output but they don't seem to factor in the original requirement.

I have a solution that will speed the ASP part up quite a bit, but I need to know the answers to the above questions. It seems that you are pulling way more data that you'll actually use. If all you really need are combinations of SKU's, then you don't even need the Individual table.

The following should give you the same results, but may still time out because of the volume of data:

Dim objConn, rs, rsSQL, rsHTML
Server.ScriptTimeout = 60000
Set objConn = Server.CreateObject("ADODB.Connection")
objConn.ConnectionString = "DSN=Related.dsn"
rsSQL="SELECT I.SKU IndivSKU, T.Ticket, R.SKU RelatedSKU FROM Individual I INNER JOIN Related T ON I.SKU=T.SKU INNER JOIN Related R ON T.Ticket=R.Ticket WHERE I.SKU<>R.SKU"
objConn.Open
rs.Open objConn, rsSQL
rsHTML=rs.GetString(,," ; ", " ; <br>", "")
rs.Close
objConn.close
Set objConn = Nothing
set rs=Nothing
Response.Write rsHTML

It will at least avoid reflexive/self-references by having the WHERE I.SKU<>R.SKU in there.

See how that works and let me know the answers to the other questions.

matt.orme
Starting Member

23 Posts

Posted - 2009-02-18 : 11:31:16

Sorry about the lack of clarity. I will attempt to explain:

For SKUs A, B, C sold on ticket 123 and 456

What I NEED is:
A->B
A->C
B->A
B->C
C->A
C->B
'end of ticket 123
A->B
A->C
B->A
B->C
C->A
C->B
'end of ticket 456

The reflexive A->A, as you mentioned, is not necessary, but I knew it was there. I was willing to accept it as the cost of a quick answer.

What I WANT, though, which I am able to get without a problem as long as I feed Access an individual SKU, is the SKU which is being related to, the SKUs which relate to it and the count of the number of instances of the related SKUs on all tickets on which the seed SKU appears. For example, for tickets 123 and 456:

A->B->2
A->C->2
B->A->2
B->C->2
C->A->2
C->B->2

You are right in that I don't need the tickets in the end result, but the reason I posed the question the way I did and wrote the code the way I did (albeit poorly) is that I had given up on the idea of ever getting the count output by the ASP. I was planning on using the html that was generated as an intermediate step to get to where I wanted to go.

Anyway, I am going to try your code right away and I will post back the results.

matt.orme
Starting Member

23 Posts

Posted - 2009-02-18 : 11:40:55

Having just tried the code, it has thrown an error on the line:

"rs.Open objConn, rsSQL"

The error reads:

Error Type:
Microsoft VBScript runtime (0x800A01A8)
Object required: ''
/test.asp, line 26

robvolk
Most Valuable Yak

15732 Posts

Posted - 2009-02-18 : 22:06:34

Ugh, sorry about that, just reverse them:

rs.Open rsSQL, objConn

Based on your answer, I think this should give you the results you want:


rsSQL="SELECT T.SKU IndivSKU, R.SKU RelatedSKU, count(distinct R.Ticket) Tickets, count(*) Lines FROM Related T INNER JOIN Related R ON T.Ticket=R.Ticket WHERE T.SKU<>R.SKU GROUP BY T.SKU, R.SKU"

The rest of the code should be fine as-is. I added 2 counts, in case a SKU combination appears more than once on a ticket. You'll get 1 count for each distinct ticket and another for the total number that appear. You can easily remove the one you don't want.

matt.orme
Starting Member

23 Posts

Posted - 2009-02-19 : 11:33:34

Technically, since it appears you have done away with the loop, couldn't I just copy the SQL statement you made directly into Access and let it do it's thing?

Also, another rookie question, but is that SQL statement copy and paste ready, or do I need to do something to it?

robvolk
Most Valuable Yak

15732 Posts

Posted - 2009-02-19 : 11:42:34

Should paste fine, I haven't tested it though. Access sometimes needs parentheses around join clauses, I know its query designer loves to put them in. It will likely reformat it if you switch from SQL to Design view but it should still work.

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources