Which query to use?

A

Alley

I have a database with a form to enter info. In the first field is Contract
No. When the contract is renewable, I would like to use a query that will
append (I think)new data to the existing contract by adding a letter on the
end of the contract no. (example: 2005-000 is the new contract. When it is
renewed it should change to 2005-000A)

Can you help?
 
A

Alley

Mostly it will be renewed unless info has changed. The info will be used on
some reports. This is used for one person to maintain data and generate
reports. How do I setup parent and child? Would that be the relationship
between the two tables?

Thanks
 
J

John Vinson

I have a database with a form to enter info. In the first field is Contract
No. When the contract is renewable, I would like to use a query that will
append (I think)new data to the existing contract by adding a letter on the
end of the contract no. (example: 2005-000 is the new contract. When it is
renewed it should change to 2005-000A)

Can you help?
Click to expand...

I'd really recommend a separate field: perhaps a letter, perhaps a
RenewalNumber integer. You can always concatenate them for display.
With a numeric renewal number you can very easily increment it, using
a Form and DMax() to find the largest renewal number for the current
contract.

John W. Vinson[MVP]
 
J

John Vinson

Could you assist me with this? Could you give me an example?
Click to expand...

I guess it depends on how your contracts are structured. Is there a
one-to-many relationship between Contracts and Renewals - that is, do
you keep track of some information about the contract as an entity,
and keep track of different information about each renewal? If so, two
tables - Contracts and Renewals - would be indicated. Or, do you want
to just keep the contract record, and overwrite some fields with the
renewal information?

John W. Vinson[MVP]
 
Top