No help on this yet.. will someone help?

[Gary]

Definitions
-----------------
TABLE NAME: 'Table A'


Table A, has six records in it.
Each record consists of two fields. ID, and VALUE.
ID happens to be the primary key for table.


ID value for record one : '1'
ID value for record two : '2'
ID value for record three : '3'
ID value for record four : '4'
ID value for record five : '5'
ID value for record six : '6'


The table operates as a tally system. I need to do the following.


First Requirment.
-----------------
increment the value field for a record by 1. As an onclick event, how
would I do this. Assuming the table is called 'Table A' and I want to
increment the VALUE field of record '3' by '1'


Second Requirement.
-------------------
I need to determine the ID of the record with the lowest entry for its
VALUE field. And I need to store this ID in a varable called ID$ for
use in my code.


If someone could show me how to do the above I would be well on my way
to solving something which i've been working on for 2 weeks now.


Thanks very much,


Gary.

 

[Lynn Trapp]

Gary,
First of all, have a little patience. Your postings on this are 2 hours
apart. The people who provide help here are volunteers and can't always get
to your problem for several hours. It's best to wait 24 hours before
reposting. Second, your problem description is a bit vague. What kind of
data is stored in the VALUE field? You say, "I want to increment the VALUE
field...by '1'." Well, we need to know what the values are. Also, it would
be very helpful if you would describe what business requirement this table
will serve.

--
Lynn Trapp
MS Access MVP
www.ltcomputerdesigns.com
Access Security: www.ltcomputerdesigns.com/Security.htm
Jeff Conrad's Access Junkie List:
http://home.bendbroadband.com/conradsystems/accessjunkie.html

 

[Min]

My guess is:
First one
In the onclick event
write a SQL:
"Update Table1 Set VALUE=VALUE+1 Where ID = ?"
then excute this SQL
(You need asign a value for the ? mark.)

Second one:
Not clear what "lowest entry" mean. If it is the lowest value, may be you
can write:
"Select top 1 Value from table1order by Value"

Hope these will help. Good luck!

 

[Jon Lewis]

I assume your using a Form for data entry/viewing and your Value field in
Table A is a number (Long Integer) type. Also, you will have to delare your
ID$ variable appropriately for the scope required (i.e. where you want to
refer to it from). If you need to refer to it only from your data entry
form then at the top (declarations section) of the form:

Static ID$ As Long

First Requirement:
(One way)
On Click:

With Me.RecordSetClone
..FindFirst "[ID]= TheIDRequired" 'The exact syntax will depend on where
you're getting the ID value from
..Edit
!Value = !Value + 1
..Update
End With

'You might then need:
Me.Refresh
'or perhaps
Me.Requery

Second Requirement:
Try the following in the On Load, After Update & After Delete Confirm form
events:

ID$ = DLookup("[ID]", "TableA", "[Value] = " & DMin("[VALUE]", "TableA") )
'Assuming all the Values are unique

Hope this helps

 

[Gary]

Thankyou for your comments, i'm going to re read them slowly as i don't
quite understand them all.
I'll explain in more detail the business requirement as asked, i hope
it helps!

I'm writing a small database to track incoming leads. We are a vehicle
leasing provider.

The tracking of the leads is actually a secondary function of the
database, the main reason for this database is to ALLOCATE leads to
individual salesman. The are six salesman, and each new lead needs to
be allocated to the next salesman 'due' a lead. The next salesman that
is 'due' a lead will be the salesman who has received the least leads
on any given day.

