473,322 Members | 1,526 Online
Bytes | Software Development & Data Engineering Community
Post Job

Home Posts Topics Members FAQ

Join Bytes to post your question to a community of 473,322 software developers and data experts.

Finding Unique Account Numbers

Hi All. I'm trying to write an ASP page that shows me the UNIQUE account number for a customer (so I can pass it to another page) based on a search criteria. For example, I want to do a select statement on the customer table and orders table (joining on the ACCTNO) for all "Video" products.

Ex:

sq = "SELECT CUSTOMER.ACCTNO, CUSTOMER.NAME FROM CUSTOMER INNER JOIN ORDERS ON CUSTOMER.ACCTNO = ORDERS.ACCTNO
WHERE PRODUCT_TYPE = 'Video' "

Once I get the result set back I want to get a list of the UNIQUE account numbers to pass to a SQL Statement on the next page that would have a WHERE ACCTNO IN "( & var & ") " Ex:

sql = "SELECT * FROM CUSTOMER WHERE ACCTNO IN (" & AcctList & ") "

What I was trying to do (in the first result set) was something like this:

do while not rs.eof
AcctList = AcctList & trim(rs("ACCTNO")) & ","
rs.movenext
loop

AcctList = Left(AcctList, len(AcctList) - 1)

The problem is that the list I get back looks like this:

(42,42,42,42,31,31,56,56,56,56,56,56,70,77,77,77 ....... etc )

How do I get just ONE account number for the search criteria?

Thanks
Jul 19 '05 #1
3 1908
David Berry wrote:
Hi All. I'm trying to write an ASP page that shows me the UNIQUE
account number for a customer (so I can pass it to another page)
based on a search criteria. For example, I want to do a select
statement on the customer table and orders table (joining on the
ACCTNO) for all "Video" products.

Ex:

sq = "SELECT CUSTOMER.ACCTNO, CUSTOMER.NAME FROM CUSTOMER INNER JOIN
ORDERS ON CUSTOMER.ACCTNO = ORDERS.ACCTNO
WHERE PRODUCT_TYPE = 'Video' "

Once I get the result set back I want to get a list of the UNIQUE
account numbers I am assuming there is only one account number per customer. If I'm wrong,
this suggestion will need to be amended.

Use either GROUP BY, or the DISTINCT keyword in your query (please use table
aliases to make your queries more readable).

sq = "SELECT c.ACCTNO, c.NAME FROM CUSTOMER c INNER JOIN ORDERS oON c.ACCTNO
= o.ACCTNO WHERE PRODUCT_TYPE = 'Video' GROUP BY c.ACCTNO, c.NAME "

or

sq = "SELECT DISTINCT c.ACCTNO, c.NAME FROM CUSTOMER c INNER JOIN ORDERS o
ON c.ACCTNO = o.ACCTNO WHERE PRODUCT_TYPE = 'Video' "
to pass to a SQL Statement on the next page that would have a WHERE ACCTNO IN "( & var & ") " Ex:
sql = "SELECT * FROM CUSTOMER WHERE ACCTNO IN (" & AcctList & ") "


Don't use selstar. Always specify the fields you want to retrieve so ADO
does not have to make an extra trip to the database to resolve the * into
the actual field names.

I'm not clear on the need to do this. IN (<list>) is not very efficient. It
should be avoided when there is a more efficient method. In this case, your
first query already returns the list of account numbers you're interested
in. Why not just use the PRODUCT_TYPE criterion again to retrieve these new
results? It should be more efficient than using IN (<list>) ...

HTH,
Bob Barrows

--
Microsoft MVP - ASP/ASP.NET
Please reply to the newsgroup. This email account is my spam trap so I
don't check it very often. If you must reply off-line, then remove the
"NO SPAM"
Jul 19 '05 #2
Hi Bob. Thanks. I tried DISTINCT and GROUP BY but that didn't work. Yes,
there is one unique account number for each customer. See if this makes
more sense:

On the first ASP page I'm doing a select of a bunch of fields from the
CUSTOMER and ORDERS table joining on ACCTNO. Then I display a table that
show the number of order <30 Days Past Due, 30-60 Days Past Due etc... When
they click on "VIEW" (next to that column) I send the AcctList to the second
page that pulls up a list all of the customers.

So the first page might be:

<30 = 13 VIEW
30-60 = 4 VIEW
60 - 90 = 5 VIEW

The first select looks like this:

SELECT DISTINCT c.ACCTNO As Acct, s.SHIPDATE, DATEDIFF(day, s.SHIPDATE,
GETDATE()) AS DUEDAYS FROM s INNER JOIN ORDERS o ON s.ORDNO = o.ORDNO INNER
JOIN CUSTOMER c ON o.ACCTNO = c.ACCTNO WHERE o.orederrep = 'Matt' AND
o.oredertype = 'Video' ORDER BY DUEDAYS DESC

