DB RELATIONSHIP PROBLEM!

  • Thread starter Thread starter Guest
  • Start date Start date
G

Guest

hey guys - i am creating a inventory tracking db in ms2000.

i am going to track the location of items and then in another table/form
tell what is needed to be purchased.

thing is - some items in inventory have cat numbers and some dont.
question -

how do i match up the items which do not have cat #s to the table of items
to be ordered?

thanks guys?

auto number and then make person fill in cat number when ordering?

a pushed input?
 
how do i match up the items which do not have cat #s to the table of items
to be ordered?
Click to expand...

I have no idea; how would you do it *without* a computer?

What information do you have available about these items? How would a
cat # be assigned normally?

John W. Vinson[MVP]
 
hey john - i have a computer - cat# is the catalog number for the items.
i think that i will assign auto nnumber (indexed -no dupes) to each and then
make the user assigna cat# when prompted. thanks!
 
hey john - i have a computer - cat# is the catalog number for the items.
i think that i will assign auto nnumber (indexed -no dupes) to each and then
make the user assigna cat# when prompted. thanks!
Click to expand...

DON'T use Autonumber for this purpose.

Autonumbers have one purpose only - to provide a guaranteed (almost,
there are bugs) unique key. They will always have gaps and can become
random (if you Replicate the database for example).

I'd suggest using a Combo Box to select the item, and use the combo's
Not In List event to open a form to add a new item if the desired item
isn't on the list. The Form could have code like this in its
BeforeInsert event to implement a "roll your own" autonumber:

Private Sub Form_BeforeInsert(Cancel as Integer)
Me!Cat = NZ(DMax("[Cat]", "[ItemsTable]")) + 1
End Sub

Suggestion: if you're using # in a fieldname, don't. It's a date
delimiter and can cause problems.

John W. Vinson[MVP]
 
john! thanks so much. i will try this.
so i need'nt a unique identifier?
here is an example.

in location 2c there is a flask. the flask has no unique catalog number.

i need to catalog that flask and its location.

now - the use would go in the db via form looking for that flask from combo
box and will find its location.

the user may have to order another flask of teh same kind BUT doesnt have
the catalog number on MY database.

i need to match these up because the budget needs to be on db too.

see where i am coming from?



John Vinson said:
hey john - i have a computer - cat# is the catalog number for the items.
i think that i will assign auto nnumber (indexed -no dupes) to each and then
make the user assigna cat# when prompted. thanks!
Click to expand...

DON'T use Autonumber for this purpose.

Autonumbers have one purpose only - to provide a guaranteed (almost,
there are bugs) unique key. They will always have gaps and can become
random (if you Replicate the database for example).

I'd suggest using a Combo Box to select the item, and use the combo's
Not In List event to open a form to add a new item if the desired item
isn't on the list. The Form could have code like this in its
BeforeInsert event to implement a "roll your own" autonumber:

Private Sub Form_BeforeInsert(Cancel as Integer)
Me!Cat = NZ(DMax("[Cat]", "[ItemsTable]")) + 1
End Sub

Suggestion: if you're using # in a fieldname, don't. It's a date
delimiter and can cause problems.

John W. Vinson[MVP]
Click to expand...
 
john! thanks so much. i will try this.
so i need'nt a unique identifier?
Click to expand...

On the contrary. If you want to store information about an item and
look it up later, you DO need a unique identifier. Otherwise how can
you distinguish that item from other items?

You can use an autonumber if it's kept "under the hood" as a link to
other tables. My concern was that you should not use an autonumber in
a way which exposes its value to users.
here is an example.

in location 2c there is a flask. the flask has no unique catalog number.

i need to catalog that flask and its location.

now - the use would go in the db via form looking for that flask from combo
box and will find its location.

the user may have to order another flask of teh same kind BUT doesnt have
the catalog number on MY database.

i need to match these up because the budget needs to be on db too.

see where i am coming from?
Click to expand...

Well, you do need a unique ID. But again... how can your database
uniquely identify a 500ml Erlenmeyer flask in Room 204, as distinct
from the other (much dirtier) 500ml Erlenmeyer flask also in Room 204?
Your combo box needs to be based on SOMETHING - a table of all your
inventory, cataloged and uncataloged. Maybe the Cat# field could be
made optional, but the table must have SOME sort of unique identifier
in addition if you're going to do so. And you'll need some combination
of fields to allow the user to select *this* flask as distinct from
*this other* flask - you know better than I what that combination
might be.

John W. Vinson[MVP]
 
Back
Top