Update Query?

[whitjl143]

I have a table that has Name, Date, HaulDescription. I would like to add a
numeric field. I need the number in the numeric field to be the same when
the Name and Date are the same. For example:

John Doe 4/27/05 Zone 1 Haul
John Doe 4/27/05 Zone 3 Haul
James Bond 4/27/05 Zone 6 Haul
James Bond 4/28/05 Zone 4 Haul

I need the two John Doe records to have a number such as 100. James Bond on
4/27 would need to be 101 and James Bond 4/28 would be 102.

Is there any way to do this with a query?

 

[Ofer]

you can do it with a simple function, but I cant see a way of doing that with
a query, if you need help with the procedure, I will be happy to do so.

 

[MGFoster]

I have a table that has Name, Date, HaulDescription. I would like to add a
numeric field. I need the number in the numeric field to be the same when
the Name and Date are the same. For example:

John Doe 4/27/05 Zone 1 Haul
John Doe 4/27/05 Zone 3 Haul
James Bond 4/27/05 Zone 6 Haul
James Bond 4/28/05 Zone 4 Haul

I need the two John Doe records to have a number such as 100. James Bond on
4/27 would need to be 101 and James Bond 4/28 would be 102.

Is there any way to do this with a query?

-----BEGIN PGP SIGNED MESSAGE-----
Hash: SHA1

Not in a query. You have to properly design your DB. The modeling
solution for this is 2 tables: one w/ the Name & Date; the other with
the HaulDescription.

CREATE TABLE HaulSchedule (
HaulScheduleID COUNTER NOT NULL ,
HaulerName VARCHAR(25) NOT NULL ,
HaulDate DATE NOT NULL
CONSTRAINT PK_HaulSchedule PRIMARY KEY (HaulerName, HaulDate)
)

CREATE TABLE HaulAreas (
HaulScheduleID INTEGER NOT NULL
REFERENCES HaulSchedule (HaulScheduleID) ,
HaulDescription VARCHAR(50) NOT NULL ,
CONSTRAINT PK_HaulAreas PRIMARY KEY (HaulScheduleID, HaulDescription)
)

The COUNTER data type is an AutoNumber. Its value will be a Foreign Key
in the table HaulAreas.

You'd set up a form for the HaulSchedule and a subform for the
HaulAreas. They'd be linked by the HaulScheduleID.
--
MGFoster:::mgf00 <at> earthlink <decimal-point> net
Oakland, CA (USA)

-----BEGIN PGP SIGNATURE-----
Version: PGP for Personal Privacy 5.0
Charset: noconv

iQA/AwUBQnAQ3oechKqOuFEgEQJTgQCgsFrQUTftdwVX+SQEkiNLAZ91W7IAnA6Y
txMi1Gw83U0Knn+UyL3swh1q
=0itn
-----END PGP SIGNATURE-----

 

[whitjl143]

Yes. Your help would be greatly appreciated. Thank you so much.

 

[Ofer]

try that with your names

function aaa()
dim MyDB as database,MyRec as recordset, MyCount as long
dim MyDate as date, MyName as string
MyCount=99
set MyDb=codedb
set MyRec=MyDB.openRecordset("Select * From Mytable Order By Name Asc, Date
Asc")
While not MyRec.eof
if MyRec!name<>MyName or MyRec!Date <> MyDate then
MyCount=MyCount+1
MyName =yRec!name
MyDate = MyRec!Date
end if
MyRec.edit
MyRec!Count=MyCount
MyRec.update
MyRec.moveNext
Wend

end function

 

[John Spencer (MVP)]

It can be done in a query if you can specify your numbers based on some order of
the records. Search this group in Google for "Ranking" queries.

For instance, if your order is specified by Name and Date. Given your example
you would need to specify name in descending order and date in Ascending order.
Also, the numbers would not be completely consecutive. If I get the SAMPLE SQL
statement correct, then you would get something like the following.

SELECT T.*,
DCount("*","TableName","[Name]<""" & [Name] & """ AND [Date]>#" & [Date] &
"#") as Position
FROM TableName as T

Depending on your version of Access, you should be able to do this with a
coordinated sub-query.

SELECT T.*,
(SELECT Count(*)
FROM TableName as X
WHERE X.[Name]< T.[Name]
AND X.[Date]>T.[Date]) as Position
FROM TableName as T

 

[whitjl143]

Please forgive me... Where and how do I do a function like this?