If this is your first visit, be sure to check out the FAQ by clicking the link above. You may have to register before you can post: click the register link above to proceed. To start viewing messages, select the forum that you want to visit from the selection below. |
|
|
Thread Tools | Display Modes |
#1
|
|||
|
|||
Design Help
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 |
#2
|
|||
|
|||
Design Help
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 "Eric Starn" wrote: 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 |
#3
|
|||
|
|||
Design Help
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. |
#4
|
|||
|
|||
Design Help
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 "NG" wrote: 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 "Eric Starn" wrote: 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 |
#5
|
|||
|
|||
Design Help
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. |
Thread Tools | |
Display Modes | |
|
|