Design Help

[Eric Starn]

I am wondering if anyone has some advice for me on how I might set up my DB.

I am creating a database to track assets for my company. The field entries
for the assets are pretty straight forward but, we use grants to pay for
these assets and more than one grant can pay for that one asset at a time. I
figured I can just make multiple fields to show the different grants paying
for the asset. However, I believe the problem will be when I need to query
out assets based on grants. I am not sure how to, for lack of a better word,
link those fields together so that when I do the query I get all the records.

I am not sure if there is a way to do this or if there is just a better way
to setup my database for this use.

Any ideas or suggestion will be greatly appreciated.

Eric

 

[NG]

Hi,

it looks to me you have a many to many relation: 1 grant can pay for many
assets, and 1 asset can be payed by many grants. In this case you need 3
tables like:
table Grants
table Assets
table PaysFor

in the table PaysFor you have the fields [AssetID], [GrantID], and extra
filelds like amount, date, .....

Hope this helps you on your way

 

[Fred]

You didn't say whether or not a particular grant may go for more than one
asset. If the answer is "no" then you could go with just asset and grant
tables, with an "AssetNumber field which is a PK in the asset table and a FK
in the grants table.

Since it appears you are just getting started on structure stuff, I would
just use Noella's more powerful/complex junction table structure if needed.

 

[Eric Starn]

Thanks for the help.

It seems that the many-to-many relationship might be the answer I need.
I am however having one hang up.
I created some tables to test run this layout.
First an Asset table with an [AssetID] field PK with Autonumber format.
Second a Grant table with an [GrantID] field PK, text format, then made up
some numbers for IDs
Then a PaysFor table with both [AssetID] and [GrantID] then set the
relationship between them.

When I open up the Grant table I can see all the assets for that grant but,
when I open up the Asset table and try to view the related grants I get this
message

“This expression is typed incorrectly, or it is to complex to be evaluated.â€

Any suggestions

Eric

Hi,

it looks to me you have a many to many relation: 1 grant can pay for many
assets, and 1 asset can be payed by many grants. In this case you need 3
tables like:
table Grants
table Assets
table PaysFor

in the table PaysFor you have the fields [AssetID], [GrantID], and extra
filelds like amount, date, .....

Hope this helps you on your way

--
Kind regards
Noëlla

I am wondering if anyone has some advice for me on how I might set up my DB.

I am creating a database to track assets for my company. The field entries
for the assets are pretty straight forward but, we use grants to pay for
these assets and more than one grant can pay for that one asset at a time. I
figured I can just make multiple fields to show the different grants paying
for the asset. However, I believe the problem will be when I need to query
out assets based on grants. I am not sure how to, for lack of a better word,
link those fields together so that when I do the query I get all the records.

I am not sure if there is a way to do this or if there is just a better way
to setup my database for this use.

Any ideas or suggestion will be greatly appreciated.

Eric

 

[Fred]

I'm guessing that you are a little newer at DB's, and now you are being
forced to enter the Indy 500 (with a many-to-many / junction table
structure) while still learning to drive a car. That's a tough one.

You are really going to have to use milti-tab;e queries and forms to look at
this data. From youy description, it sounds like you are trying to look at
related tables by looking at one table, possibly with those things that
Access puts in table views to try to look at related tables....not a viable
way to do yours.

Hope that helps at least a tiny bit.