VB / SQL Question

  • Thread starter Thread starter garyusenet
  • Start date Start date
G

garyusenet

I'm using MS VB Express Ed. 2005.

I have an sql database. This database has one table, and there is a
column called salesman.

I want to AUTOMATICALLY enter the salesman name for every new record
created.

The salesman name will be taken from a list of six possible salesman in
strict order. i.e. salesman one for the first record, salesman two for
the second, etc... until record seven arises, which will be given
salesman one, ad infinitum.

The form will need to have an option to 'skip' a salesman if he is not
available, in which case the salesman ascribed to the record should
then become the next salesman from the list. However for the next
record added, it should revert back to the salesman that was
unavailable.

the whole point is i need to generate a system which allocated incoming
leads to the salesman in a fair impartial manner.

someone suggested the following sql query to me, but it makes no sense
to me - do you think this is what i'd need? and if so can you please
explain it. and how i would enter this in vb express as i have only
used ms access in the past.

===
You need to record the datetime that each salesman do their thing. Use
this
query and pick the one at the top of the list.

SELECT Table26.name AS [Up Next], Last(Table26.leadtime) AS [Last Time]

FROM Table26
GROUP BY Table26.name
ORDER BY Last(Table26.leadtime);


I do not know how you expect to skip the salesman that aren't available
unless you are going to record their not being present as a sales
activity.
=====================

Thankyou very much.

Gary.
 
Gary, if I understand you correctly, you basically need to have

Position 1 - Salesman 1 Name
Position 2 - Salemsan 2 Name
Position 3 - Salesman 3 Name
Position 4 - Salesman 4 Name
Position 5 - Salesman 5 Name
Position 6 - Salesman 6 Name
Position 1 - Salesman 1 Name 'repeat at this point.

Now, let's say that this could be 100 salesman, just replace the code below
with a call to your database table with their names (if you have index
numbers assigned, use them, oterhwise you can use the autoincrement approach
I used). Afterward, you'll have them all inserted correctly and you can
just do a straight update to the databse (note that you'll need to change
the counter to static to ensure that different instances of the form all use
the same number. Youc an created a class for this or if you're using
VB.NET, you can use that Module thingy which is essentially a class with all
static properties. Again though, how you want to make this determination on
behavior is up to you, In this case it would reset itself for each instance
of the form or each time it loaded - which would unfairly favor the guys at
the front - you could fix that by using a Random or, if I understood you
right, a static would meet your needs to handle it sequentially. HOWEVER,
you might need to keept he counter in the db if you need it to be consistent
against each instance of the app. For the same reasons that an instance
variable would unfairly favor guys at the beginning, even a static would
favor them unless you got the variable from some central place b/c let's
say that you had 5 users simultaneously. So user 1 does 2 of these, then
user 2 starts up, and does 2, then 3 does the same. Salesman 1 and 2 would
be selected three times each and the otehr guys would never get picked... if
you store the value in a db or web service (and keep it static in the web
service) then this would be alleviated - Needless to say, there's some
thought that needs to go into this, I mention it only to bring it to your
attention.

This code should do it for you though assuming you tweak the scope fo the
variables to match your business rules:

Int32 Counter = -1;
DataTable salesman = new DataTable("SalesmanLookup");
DataTable realTable = new DataTable("RealTable");

private void button4_Click(object sender, System.EventArgs e)
{
DataColumn dc = new DataColumn("ID", typeof(Int32));
dc.AutoIncrement = true;
dc.AutoIncrementSeed = 0;
DataColumn dc2 = new DataColumn("SalesmanName", typeof(String));
salesman.Columns.Add(dc);
salesman.Columns.Add(dc2);
DataRow dro = salesman.NewRow();
dro[1] = "Salesman1Name"; //Add each Salesman's real name, I just used fake
ones
salesman.Rows.Add(dro);
dro = salesman.NewRow();
dro[1] = "Salesman2Name";
salesman.Rows.Add(dro);
dro = salesman.NewRow();
dro[1] = "Salesman3Name";
salesman.Rows.Add(dro);
dro = salesman.NewRow();
dro[1] = "Salesman4Name";
salesman.Rows.Add(dro);
dro = salesman.NewRow();
dro[1] = "Salesman5Name";
salesman.Rows.Add(dro);
dro = salesman.NewRow();
dro[1] = "Salesman6Name";
salesman.Rows.Add(dro);
salesman.AcceptChanges(); //Since this is just a lookup, we don't need to
ave the values/
//However, you could extract these from a Database or other source
DataColumn dcReal = new DataColumn("SalesmanName", typeof(String));
realTable.Columns.Add(dcReal);
}
private void button5_Click(object sender, System.EventArgs e)
{
if(Counter == 5)
{
Counter = 0;
}
else
{
Counter++;
}
DataRow dro = realTable.NewRow();
dro[0] = salesman.Rows[Counter][1].ToString();
Debug.WriteLine(String.Format("Counter: {0} Salesman: {1}",
Counter.ToString(), salesman.Rows[Counter][1].ToString()));
}

I'm using MS VB Express Ed. 2005.

I have an sql database. This database has one table, and there is a
column called salesman.

I want to AUTOMATICALLY enter the salesman name for every new record
created.

The salesman name will be taken from a list of six possible salesman in
strict order. i.e. salesman one for the first record, salesman two for
the second, etc... until record seven arises, which will be given
salesman one, ad infinitum.

The form will need to have an option to 'skip' a salesman if he is not
available, in which case the salesman ascribed to the record should
then become the next salesman from the list. However for the next
record added, it should revert back to the salesman that was
unavailable.

the whole point is i need to generate a system which allocated incoming
leads to the salesman in a fair impartial manner.

someone suggested the following sql query to me, but it makes no sense
to me - do you think this is what i'd need? and if so can you please
explain it. and how i would enter this in vb express as i have only
used ms access in the past.

===
You need to record the datetime that each salesman do their thing. Use
this
query and pick the one at the top of the list.

SELECT Table26.name AS [Up Next], Last(Table26.leadtime) AS [Last Time]

FROM Table26
GROUP BY Table26.name
ORDER BY Last(Table26.leadtime);


I do not know how you expect to skip the salesman that aren't available
unless you are going to record their not being present as a sales
activity.
=====================

Thankyou very much.

Gary.
Click to expand...
 
Back
Top