I then try and loop through to create a list of acct numbers to pass to the
next page

To create the list I was doing this:

do while not rs.eof
AcctList = AcctList & trim(rs("Acct")) & ","
rs.movenext
loop

In the above case there's only one customer - #53, but I get a list like
(53,53,53,53,53,53,53)

When they click on "View" I pass AcctList to the next page to fill the
SELECT statement, Ex:

sql = "SELECT * FROM CUSTOMER WHERE ACCTNO IN (" & AcctList & ") "

(No, I'm not using SELECT * I just didn't want to type all the field names
here).

The reason I wanted to use the IN statement is because all the second page
is doing is pulling back a list of all the customer names that met the
criteria from the page before. For example, all customers that are 30 days
past due.

Does that help explain it better?

Thanks!
"Bob Barrows" <re******@NOyahoo.SPAMcom> wrote in message
news:ui**************@TK2MSFTNGP11.phx.gbl...
David Berry wrote:
Hi All. I'm trying to write an ASP page that shows me the UNIQUE
account number for a customer (so I can pass it to another page)
based on a search criteria. For example, I want to do a select
statement on the customer table and orders table (joining on the
ACCTNO) for all "Video" products.

Ex:

sq = "SELECT CUSTOMER.ACCTNO, CUSTOMER.NAME FROM CUSTOMER INNER JOIN
ORDERS ON CUSTOMER.ACCTNO = ORDERS.ACCTNO
WHERE PRODUCT_TYPE = 'Video' "

Once I get the result set back I want to get a list of the UNIQUE
account numbers I am assuming there is only one account number per customer. If I'm wrong,
this suggestion will need to be amended.

Use either GROUP BY, or the DISTINCT keyword in your query (please use

table aliases to make your queries more readable).

sq = "SELECT c.ACCTNO, c.NAME FROM CUSTOMER c INNER JOIN ORDERS oON c.ACCTNO = o.ACCTNO WHERE PRODUCT_TYPE = 'Video' GROUP BY c.ACCTNO, c.NAME "

or

sq = "SELECT DISTINCT c.ACCTNO, c.NAME FROM CUSTOMER c INNER JOIN ORDERS o
ON c.ACCTNO = o.ACCTNO WHERE PRODUCT_TYPE = 'Video' "
to pass to a SQL Statement on the next page that would have a WHERE ACCTNO IN "( & var & ") " Ex:

sql = "SELECT * FROM CUSTOMER WHERE ACCTNO IN (" & AcctList & ") "


Don't use selstar. Always specify the fields you want to retrieve so ADO
does not have to make an extra trip to the database to resolve the * into
the actual field names.

I'm not clear on the need to do this. IN (<list>) is not very efficient.

It should be avoided when there is a more efficient method. In this case, your first query already returns the list of account numbers you're interested
in. Why not just use the PRODUCT_TYPE criterion again to retrieve these new results? It should be more efficient than using IN (<list>) ...

HTH,
Bob Barrows

--
Microsoft MVP - ASP/ASP.NET
Please reply to the newsgroup. This email account is my spam trap so I
don't check it very often. If you must reply off-line, then remove the
"NO SPAM"

Jul 19 '05 #3
David Berry wrote:
Hi Bob. Thanks. I tried DISTINCT and GROUP BY but that didn't work.
Yes, there is one unique account number for each customer. See if
this makes more sense:

On the first ASP page I'm doing a select of a bunch of fields from the
CUSTOMER and ORDERS table joining on ACCTNO. Then I display a table
that show the number of order <30 Days Past Due, 30-60 Days Past Due
etc... When they click on "VIEW" (next to that column) I send the
AcctList to the second page that pulls up a list all of the customers.

So the first page might be:

<30 = 13 VIEW
30-60 = 4 VIEW
60 - 90 = 5 VIEW

The first select looks like this:

SELECT DISTINCT c.ACCTNO As Acct, s.SHIPDATE, DATEDIFF(day,
s.SHIPDATE, GETDATE()) AS DUEDAYS FROM s INNER JOIN ORDERS o ON
s.ORDNO = o.ORDNO INNER JOIN CUSTOMER c ON o.ACCTNO = c.ACCTNO WHERE
o.orederrep = 'Matt' AND o.oredertype = 'Video' ORDER BY DUEDAYS DESC

I then try and loop through to create a list of acct numbers to pass
to the next page

To create the list I was doing this:

