Tables

[Arnie]

if a table with two columns column A represents Column B (two different codes
same item) i can't get away from that but on my form i need to be able to
select either and show the other as well so TextBox A i want to show Column A
and Textbox B i want to show Column B i use a combobox to decide which i
want to look for but cant seem to populate the Textboxes with A and B

if this makes any sense many thanks for answers in advance

 

[Allen Browne]

So the 2 columns both represent the same thing?
And if they don't match, the data is wrong?

That's called redundancy, and it's an absolute no-no in relational data
design. You are creating more headaches for yourself than it is worth.
*Please* don't get yourself in that trap. There is a better way.

Say, for example, you have a table of products, and there are 2 codes by
which a product is uniquely identified (such as a barcode, and a supplier's
order code.) You can put the 2 fields in the Product table, with a unique
index on each one.

But now in your Orders table, you do *not* store both. You choose one field
(whichever one you chose as your primary key), and you store that one in the
order details.

In the form where you enter the order details, you can put 2 combos, both
bound to the ProductID field. The first one shows the ProductID; the second
is bound to ProductID also, but it's a hidden column so it shows the other
code. Voila! *One* stored primary key, but 2 combos where you can choose the
values. No code needed.

It keeps itself up to date. And the data can never be wrong, because you are
not storing redundant data.

 

[Arnie]

thanks unfortunately i have to keep both because one column id is known to
one company and the other id is known to a second one so when picking the id
we have to see both or use either to look up the same thing not ideal. i will
try your idea.

on another note do you know how i can generate an incremental number say
T101-00001 next number T101-00002 and so on making sure once a number has
been used it can't be re-used . i know i should post this as another question
but they don't always get an answer.

thanks for the first bit

 

[Allen Browne]

Your example looks like there are 2 pieces of information in the same field:
- whatever the T101 is,
- the following number.

That breaks another basic rule of data normalization, that the data must be
atomic, i.e. you never store 2 pieces of info in the same field.

Use 2 fields. You can then use DMax() to look up the highest number used so
far for the prefix, and add 1 to get the next available number.

I suggest you do that in the BeforeUpdate event of the form where entries
are made. That's the last possible moment before the record is saved, which
reduces the chance that 2 users will both be given the same number.