Which query to use?

[Guest]

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?

 

[Joseph Meehan]

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?

I can think of several ways of doing that, but which may be best will
depend on your current table design and how you will be using this
information.

I am going to guess that you have a master contract and it may be
renewed. The question is are there any changes when renewed or does it just
note the renewal?

What information do you need to pull out of this data for reports etc?

I am thinking that a child table for the renewals linked to the parent
table for the original contract. However I can conceive of all kinds of
complications depending on your business model.

 

[Guest]

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

 

[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?

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]

 

[Guest]

Could you assist me with this? Could you give me an example?

Thanks

 

[John Vinson]

Could you assist me with this? Could you give me an example?

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]