do while not rs.eof
AcctList = AcctList & trim(rs("Acct")) & ","
rs.movenext
loop

In the above case there's only one customer - #53, but I get a list
like (53,53,53,53,53,53,53)

When they click on "View" I pass AcctList to the next page to fill the
SELECT statement, Ex:

sql = "SELECT * FROM CUSTOMER WHERE ACCTNO IN (" & AcctList & ") "
The reason I wanted to use the IN statement is because all the second
page is doing is pulling back a list of all the customer names that
met the criteria from the page before. For example, all customers
that are 30 days past due.

Does that help explain it better?

Sorry, but no. I don't understand why you don't use the ">30" (for example)
criterion to get the results for the second page.

A picture is worth a thousand words. Could you show a few rows of sample
data in tabular format for the CUSTOMERS and ORDERS tables. Just the minimum
columns to illustrate the problem. Then show the results you want to show on
each page.

Also tell us what database you are using.

--
Microsoft MVP - ASP/ASP.NET
Please reply to the newsgroup. This email account is my spam trap so I
don't check it very often. If you must reply off-line, then remove the
"NO SPAM"
Jul 19 '05 #4

This thread has been closed and replies have been disabled. Please start a new discussion.

Similar topics

4
by: August1 | last post by:
A handful of articles have been posted requesting information on how to use these functions in addition to the time() function as the seed to generate unique groups (sets) of numbers - each group...
6
by: GSpiggle | last post by:
I have a database with records that should have a consecutive number ID (Check Register). Also has other records (deposits and adjusting entries) that don't fit in the number range so...
2
by: D | last post by:
My first attempt at this and I'm searching formulas like so RIGHT(TEXT(A15,'yy'),1)*1000+A15-CONCATENATE(1,'-','jan','-',TEXT(A15,'yy'))+1 I want to extract the row / col coordinates (A15 in...
9
by: dave m | last post by:
I need to be able to retrieve a unique ID from a users PC. I needs to be something a user could not easily change, like the computer name. Could someone point me in the right direction to find ...
3
by: deaddog4201 | last post by:
How can i save a .txt file where the name of the file would be the result of the selection made from a Dom tree view's parent node tag in visual basic? So for example i have an editable tree view...
2
by: srusskinyon | last post by:
I need some help getting unique records from our database! I work for a small non-profit homeless shelter. We keep track of guest information as well as what services we have offered for...
5
by: Ramdas | last post by:
I am doing some HTML scrapping for a side project. I need a method using sgmllib or HTMLParser to parse an HTML file and get line nos of all the tags I tried a few things, but I am just not...
3
by: tkpmep | last post by:
I have coded a robust (Theil-Sen) regression routine which takes as inputs two lists of numbers, x and y, and returns a robust estimate of the slope and intercept of the best robust straight line...
0
by: DolphinDB | last post by:
Tired of spending countless mintues downsampling your data? Look no further! In this article, you’ll learn how to efficiently downsample 6.48 billion high-frequency records to 61 million...
1
isladogs
by: isladogs | last post by:
The next Access Europe meeting will be on Wednesday 6 Mar 2024 starting at 18:00 UK time (6PM UTC) and finishing at about 19:15 (7.15PM). In this month's session, we are pleased to welcome back...
0
by: Vimpel783 | last post by:
Hello! Guys, I found this code on the Internet, but I need to modify it a little. It works well, the problem is this: Data is sent from only one cell, in this case B5, but it is necessary that data...
0
by: jfyes | last post by:
As a hardware engineer, after seeing that CEIWEI recently released a new tool for Modbus RTU Over TCP/UDP filtering and monitoring, I actively went to its official website to take a look. It turned...
0
by: ArrayDB | last post by:
The error message I've encountered is; ERROR:root:Error generating model response: exception: access violation writing 0x0000000000005140, which seems to be indicative of an access violation...
1
by: PapaRatzi | last post by:
Hello, I am teaching myself MS Access forms design and Visual Basic. I've created a table to capture a list of Top 30 singles and forms to capture new entries. The final step is a form (unbound)...
1
by: CloudSolutions | last post by:
Introduction: For many beginners and individual users, requiring a credit card and email registration may pose a barrier when starting to use cloud servers. However, some cloud server providers now...
1
by: Defcon1945 | last post by:
I'm trying to learn Python using Pycharm but import shutil doesn't work
1
by: Shællîpôpï 09 | last post by:
If u are using a keypad phone, how do u turn on JavaScript, to access features like WhatsApp, Facebook, Instagram....

By using Bytes.com and it's services, you agree to our Privacy Policy and Terms of Use.

To disable or enable advertisements and analytics tracking please visit the manage ads & tracking page.