Access Table Setup Help

[Bulldog]

Let me simply explain what I'm trying to do and maybe it will make
sense. I think it's a fairly easy thing, but I've been looking at
the
DB too long. Any suggestions are welcome.


I've got an tblAsset that has a list of assets. The key field is
obviously Asset Number which will be unique to each record. What I
am
needing to do is pull up an Asset, and add assets under that one
particular assets. Example, I have a building asset which I want
fixture assets to fall under to show where those assets are. With
that said, I've created a tblContents table that simply has these
fields: id (autonumber PK), asset number, content number (the sub
asset numbers). This concept would work fine but I want to be able
to
pull description and value for the subassets on forms, and reports
but
this won't allow me. It pulls the Main asset's description and value
instead. I didn't really want to put the description and value in
the
tblContents just to avoid duplicate data in the database. Any
suggestions are greatly appreciated!!! Sorry for the long post!

 

[Jeff Boyce]

You may already be doing this...

It sounds to me like you have Assets (some bigger, some smaller), and you
have, for lack of a more appropriate expression, some Assets that "belong
to" or are "located at" other Assets. I can't tell how "deep" this goes, or
if it might be just the single top layer and a single layer underneath. If
it goes deeper, you might want to search on BOM (bill of materials)
processes/models.

If it's just the two levels, I could imagine a tblAssets and a
trelBelongsTo, looking something like:

trelBelongsTo
BelongsToID
AssetID (this is the asset that belong to another)
BelongsToAssetID (this is the asset it belongs to)

Good luck!

Regards

Jeff Boyce
Microsoft Office/Access MVP

 

[Bulldog]

You may already be doing this...

It sounds to me like you have Assets (some bigger, some smaller), and you
have, for lack of a more appropriate expression, some Assets that "belong
to" or are "located at" other Assets. I can't tell how "deep" this goes, or
if it might be just the single top layer and a single layer underneath. If
it goes deeper, you might want to search on BOM (bill of materials)
processes/models.

If it's just the two levels, I could imagine a tblAssets and a
trelBelongsTo, looking something like:

trelBelongsTo
BelongsToID
AssetID (this is the asset that belong to another)
BelongsToAssetID (this is the asset it belongs to)

Good luck!

Regards

Jeff Boyce
Microsoft Office/Access MVP







- Show quoted text -

I've got that sort of setup now. I guess I am lost to when it comes
to displaying the information on forms and/or reports. For example,
on my main form I've got the Main Asset with its information (value,
desc., etc.) and then on the subform have the secondary asset and
display its information (value, desc., etc.) I am having problems
getting that secondary information to display since its all really in
the tblAssets table. Any other advice....

 

[Jeff Boyce]

Sorry, I misunderstood. You posted about "Access Table Setup Help" in the
tablesdbdesign newsgroup, so I assumed your question was pointed at table
structure.

Again, you may have already considered this...

It sounds like you wish to have a way to associate Asset2 with Asset1. It
also sounds like you already have a place to "describe" an asset. I offered
the BelongTo table approach in case you needed to have assets related
1-to-many.

But if, in your situation, the majority of the assets you have actually
"belong to" another asset (e.g., items within a building), you might be able
to simplify the table structure to a single table, the Assets table, by
adding a BelongsToAsset field within the Assets table.

As for data entry/edit, you could use a single form based on a query on the
Assets table (to get at the descriptive items), and an added combo box that
was based on another query of the Assets table (to give a way to look up the
BelongsTo Asset).


Regards

Jeff Boyce
Microsoft Office/Access MVP

 

[Bulldog]

Sorry, I misunderstood. You posted about "Access Table Setup Help" in the
tablesdbdesign newsgroup, so I assumed your question was pointed at table
structure.

Again, you may have already considered this...

It sounds like you wish to have a way to associate Asset2 with Asset1. It
also sounds like you already have a place to "describe" an asset. I offered
the BelongTo table approach in case you needed to have assets related
1-to-many.

But if, in your situation, the majority of the assets you have actually
"belong to" another asset (e.g., items within a building), you might be able
to simplify the table structure to a single table, the Assets table, by
adding a BelongsToAsset field within the Assets table.

As for data entry/edit, you could use a single form based on a query on the
Assets table (to get at the descriptive items), and an added combo box that
was based on another query of the Assets table (to give a way to look up the
BelongsTo Asset).

Regards

Jeff Boyce
Microsoft Office/Access MVP







- Show quoted text -

Thank you for the info. One question though is if I have one table,
how will I differentiate description and value between Asset1 and
Asset2 (the sub assets)?

Thanks!

 

[Jeff Boyce]

I'm not sure what you mean by "differentiate".

If all items are Assets, and all Assets have a description and a value, the
only remaining question in my limited understanding is what relationship
(i.e., does this Asset "belong" to another, and if so, which one?)

Regards

Jeff Boyce
Microsoft Office/Access MVP