Now, after considerable thought I think the only way i can do this is
as follows. Create a second table which keeps a tally of how many leads
each salesman has received in one day. The field i have referred to as
value is actually the tally counter, so will always be an integer - i
think i'll use the tinyint type as this is never going to be more than
255 in one day (we aren't that busy!)
So this tally counting table, will have six records, each record
belonging to one of six possible salesman. Each salesman's record will
have a 'value' field which keeps a running tally of how many leads that
salesman has received in one day.

When a new lead is added to the leads database, I want to run some code
that will query the tally database and determine the salesman with the
lowest entry in his value field (i.e. the salesman who has received the
least leads on this day.) I then want to store the ID of that record
(the saleman's name) as a string, so I can automatically associate a
salesman with the new lead (the new lead will have a salesman field
which will be filled in automatically.)

Does this clarify? am i on the right lines?

help would be greatly appreciated.

thanks,

Gary.

 

[Gary]

I tried a suggestion as follows : -

Select top 1 Value from beta by Value

value = the name of my tally field
beta = the name of my table

but it generated error, so i'm still very stumped in spite of your
generous comments.

I look forward to your continued efforts! =)

Gary

 

[Douglas J Steele]

You're missing the keyword ORDER (it's ORDER BY, not BY)

As well, it's possible that Value is a reserved word (sorry, I don't have
the list available at the moment)

Try putting square brackets around it:

Select top 1 [Value] from beta order by [Value]

 

[Guest]

Yep, Value is a reserved word for both Access and JET:

Reserved Words in Microsoft Access
http://support.microsoft.com/?id=286335

List of reserved words in Jet 4.0
http://support.microsoft.com/?id=321266


Tom

http://www.access.qbuilt.com/html/expert_contributors.html
__________________________________________

You're missing the keyword ORDER (it's ORDER BY, not BY)

As well, it's possible that Value is a reserved word (sorry, I don't have
the list available at the moment)

Try putting square brackets around it:

Select top 1 [Value] from beta order by [Value]


--
Doug Steele, Microsoft Access MVP

(no e-mails, please!)

I tried a suggestion as follows : -

Select top 1 Value from beta by Value

value = the name of my tally field
beta = the name of my table

but it generated error, so i'm still very stumped in spite of your
generous comments.

I look forward to your continued efforts! =)

Gary

 

[Gary]

that works great.
and is finding the record with the lowest 'tally' field, and giving me
the name of the person.

i tried to use this in my visual studio 2005 c# sql app and i'm stuck.

the query works ok and gives my value - but i cant find any way to run
this query from a button, in a similar way to how access would let me.
And the forums for dotnet are no where near as good as this forum.

=/

 

[Brendan Reynolds]

The two environments are so different, that you'll probably need to ask this
question in a more relevant forum. Here's a quick example that shows how to
retrive a single value from a database table using C# and ADO.NET. A
detailed discussion of this code would be way off-topic in this forum, but
it will indicate some of the objects and methods used, which may serve as a
jumping-off point.

BTW: You mention VS 2005, which is still in Beta (though I understand not
for much longer). This code has been tested in VS 2003. I expect it will
still work in VS 2005, but I have not tested it in that environment.

using System;
namespace ConsoleApplication1
{
class Class1
{
[STAThread]
static void Main(string[] args)
{
System.Data.OleDb.OleDbConnection cn = null;
string strConnection = "Provider=Microsoft.Jet.OLEDB.4.0;"
+ @"Data Source=c:\usenet\test.mdb;"
+ "Persist Security Info=False";
try
{
cn = new System.Data.OleDb.OleDbConnection(strConnection);
cn.Open();
string strSQL = "SELECT TOP 1 LastName FROM Employees "
+ "ORDER BY EmployeeID";
System.Data.OleDb.OleDbCommand cmd
= new System.Data.OleDb.OleDbCommand(strSQL, cn);
string strLastName = cmd.ExecuteScalar().ToString();
Console.WriteLine(strLastName);
cn.Close();
Console.ReadLine();
}
catch (System.Exception ex)
{
Console.WriteLine(ex.Message);
Console.ReadLine();
}
finally
{
if (cn != null)
{
if (cn.State != System.Data.ConnectionState.Closed)
{
cn.Close();
}
}
}
}
}
}