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
|
|||
|
|||
Need advice - how should I set this up?
Hello again,
The boss has me working on another database. I guess they liked the first one I created. Anyways, I'm pretty sure this one is more complicated than the last. I'm creating a database to track grant monies. Specifically, the amount of money, the projects associated with each grant, what was achieved in the project, and the payments made. Then in the end I need to able to pull reports from the data showing final figures for the work accomplished, the total project cost, and any remaining funds. There can be several projects associated with an account number. Each grant is associated with an account number. So, for each grant, there can be multiple projects. Each project is awarded a dollar amount of the grant, which is then paid in installments with matching amounts coming from the recipient, ending in a total project dollar amount. Below I have listed out what I've created so far. AccomplishmentsTbl ProjectID -- primary key Acres DS Thin FuelBreaks Slash Prun RxBurn Plans&Assessment I&E GrantInfoTbl ProjectID -- primary key Account# -- primary key Project# -- primary key FFY DistrictPriority District CongDist GrantName Request Awarded DateAgreementSigned POReceived Practice Extensions Notes WestGrantApp# BLMGrants WSFMGrantAmount GrantAdmin AmountforIndirect AdminAdded AmountofAdminforImp AmountforIndirect(%foradmin) PaymentsTbl ProjectID -- primary key Payment1 Payment1Date Payment2 Payment2Date Payment3 Payment3Date Payment4 Payment4Date Payment5 Payment5Date Match TotalProject AdditionalFundsRequested Balance RecipientsTbl ProjectID -- primary key Name StreetAddress City State Zipcode County Community Phonenumber Fax This is the current setup. I'm thinking I might need to have a table for grants and a table for projects? Not sure though. Also, all the projectID fields are linked to each other in a one-to-one relationship with the projectID field in the GrantInfoTbl. Is this correct? So, I guess I'd like to know if this looks correct. I know some of the fields near the bottom of the GrantInfoTbl won't have data in them for some of the grants and projects. Should I put those miscellaneous fields into their own table? Also, some of those fields listed are calculations, should I remove them completely? Basically I'm confused on how many tables I should be using, assigning proper primary keys, and relationships with this database. If someone could help me out it would be greatly appreciated. Thanks in advance for any and all advice. -- I've never used Access before. HELP! |
#2
|
|||
|
|||
Need advice - how should I set this up?
One thing I noticed is your payments table. You have it set up to store 5
payments. I would think this would be a one-to-many relationship where you could create one or more records for the payments. What if you get the entire money in one payment? What if you get it in 10? -- Rick B "justin" wrote in message ... Hello again, The boss has me working on another database. I guess they liked the first one I created. Anyways, I'm pretty sure this one is more complicated than the last. I'm creating a database to track grant monies. Specifically, the amount of money, the projects associated with each grant, what was achieved in the project, and the payments made. Then in the end I need to able to pull reports from the data showing final figures for the work accomplished, the total project cost, and any remaining funds. There can be several projects associated with an account number. Each grant is associated with an account number. So, for each grant, there can be multiple projects. Each project is awarded a dollar amount of the grant, which is then paid in installments with matching amounts coming from the recipient, ending in a total project dollar amount. Below I have listed out what I've created so far. AccomplishmentsTbl ProjectID -- primary key Acres DS Thin FuelBreaks Slash Prun RxBurn Plans&Assessment I&E GrantInfoTbl ProjectID -- primary key Account# -- primary key Project# -- primary key FFY DistrictPriority District CongDist GrantName Request Awarded DateAgreementSigned POReceived Practice Extensions Notes WestGrantApp# BLMGrants WSFMGrantAmount GrantAdmin AmountforIndirect AdminAdded AmountofAdminforImp AmountforIndirect(%foradmin) PaymentsTbl ProjectID -- primary key Payment1 Payment1Date Payment2 Payment2Date Payment3 Payment3Date Payment4 Payment4Date Payment5 Payment5Date Match TotalProject AdditionalFundsRequested Balance RecipientsTbl ProjectID -- primary key Name StreetAddress City State Zipcode County Community Phonenumber Fax This is the current setup. I'm thinking I might need to have a table for grants and a table for projects? Not sure though. Also, all the projectID fields are linked to each other in a one-to-one relationship with the projectID field in the GrantInfoTbl. Is this correct? So, I guess I'd like to know if this looks correct. I know some of the fields near the bottom of the GrantInfoTbl won't have data in them for some of the grants and projects. Should I put those miscellaneous fields into their own table? Also, some of those fields listed are calculations, should I remove them completely? Basically I'm confused on how many tables I should be using, assigning proper primary keys, and relationships with this database. If someone could help me out it would be greatly appreciated. Thanks in advance for any and all advice. -- I've never used Access before. HELP! |
#3
|
|||
|
|||
Need advice - how should I set this up?
We make the payments to the recipient after they've completed some of the
project. Over the course of 3 years of spreadsheets currently keeping all this information, the greatest number of payments has only been 4. what do you mean by "a one-to-many relationship where you could create one or more records for the payments." ? -- "Rick B" wrote: One thing I noticed is your payments table. You have it set up to store 5 payments. I would think this would be a one-to-many relationship where you could create one or more records for the payments. What if you get the entire money in one payment? What if you get it in 10? -- Rick B "justin" wrote in message ... Hello again, The boss has me working on another database. I guess they liked the first one I created. Anyways, I'm pretty sure this one is more complicated than the last. I'm creating a database to track grant monies. Specifically, the amount of money, the projects associated with each grant, what was achieved in the project, and the payments made. Then in the end I need to able to pull reports from the data showing final figures for the work accomplished, the total project cost, and any remaining funds. There can be several projects associated with an account number. Each grant is associated with an account number. So, for each grant, there can be multiple projects. Each project is awarded a dollar amount of the grant, which is then paid in installments with matching amounts coming from the recipient, ending in a total project dollar amount. Below I have listed out what I've created so far. AccomplishmentsTbl ProjectID -- primary key Acres DS Thin FuelBreaks Slash Prun RxBurn Plans&Assessment I&E GrantInfoTbl ProjectID -- primary key Account# -- primary key Project# -- primary key FFY DistrictPriority District CongDist GrantName Request Awarded DateAgreementSigned POReceived Practice Extensions Notes WestGrantApp# BLMGrants WSFMGrantAmount GrantAdmin AmountforIndirect AdminAdded AmountofAdminforImp AmountforIndirect(%foradmin) PaymentsTbl ProjectID -- primary key Payment1 Payment1Date Payment2 Payment2Date Payment3 Payment3Date Payment4 Payment4Date Payment5 Payment5Date Match TotalProject AdditionalFundsRequested Balance RecipientsTbl ProjectID -- primary key Name StreetAddress City State Zipcode County Community Phonenumber Fax This is the current setup. I'm thinking I might need to have a table for grants and a table for projects? Not sure though. Also, all the projectID fields are linked to each other in a one-to-one relationship with the projectID field in the GrantInfoTbl. Is this correct? So, I guess I'd like to know if this looks correct. I know some of the fields near the bottom of the GrantInfoTbl won't have data in them for some of the grants and projects. Should I put those miscellaneous fields into their own table? Also, some of those fields listed are calculations, should I remove them completely? Basically I'm confused on how many tables I should be using, assigning proper primary keys, and relationships with this database. If someone could help me out it would be greatly appreciated. Thanks in advance for any and all advice. -- I've never used Access before. HELP! |
#4
|
|||
|
|||
Need advice - how should I set this up?
justin wrote:
We make the payments to the recipient after they've completed some of the project. Over the course of 3 years of spreadsheets currently keeping all this information, the greatest number of payments has only been 4. what do you mean by "a one-to-many relationship where you could create one or more records for the payments." ? The "many" would be records in a [Payments] Table, in which each record would contain, for example, a [Date] field, [Amount] field, etc., and a [ProjectID] link (called a "foreign key") to your [PaymentsTbl] Table. Incidentally (this is a stylistic comment), even though you can give a key any valid field name, I think calling the primary key in [AccomplishmentsTbl] the same thing you call [PaymentsTbl]'s primary key is confusing. That way, any time you refer to it, you must include the name of the Table as well as the field. And I think it doesn't make it very easy to remember what that key means. (End of soapbox.) The "one" would be the (unique) record in [PaymentsTbl], which you identify via [PaymentsTbl].[ProjectID], to which all of the payments apply. Even if you have only two such payments, keeping them in a separate Table would allow you to simplify maintenance. For example, if you needed to change your [PaymentsTbl].[Payment2] field in some way, such as the way it's formatted, right now you'd also need to change [PaymentsTbl].[Payment3] and others. If you had just one such field, for example [PaymentTransactions].[Payment], you'd need to change it only once and all affected fields would be corrected at the same time. Even if you don't plan to make any changes, having fewer fields avoids wasting space, and there are fewer fields to document. For example, I almost always include an explanation, in the Table Design View's "Description" field, of what each field in a Table is supposed to contain. Access doesn't care and would work just as well if I left it blank, but it helps me to remember what is supposed to be stored there. -- Vincent Johns Please feel free to quote anything I say here. Rick B wrote: One thing I noticed is your payments table. You have it set up to store 5 payments. I would think this would be a one-to-many relationship where you could create one or more records for the payments. What if you get the entire money in one payment? What if you get it in 10? -- Rick B "justin" wrote in message ... Hello again, The boss has me working on another database. I guess they liked the first one I created. Anyways, I'm pretty sure this one is more complicated than the last. I'm creating a database to track grant monies. Specifically, the amount of money, the projects associated with each grant, what was achieved in the project, and the payments made. Then in the end I need to able to pull reports from the data showing final figures for the work accomplished, the total project cost, and any remaining funds. There can be several projects associated with an account number. Each grant is associated with an account number. So, for each grant, there can be multiple projects. Each project is awarded a dollar amount of the grant, which is then paid in installments with matching amounts coming from the recipient, ending in a total project dollar amount. Below I have listed out what I've created so far. AccomplishmentsTbl ProjectID -- primary key Acres DS Thin FuelBreaks Slash Prun RxBurn Plans&Assessment I&E GrantInfoTbl ProjectID -- primary key Account# -- primary key Project# -- primary key FFY DistrictPriority District CongDist GrantName Request Awarded DateAgreementSigned POReceived Practice Extensions Notes WestGrantApp# BLMGrants WSFMGrantAmount GrantAdmin AmountforIndirect AdminAdded AmountofAdminforImp AmountforIndirect(%foradmin) PaymentsTbl ProjectID -- primary key Payment1 Payment1Date Payment2 Payment2Date Payment3 Payment3Date Payment4 Payment4Date Payment5 Payment5Date Match TotalProject AdditionalFundsRequested Balance RecipientsTbl ProjectID -- primary key Name StreetAddress City State Zipcode County Community Phonenumber Fax This is the current setup. I'm thinking I might need to have a table for grants and a table for projects? Not sure though. Also, all the projectID fields are linked to each other in a one-to-one relationship with the projectID field in the GrantInfoTbl. Is this correct? So, I guess I'd like to know if this looks correct. I know some of the fields near the bottom of the GrantInfoTbl won't have data in them for some of the grants and projects. Should I put those miscellaneous fields into their own table? Also, some of those fields listed are calculations, should I remove them completely? Basically I'm confused on how many tables I should be using, assigning proper primary keys, and relationships with this database. If someone could help me out it would be greatly appreciated. Thanks in advance for any and all advice. -- I've never used Access before. HELP! |
#5
|
|||
|
|||
Need advice - how should I set this up?
Ok, so you're saying I can simplify my [paymentsTbl] by removing all but one
payments field, and all but one date field? Then making the projectID field a one to many relationship with the projectID field on [GrantInfoTbl]? I have another question also. Like I mentioned in my original post, each grant has many projects associated with it. So, with my current design, will I be able to enter in multiple projects under each grant without having to re-enter all the grant information each time? Or do I need a separate table for project information and a separate table for grant information? -- "Vincent Johns" wrote: The "many" would be records in a [Payments] Table, in which each record would contain, for example, a [Date] field, [Amount] field, etc., and a [ProjectID] link (called a "foreign key") to your [PaymentsTbl] Table. Incidentally (this is a stylistic comment), even though you can give a key any valid field name, I think calling the primary key in [AccomplishmentsTbl] the same thing you call [PaymentsTbl]'s primary key is confusing. That way, any time you refer to it, you must include the name of the Table as well as the field. And I think it doesn't make it very easy to remember what that key means. (End of soapbox.) The "one" would be the (unique) record in [PaymentsTbl], which you identify via [PaymentsTbl].[ProjectID], to which all of the payments apply. Even if you have only two such payments, keeping them in a separate Table would allow you to simplify maintenance. For example, if you needed to change your [PaymentsTbl].[Payment2] field in some way, such as the way it's formatted, right now you'd also need to change [PaymentsTbl].[Payment3] and others. If you had just one such field, for example [PaymentTransactions].[Payment], you'd need to change it only once and all affected fields would be corrected at the same time. Even if you don't plan to make any changes, having fewer fields avoids wasting space, and there are fewer fields to document. For example, I almost always include an explanation, in the Table Design View's "Description" field, of what each field in a Table is supposed to contain. Access doesn't care and would work just as well if I left it blank, but it helps me to remember what is supposed to be stored there. -- Vincent Johns Please feel free to quote anything I say here. Rick B wrote: One thing I noticed is your payments table. You have it set up to store 5 payments. I would think this would be a one-to-many relationship where you could create one or more records for the payments. What if you get the entire money in one payment? What if you get it in 10? -- Rick B "justin" wrote in message ... Hello again, The boss has me working on another database. I guess they liked the first one I created. Anyways, I'm pretty sure this one is more complicated than the last. I'm creating a database to track grant monies. Specifically, the amount of money, the projects associated with each grant, what was achieved in the project, and the payments made. Then in the end I need to able to pull reports from the data showing final figures for the work accomplished, the total project cost, and any remaining funds. There can be several projects associated with an account number. Each grant is associated with an account number. So, for each grant, there can be multiple projects. Each project is awarded a dollar amount of the grant, which is then paid in installments with matching amounts coming from the recipient, ending in a total project dollar amount. Below I have listed out what I've created so far. AccomplishmentsTbl ProjectID -- primary key Acres DS Thin FuelBreaks Slash Prun RxBurn Plans&Assessment I&E GrantInfoTbl ProjectID -- primary key Account# -- primary key Project# -- primary key FFY DistrictPriority District CongDist GrantName Request Awarded DateAgreementSigned POReceived Practice Extensions Notes WestGrantApp# BLMGrants WSFMGrantAmount GrantAdmin AmountforIndirect AdminAdded AmountofAdminforImp AmountforIndirect(%foradmin) PaymentsTbl ProjectID -- primary key Payment1 Payment1Date Payment2 Payment2Date Payment3 Payment3Date Payment4 Payment4Date Payment5 Payment5Date Match TotalProject AdditionalFundsRequested Balance RecipientsTbl ProjectID -- primary key Name StreetAddress City State Zipcode County Community Phonenumber Fax This is the current setup. I'm thinking I might need to have a table for grants and a table for projects? Not sure though. Also, all the projectID fields are linked to each other in a one-to-one relationship with the projectID field in the GrantInfoTbl. Is this correct? So, I guess I'd like to know if this looks correct. I know some of the fields near the bottom of the GrantInfoTbl won't have data in them for some of the grants and projects. Should I put those miscellaneous fields into their own table? Also, some of those fields listed are calculations, should I remove them completely? Basically I'm confused on how many tables I should be using, assigning proper primary keys, and relationships with this database. If someone could help me out it would be greatly appreciated. Thanks in advance for any and all advice. -- I've never used Access before. HELP! |
#6
|
|||
|
|||
Need advice - how should I set this up?
bah, I forgot to mention.
All of those projectID fields are autonumber types. Is this right, or should only one of them be autonumber and rest number type? -- I've never used Access before. HELP! "justin" wrote: Ok, so you're saying I can simplify my [paymentsTbl] by removing all but one payments field, and all but one date field? Then making the projectID field a one to many relationship with the projectID field on [GrantInfoTbl]? I have another question also. Like I mentioned in my original post, each grant has many projects associated with it. So, with my current design, will I be able to enter in multiple projects under each grant without having to re-enter all the grant information each time? Or do I need a separate table for project information and a separate table for grant information? -- "Vincent Johns" wrote: The "many" would be records in a [Payments] Table, in which each record would contain, for example, a [Date] field, [Amount] field, etc., and a [ProjectID] link (called a "foreign key") to your [PaymentsTbl] Table. Incidentally (this is a stylistic comment), even though you can give a key any valid field name, I think calling the primary key in [AccomplishmentsTbl] the same thing you call [PaymentsTbl]'s primary key is confusing. That way, any time you refer to it, you must include the name of the Table as well as the field. And I think it doesn't make it very easy to remember what that key means. (End of soapbox.) The "one" would be the (unique) record in [PaymentsTbl], which you identify via [PaymentsTbl].[ProjectID], to which all of the payments apply. Even if you have only two such payments, keeping them in a separate Table would allow you to simplify maintenance. For example, if you needed to change your [PaymentsTbl].[Payment2] field in some way, such as the way it's formatted, right now you'd also need to change [PaymentsTbl].[Payment3] and others. If you had just one such field, for example [PaymentTransactions].[Payment], you'd need to change it only once and all affected fields would be corrected at the same time. Even if you don't plan to make any changes, having fewer fields avoids wasting space, and there are fewer fields to document. For example, I almost always include an explanation, in the Table Design View's "Description" field, of what each field in a Table is supposed to contain. Access doesn't care and would work just as well if I left it blank, but it helps me to remember what is supposed to be stored there. -- Vincent Johns Please feel free to quote anything I say here. Rick B wrote: One thing I noticed is your payments table. You have it set up to store 5 payments. I would think this would be a one-to-many relationship where you could create one or more records for the payments. What if you get the entire money in one payment? What if you get it in 10? -- Rick B "justin" wrote in message ... Hello again, The boss has me working on another database. I guess they liked the first one I created. Anyways, I'm pretty sure this one is more complicated than the last. I'm creating a database to track grant monies. Specifically, the amount of money, the projects associated with each grant, what was achieved in the project, and the payments made. Then in the end I need to able to pull reports from the data showing final figures for the work accomplished, the total project cost, and any remaining funds. There can be several projects associated with an account number. Each grant is associated with an account number. So, for each grant, there can be multiple projects. Each project is awarded a dollar amount of the grant, which is then paid in installments with matching amounts coming from the recipient, ending in a total project dollar amount. Below I have listed out what I've created so far. AccomplishmentsTbl ProjectID -- primary key Acres DS Thin FuelBreaks Slash Prun RxBurn Plans&Assessment I&E GrantInfoTbl ProjectID -- primary key Account# -- primary key Project# -- primary key FFY DistrictPriority District CongDist GrantName Request Awarded DateAgreementSigned POReceived Practice Extensions Notes WestGrantApp# BLMGrants WSFMGrantAmount GrantAdmin AmountforIndirect AdminAdded AmountofAdminforImp AmountforIndirect(%foradmin) PaymentsTbl ProjectID -- primary key Payment1 Payment1Date Payment2 Payment2Date Payment3 Payment3Date Payment4 Payment4Date Payment5 Payment5Date Match TotalProject AdditionalFundsRequested Balance RecipientsTbl ProjectID -- primary key Name StreetAddress City State Zipcode County Community Phonenumber Fax This is the current setup. I'm thinking I might need to have a table for grants and a table for projects? Not sure though. Also, all the projectID fields are linked to each other in a one-to-one relationship with the projectID field in the GrantInfoTbl. Is this correct? So, I guess I'd like to know if this looks correct. I know some of the fields near the bottom of the GrantInfoTbl won't have data in them for some of the grants and projects. Should I put those miscellaneous fields into their own table? Also, some of those fields listed are calculations, should I remove them completely? Basically I'm confused on how many tables I should be using, assigning proper primary keys, and relationships with this database. If someone could help me out it would be greatly appreciated. Thanks in advance for any and all advice. -- I've never used Access before. HELP! |
#7
|
|||
|
|||
Need advice - how should I set this up?
On Thu, 20 Oct 2005 08:56:03 -0700, justin
wrote: All of those projectID fields are autonumber types. Is this right, or should only one of them be autonumber and rest number type? NONE of them should be Autonumber, except the Primary Key of the Project table. The rest should be Number... Long Integer. John W. Vinson[MVP] |
#8
|
|||
|
|||
Need advice - how should I set this up?
John Vinson wrote:
On Thu, 20 Oct 2005 08:56:03 -0700, justin wrote: All of those projectID fields are autonumber types. Is this right, or should only one of them be autonumber and rest number type? NONE of them should be Autonumber, except the Primary Key of the Project table. The rest should be Number... Long Integer. John W. Vinson[MVP] Having looked at your Table structure a bit more carefully, I agree. Most of those keys that you called "primary key" were only "foreign keys". You use a primary key to uniquely identify a record, and Autonumber type is usually a good choice. Indeed, I often make my Autonumber fields random, to avoid the appearance that they have some meaning beyond that of identifying records. You use a foreign key to refer to a record in some (usually other) Table, for example by making it a copy of that other record's primary key, and it doesn't have to be unique. I usually give a foreign key the same name that the corresponding primary key has; they are distinguished by the context. If you define a foreign key to be of Autonumber type, you'll pretty quickly see that it doesn't work, as when you try to set it to match the value of a matching record's primary key, Access will correctly tell you that you can't change it. justin wrote: Ok, so you're saying I can simplify my [paymentsTbl] by removing all but one payments field, and all but one date field? Then making the projectID field a one to many relationship with the projectID field on [GrantInfoTbl]? Yes, and if you need to distinguish between [Payment1] and [Payment3] you might also include a field to tell which one (1 or 3, for example) it is. You could link them all to a record in some other Table, possibly [GrantInfoTbl], via a key value specified by the primary key in that Table. But I'd call the primary key something else -- for example, I'd call [GrantInfoTbl]'s primary key [GrantInfoTblID], in which case that would become the reference to a [GrantInfoTbl] record. Since I don't know much about your fields besides their names, I have to guess about what they contain, so my advice is likely to be erroneous. (I can figure out some, such as [RecipientsTbl].[StreetAddress], but not [AccomplishmentsTbl].[Slash]. Descriptions would help. Of course, in many cases, I don't even need to know that the fields exist, as they don't affect the relationships of your Tables.) I have another question also. Like I mentioned in my original post, each grant has many projects associated with it. One of the first things I would have done, besides setting up a [GrantInfoTbl] Table to describe a grant, would be to define a [Projects] Table, in which each record would describe a project. You didn't mention that you have a Table like that, though you did suggest that you were considering including one. So, with my current design, will I be able to enter in multiple projects under each grant without having to re-enter all the grant information each time? Or do I need a separate table for project information and a separate table for grant information? Yes, and since you have "multiple projects under each grant", this calls for including in your [Projects] Table a [GrantInfoTblID] foreign key whose value is copied from the primary key in [GrantInfoTbl], which I would call [GrantInfoTbl].[GrantInfoTblID] and would declare as Autonumber type (random values), though any unique values will do. [...] "justin" wrote in message ... Hello again, The boss has me working on another database. I guess they liked the first one I created. Anyways, I'm pretty sure this one is more complicated than the last. I'm creating a database to track grant monies. Specifically, the amount of money, If there's just one such amount per grant, it can just be a field in [GrantInfoTbl]. If more than one, they belong in a separate Table with links to the relevant record in [GrantInfoTbl]. the projects associated with each grant, These could be represented as records in [Projects], linked to [GrantInfoTbl]. what was achieved in the project, This could be a memo field in [Projects], containing paragraphs describing the achievements; or a link to another Table containing a list of achievements, one per record; or a link to a Table with just one record per project (1:1 link; is this what [AccomplishmentsTbl] is supposed to do?); or a set of fields within [Projects] describing the achievements. and the payments made. I assume that [PaymentsTbl] contains these. Are these grants or disbursements? Anyway, if a payment is associated with a project, link each record to [Projects]; if with a grant, then link to [GrantInfoTbl]; if with an account, then link to [Accounts], etc. Then in the end I need to able to pull reports from the data showing final figures for the work accomplished, This will be a lot easier if you actually record these figures somewhere in your Tables. My guess is that they'd be in a Table with links to [AccomplishmentsTbl] or a subset thereof. Subsets might include work done by an individual or company under a specific goal number in [AccomplishmentsTbl]. Your level of detail in this would be driven by the detail required in the reports you must render. the total project cost, available IF you have tracked individual costs... and any remaining funds. There can be several projects associated with an account number. But you didn't say what an "account number" is associated with. You referred to it in [GrantInfoTbl] (you called it part of the primary key there), but I didn't see any [Accounts] Table. If one account number is attached to only one grant, I suggest keeping its field, for reporting purposes, but using an Autonumber field, [GrantInfoTblID], for reference purposes (using the [GrantInfoTblID] value as a foreign key in other Tables that refer to this record). Each grant is associated with an account number. You didn't say if each account number is associated with only one grant, as opposed to being shared by multiple grants. So, for each grant, there can be multiple projects. These could be represented by records in [Projects] linked to the [GrantInfoTbl] Table. Each project is awarded a dollar amount of the grant, which is then paid in installments These installments look like candidats for a [Receipts] Table, with each record documenting one such payment (date received, amount, project, source, etc.). with matching amounts coming from the recipient, ending in a total project dollar amount. Total amount you'd calculate by adding the installment amounts; you might not need to store it, unless for error-checking purposes or to determine how much is still owed. Below I have listed out what I've created so far. As I mentioned, I find it convenient to make the primary key for each Table be an Autonumber field (random values) with a name formed by appending "ID" to the Table's name, such as [AccomplishmentsTblID]. AccomplishmentsTbl ProjectID -- primary key Acres DS Thin FuelBreaks Slash Prun RxBurn Plans&Assessment I&E This could use a foreign key [GrantInfoTblID] or [ProjectsID] to identify the grant or project to which the accomplishment belongs. GrantInfoTbl ProjectID -- primary key Account# -- primary key Project# -- primary key FFY DistrictPriority District CongDist GrantName Request Awarded DateAgreementSigned POReceived Practice Extensions Notes WestGrantApp# BLMGrants Plural number on this name (instead of "BLMGrant") makes me suspicious that there could be more than one of these. If so, set up another Table, [BLMGrants], in which each record links to this record via this record's [GrantInfoTblID] value. (Same comment applies to any other plural-number field names.) WSFMGrantAmount GrantAdmin AmountforIndirect AdminAdded AmountofAdminforImp AmountforIndirect(%foradmin) PaymentsTbl ProjectID -- primary key Payment1 Payment1Date Payment2 Payment2Date Payment3 Payment3Date Payment4 Payment4Date Payment5 Payment5Date Match TotalProject AdditionalFundsRequested Balance The 4 fields at the end probably do not apply to individual payments, if so, they should be moved to the parent Table. [Balance], if I guess correctly as to its nature, should perhaps not be in a Table at all, but rather calculated from other stored information. RecipientsTbl ProjectID -- primary key Name StreetAddress City State Zipcode County Community Phonenumber Fax You didn't say what this Table does. If it characterizes persons to whom disbursements are made, then the records describing disbursements should contain a link to this Table. This is the current setup. I'm thinking I might need to have a table for grants and a table for projects? Not sure though. Also, all the projectID fields are linked to each other in a one-to-one relationship with the projectID field in the GrantInfoTbl. Is this correct? Whether it's "correct" depends on what you're trying to do. But I don't understand the purpose of a 1:1 link, as that's logically equivalent to just stuffing those fields into one combined Table. (It can make sense in terms of grouping fields that have related meanings and distribution.) So, I guess I'd like to know if this looks correct. I know some of the fields near the bottom of the GrantInfoTbl won't have data in them for some of the grants and projects. Should I put those miscellaneous fields into their own table? Perhaps, but not for that reason. For now, I'd set up Tables based on how their information is related. Later, I might split up Tables based on their contents, but not for a while. Also, some of those fields listed are calculations, should I remove them completely? I certainly would. Calculated values in a Table usually don't help much (unless, for example, you're using them because the calculations take too long), and they can cause headaches if you misjudge when they have to be updated. Basically I'm confused on how many tables I should be using, I think the number of Tables is far less important than their functional relationships. What do you intend each record to represent? In some cases, a human being; in others, a payment; in others, a project. assigning proper primary keys, That's pretty easy. If you're not sure, just give every Table a primary key (as I described above). Access will do this automagically for you, but I like to modify Access's suggestion by changing the type to random and including the Table's name in the field's name. and relationships with this database. This part is trickier. You set a relationship wherever the records need to be linked, such as a disbursement with a payee and with a project. The link takes the form of a field whose value (and, I suggest, whose name) duplicates that of the associated record in the other Table. If someone could help me out it would be greatly appreciated. Thanks in advance for any and all advice. -- I've never used Access before. HELP! -- Vincent Johns |
#9
|
|||
|
|||
Need advice - how should I set this up?
Ok, so after reading everyones advice/suggestions, I've changed things up a
bit, but I'm still having some troubles. Thanks for the help by the way, it helps; a lot! Anyway, here is how I have my tables set up currently. [AccomplishmentsTbl] ProjectID -- Foreign key number field linked to [ProjectInfoTbl].[ProjectID] in a 1-to-1 relationship Acres DS Thin FuelBreaks Slash Prun RxBurn Plans&Assessment I&E All of those fields are number fields except the bottom two. users enter in the amount of each, if any was completed for that project. [CommentsTbl] ID -- Foreign Key number field linked to [ProjectInfoTbl].[ProjectID] in a one to many relationship. the many being [CommentsTbl] Comments -- another Foreign Key Date Name This table is here to leave comments about the project. [GrantInfoTbl] GrantID -- Primary Key autonumber field linked to [ProjectInfoTbl].[GrantID] in an indeterminate relationship (not sure what that means exactly, or how to change it) ProjectID -- Foreign Key (Not sure if I need this here or not?) Account# Project# FFY CongDist GrantName Request Awarded DateAgreementSigned POReceived -- (might get moved to [ProjectInfoTbl] not sure on that yet) Notes WestGrantApp# BLMGrants WSFMGrantAmount GrantAdmin AmountforIndirect AdminAdded AmountofAdminforImp AmountforIndirect(%forAdmin) Some of these fields at the end are calculations (i think), so I'll probably end up deleting them. [PaymentsTbl] ProjectID -- Foreign Key number field linked to [ProjectInfoTbl].[ProjectID] in a one to many relationship. The many side being [PaymentsTbl]. Payment -- Foreign Key PaymentDate Match AdditionalFundsRequested This table tracks the payments we make to a project, as the project progresses, and the match from the recipient. This will be used to calculate totals for the reports to be made. [ProjectInfoTbl] GrantID -- Foreign Key number field linked to [GrantInfoTbl].[GrantID] in an indeterminate relationship. ProjectID -- Primary Key autonumber field linked as noted above. Project# -- Primary Key District DistrictPriority ProjectName Extensions Request Awarded Practice This table stores the info related to the individual projects. [RecipientsTbl] ProjectID -- Foreign Key number field linked to [ProjectInfoTbl].[ProjectID] in a one to one relationship Name StreetAddress City State ZipCode County Community PhoneNumber Fax This table stores the contact info. and location info. related to the project/recipient. So these are all the tables I have created thus far. I might be adding a lookup table or two in the future, but that is still undecided. I made a form, to try and capture all this for ease of data entry, but I'm running into problems when I try to input some data to see if things work correctly. Briefly, I'll explain my form setup. I created a form with [ProjectInfoTbl], then I created a tab thing, with 4 folders in it. Each folder has the following subform in it; [RecipientsTbl], [PaymentsTbl], [AccomplishmentsTbl], [CommentsTbl]. Then I saved this. I then created a new form with [GrantInfoTbl]. Within this form I inserted my [ProjectInfoTbl form] as a subform. Then Saved it. So now i have the one form with all the tables in it. Now, the problems. When I start entering data into the fields in [GrantInfoTbl] things seem to work fine. And, if I click into the subform to enter data about the project, things also seem fine. Now if I click on the first Tab page to put recipient info in, I get Error 3201; You cannot add or change a record because a related record is required in table 'AccomplishmentsTbl' and if I try to click back to something in the outer most form the [GrantsInfoTbl] part, I get the same error message. Can someone please shed some light onto this problem? Thanks. I know I had another problem, but I can't remember what it was right now. It will come up soon I imagine. Sorry this post is so long, but I tried to paint a good picture of what my database currently looks like. Again, thanks for any help in advance. -Justin -- I''ve never used Access before. HELP! "Vincent Johns" wrote: John Vinson wrote: On Thu, 20 Oct 2005 08:56:03 -0700, justin wrote: All of those projectID fields are autonumber types. Is this right, or should only one of them be autonumber and rest number type? NONE of them should be Autonumber, except the Primary Key of the Project table. The rest should be Number... Long Integer. John W. Vinson[MVP] Having looked at your Table structure a bit more carefully, I agree. Most of those keys that you called "primary key" were only "foreign keys". You use a primary key to uniquely identify a record, and Autonumber type is usually a good choice. Indeed, I often make my Autonumber fields random, to avoid the appearance that they have some meaning beyond that of identifying records. You use a foreign key to refer to a record in some (usually other) Table, for example by making it a copy of that other record's primary key, and it doesn't have to be unique. I usually give a foreign key the same name that the corresponding primary key has; they are distinguished by the context. If you define a foreign key to be of Autonumber type, you'll pretty quickly see that it doesn't work, as when you try to set it to match the value of a matching record's primary key, Access will correctly tell you that you can't change it. justin wrote: Ok, so you're saying I can simplify my [paymentsTbl] by removing all but one payments field, and all but one date field? Then making the projectID field a one to many relationship with the projectID field on [GrantInfoTbl]? Yes, and if you need to distinguish between [Payment1] and [Payment3] you might also include a field to tell which one (1 or 3, for example) it is. You could link them all to a record in some other Table, possibly [GrantInfoTbl], via a key value specified by the primary key in that Table. But I'd call the primary key something else -- for example, I'd call [GrantInfoTbl]'s primary key [GrantInfoTblID], in which case that would become the reference to a [GrantInfoTbl] record. Since I don't know much about your fields besides their names, I have to guess about what they contain, so my advice is likely to be erroneous. (I can figure out some, such as [RecipientsTbl].[StreetAddress], but not [AccomplishmentsTbl].[Slash]. Descriptions would help. Of course, in many cases, I don't even need to know that the fields exist, as they don't affect the relationships of your Tables.) I have another question also. Like I mentioned in my original post, each grant has many projects associated with it. One of the first things I would have done, besides setting up a [GrantInfoTbl] Table to describe a grant, would be to define a [Projects] Table, in which each record would describe a project. You didn't mention that you have a Table like that, though you did suggest that you were considering including one. So, with my current design, will I be able to enter in multiple projects under each grant without having to re-enter all the grant information each time? Or do I need a separate table for project information and a separate table for grant information? Yes, and since you have "multiple projects under each grant", this calls for including in your [Projects] Table a [GrantInfoTblID] foreign key whose value is copied from the primary key in [GrantInfoTbl], which I would call [GrantInfoTbl].[GrantInfoTblID] and would declare as Autonumber type (random values), though any unique values will do. [...] "justin" wrote in message ... Hello again, The boss has me working on another database. I guess they liked the first one I created. Anyways, I'm pretty sure this one is more complicated than the last. I'm creating a database to track grant monies. Specifically, the amount of money, If there's just one such amount per grant, it can just be a field in [GrantInfoTbl]. If more than one, they belong in a separate Table with links to the relevant record in [GrantInfoTbl]. the projects associated with each grant, These could be represented as records in [Projects], linked to [GrantInfoTbl]. what was achieved in the project, This could be a memo field in [Projects], containing paragraphs describing the achievements; or a link to another Table containing a list of achievements, one per record; or a link to a Table with just one record per project (1:1 link; is this what [AccomplishmentsTbl] is supposed to do?); or a set of fields within [Projects] describing the achievements. and the payments made. I assume that [PaymentsTbl] contains these. Are these grants or disbursements? Anyway, if a payment is associated with a project, link each record to [Projects]; if with a grant, then link to [GrantInfoTbl]; if with an account, then link to [Accounts], etc. Then in the end I need to able to pull reports from the data showing final figures for the work accomplished, This will be a lot easier if you actually record these figures somewhere in your Tables. My guess is that they'd be in a Table with links to [AccomplishmentsTbl] or a subset thereof. Subsets might include work done by an individual or company under a specific goal number in [AccomplishmentsTbl]. Your level of detail in this would be driven by the detail required in the reports you must render. the total project cost, available IF you have tracked individual costs... and any remaining funds. There can be several projects associated with an account number. But you didn't say what an "account number" is associated with. You referred to it in [GrantInfoTbl] (you called it part of the primary key there), but I didn't see any [Accounts] Table. If one account number is attached to only one grant, I suggest keeping its field, for reporting purposes, but using an Autonumber field, [GrantInfoTblID], for reference purposes (using the [GrantInfoTblID] value as a foreign key in other Tables that refer to this record). Each grant is associated with an account number. You didn't say if each account number is associated with only one grant, as opposed to being shared by multiple grants. So, for each grant, there can be multiple projects. These could be represented by records in [Projects] linked to the [GrantInfoTbl] Table. Each project is awarded a dollar amount of the grant, which is then paid in installments These installments look like candidats for a [Receipts] Table, with each record documenting one such payment (date received, amount, project, source, etc.). with matching amounts coming from the recipient, ending in a total project dollar amount. Total amount you'd calculate by adding the installment amounts; you might not need to store it, unless for error-checking purposes or to determine how much is still owed. Below I have listed out what I've created so far. As I mentioned, I find it convenient to make the primary key for each Table be an Autonumber field (random values) with a name formed by appending "ID" to the Table's name, such as [AccomplishmentsTblID]. AccomplishmentsTbl ProjectID -- primary key Acres DS Thin FuelBreaks Slash Prun RxBurn Plans&Assessment I&E This could use a foreign key [GrantInfoTblID] or [ProjectsID] to identify the grant or project to which the accomplishment belongs. GrantInfoTbl ProjectID -- primary key Account# -- primary key Project# -- primary key FFY DistrictPriority District CongDist GrantName Request Awarded DateAgreementSigned POReceived Practice Extensions Notes WestGrantApp# BLMGrants Plural number on this name (instead of "BLMGrant") makes me suspicious that there could be more than one of these. If so, set up another Table, [BLMGrants], in which each record links to this record via this record's [GrantInfoTblID] value. (Same comment applies to any other plural-number field names.) WSFMGrantAmount GrantAdmin AmountforIndirect AdminAdded AmountofAdminforImp AmountforIndirect(%foradmin) PaymentsTbl ProjectID -- primary key Payment1 Payment1Date Payment2 Payment2Date Payment3 Payment3Date Payment4 Payment4Date Payment5 Payment5Date Match TotalProject AdditionalFundsRequested Balance The 4 fields at the end probably do not apply to individual payments, if so, they should be moved to the parent Table. [Balance], if I guess correctly as to its nature, should perhaps not be in a Table at all, but rather calculated from other stored information. RecipientsTbl ProjectID -- primary key Name StreetAddress City State Zipcode County Community Phonenumber Fax You didn't say what this Table does. If it characterizes persons to whom disbursements are made, then the records describing disbursements should contain a link to this Table. This is the current setup. I'm thinking I might need to have a table for grants and a table for projects? Not sure though. Also, all the projectID fields are linked to each other in a one-to-one relationship with the projectID field in the GrantInfoTbl. Is this correct? Whether it's "correct" depends on what you're trying to do. But I don't understand the purpose of a 1:1 link, as that's logically equivalent to just stuffing those fields into one combined Table. (It can make sense in terms of grouping fields that have related meanings and distribution.) So, I guess I'd like to know if this looks correct. I know some of the fields near the bottom of the GrantInfoTbl won't have data in them for some of the grants and projects. Should I put those miscellaneous fields into their own table? Perhaps, but not for that reason. For now, I'd set up Tables based on how their information is related. Later, I might split up Tables based on their contents, but not for a while. Also, some of those fields listed are calculations, should I remove them completely? I certainly would. Calculated values in a Table usually don't help much (unless, for example, you're using them because the calculations take too long), and they can cause headaches if you misjudge when they have to be updated. Basically I'm confused on how many tables I should be using, I think the number of Tables is far less important than their functional |
#10
|
|||
|
|||
Need advice - how should I set this up?
On Mon, 24 Oct 2005 13:38:04 -0700, justin
wrote: Ok, so after reading everyones advice/suggestions, I've changed things up a bit, but I'm still having some troubles. Thanks for the help by the way, it helps; a lot! Anyway, here is how I have my tables set up currently. [AccomplishmentsTbl] ProjectID -- Foreign key number field linked to [ProjectInfoTbl].[ProjectID] in a 1-to-1 relationship So each Project can have exactly zero or one accomplishments? Never a second one? One to one relationships are VERY rare. Generally if it's truly a one to one, you can simply include the fields into the parent table. Acres DS Thin FuelBreaks Slash Prun RxBurn Plans&Assessment I&E And this looks like you're storing data in fieldnames. All of those fields are number fields except the bottom two. users enter in the amount of each, if any was completed for that project. A better design might be a many to many relationship. You'ld have a small table of Activities: Activities ActivityID Autonumber Primary Key Activity Text e.g. "Thin", "RxBurn" and a resolver table ProjectAccomplishments: ProjectAccomplishments ProjectID Long Integer link to Projects ActivityID Long Integer link to Activities Amount Number where you fill in the amount [CommentsTbl] ID -- Foreign Key number field linked to [ProjectInfoTbl].[ProjectID] in a one to many relationship. the many being [CommentsTbl] Comments -- another Foreign Key Foreign key to... what? Shouldn't this just be a Text (or Memo if the comments will run over 255 bytes) field containing the text of the comment? Date Name Rename these fields: both Date and Name are reserved words. This table is here to leave comments about the project. [GrantInfoTbl] GrantID -- Primary Key autonumber field linked to [ProjectInfoTbl].[GrantID] in an indeterminate relationship (not sure what that means exactly, or how to change it) You cannot ***EVER*** use an Autonumber as a foreign key. Primary key, yes - but it *SIMPLY WILL NOT WORK* as a foreign key, or the field that you link *to*. An Autonumber is uncontrollable and arbitrary. How are Grants related to Projects, in the real world? Will one Grant cover multiple projects, or will each Project use multiple grants, or both, or neither? ProjectID -- Foreign Key (Not sure if I need this here or not?) Account# Project# FFY CongDist GrantName Request Awarded DateAgreementSigned POReceived -- (might get moved to [ProjectInfoTbl] not sure on that yet) Notes WestGrantApp# BLMGrants WSFMGrantAmount GrantAdmin AmountforIndirect AdminAdded AmountofAdminforImp AmountforIndirect(%forAdmin) Some of these fields at the end are calculations (i think), so I'll probably end up deleting them. Yep. [PaymentsTbl] ProjectID -- Foreign Key number field linked to [ProjectInfoTbl].[ProjectID] in a one to many relationship. The many side being [PaymentsTbl]. Payment -- Foreign Key Again... NO. The payment is a data value, NOT a linking field and not a foreign key. Just to be clear: a foreign key is a field whose function is to provide a link to a Primary Key value in some other table. If the Primary Key in the "one" side table is Text, the foreign key must be Text; if it's Autonumber, it should be Long Integer. You're not storing *data* per se in foreign key fields, you're storing pointers to other tables. PaymentDate Match AdditionalFundsRequested This table tracks the payments we make to a project, as the project progresses, and the match from the recipient. This will be used to calculate totals for the reports to be made. [ProjectInfoTbl] GrantID -- Foreign Key number field linked to [GrantInfoTbl].[GrantID] in an indeterminate relationship. ProjectID -- Primary Key autonumber field linked as noted above. Project# -- Primary Key Stop. A table can have ONLY ONE PRIMARY KEY. If the Project# is stable (won't be edited often or ever) and can be counted on to be unique, use *it* as the Primary Key and as the foreign key in every table which needs a link; using an autonumber is necessary only if you don't have a "natural" key. I'm guessing that each Project will have a unique Project# - so that would be your natural key. District DistrictPriority ProjectName Extensions Request Awarded Practice This table stores the info related to the individual projects. [RecipientsTbl] ProjectID -- Foreign Key number field linked to [ProjectInfoTbl].[ProjectID] in a one to one relationship\ Again... you want each Project to have one, and only one, never any more, contacts? I doubt it! I think you want a one to many relationship. Name StreetAddress City State ZipCode County Community PhoneNumber Fax This table stores the contact info. and location info. related to the project/recipient. So these are all the tables I have created thus far. I might be adding a lookup table or two in the future, but that is still undecided. You'll probably want tables of Cities, States, Counties (perhaps all in one Zip table), and doubtless more. I made a form, to try and capture all this for ease of data entry, but I'm running into problems when I try to input some data to see if things work correctly. Yep. You're jumping the gun. Let's get these incorrect relationships fixed first. Briefly, I'll explain my form setup. I created a form with [ProjectInfoTbl], then I created a tab thing, with 4 folders in it. Each folder has the following subform in it; [RecipientsTbl], [PaymentsTbl], [AccomplishmentsTbl], [CommentsTbl]. Then I saved this. I then created a new form with [GrantInfoTbl]. Within this form I inserted my [ProjectInfoTbl form] as a subform. Then Saved it. So now i have the one form with all the tables in it. Now, the problems. When I start entering data into the fields in [GrantInfoTbl] things seem to work fine. And, if I click into the subform to enter data about the project, things also seem fine. Now if I click on the first Tab page to put recipient info in, I get Error 3201; You cannot add or change a record because a related record is required in table 'AccomplishmentsTbl' Right. Because you have a one to one relationship (an incorrect one I'm guessing) and haven't filled in a record in AccomplishmentsTbl. and if I try to click back to something in the outer most form the [GrantsInfoTbl] part, I get the same error message. Can someone please shed some light onto this problem? Thanks. I think the first thing to do is to get clear in your mind how relationships work. THEN start thinking about the form! John W. Vinson[MVP] |
|
Thread Tools | |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Thread Starter | Forum | Replies | Last Post |
Advice needed: Should we upgrade MS Access 2000? And if so to what? | ship | General Discussion | 43 | November 30th, 2005 10:58 PM |
Need Database Design Advice | SMBR | Database Design | 1 | October 3rd, 2005 04:56 PM |
Need advice on choosing template... | paulb104 | New Users | 1 | June 25th, 2005 09:45 PM |
Database Corruption and advice on upsizing | Robert Harris | General Discussion | 9 | March 21st, 2005 03:50 AM |
Advice on forms | Pehr Jansson | General Discussion | 1 | January 25th, 2005 04:12 AM |