Table Fields using Billkey vs. Billnumber

[JJ Johnson]

I have a report that is returning the wrong bill number for customers. The
database table has a "BillKey" field and a "BillNumber" field. The
"BillNumber" is the correct bill number for each customer. The "BillKey"
field is incremental (by 1). However, when a manual adjustment is done and
assigns a bill number to it, it is in a differenct sequence than the regular
bill numbers and now the "BillKey" field does not match the "BillNumber"
field.
The report is returning the "BillKey" field. There are also tables that
have "CustomerKey" fields to match the "CustomerNumber" field. Because no
customers were entered with a different sequence than the norm, they are
synchronized on-to-one. Why are the "key" fields needed? How do I change
the query to pick up the correct bill number?

 

[Jeff Boyce]

I am unclear what you gain by having two fields (supposedly) containing the
same value...

It all starts with the data! How is it that your [BillKey] and
[CustomerKey] fields receive their values? You mentioned that the [*Number]
fields are "incremental" ... how do THEY receive their values?

And again, why duplicate the values?

(if the [*Key] fields hold the correct values, why not just use them?)

--
Regards

Jeff Boyce
www.InformationFutures.net

Microsoft Office/Access MVP


Microsoft IT Academy Program Mentor
http://microsoftitacademy.com/

 

[Evi]

Are these Key fields the Primary Key fields? Are they Autonumbers?
Evi