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
|
|||
|
|||
'Lord Help Me' Project
I am SO lost. I started out on something I thought would be easy and have
dug myself into a hole of no return. I promise if I can get this program to work, I'll stick to what I know and leave the programming to the experts. I'm in charge of building and tracking our command's budget and converted an old spreadsheet that had columns out to infinity into a relational database. There are other tables that contain account code information, office/unit information etc, but the main table captures requirements, validated requirements, and amount funded. Orignally, I had three sep. columns to capture this data and had my reports, queries, subforms, etc...all working fine, but realized I had a problem when it came time to switch into the new year and start over, but still retain prior year info for historical purposes. After extensive posting on this community site I was later advised my design was flawed and the best way was to have one column called amount and then another called amount type where I would choose [rqmt, valrqmt, or funded]. So I've done this and recreated my reports, relationships, forms etc...but now I can't get a critical form to operate and I'm so down in the weeds that I don't even know where to start explaining it. My goal is to create a form (audit trail) that let's me capture funding changes that occur through out the year, thus increasing the number of records to the main table. While the amounts for Rqmt and valrqmt will not change, funding amts will change based on budget increases/decreases or reprogramming actions. So in theory, I'd like a main form that identifies the unit, and accting information, and then a subforms in datasheet view showing the requirement description along with the amount funded as a startpoint. In the subform I would then be able to add additional records by duplicating the requirement description and either increasing or decreasing the funded amount and attach a remark. Then through the awesome magic of automation, the other fields in the record would automatically populate with the fields in the main form. See that's not too much to ask, is it? I have created a query off the maintable that 'restructures' my database so I have 3 columns titled (guess??) [ rqmts], [valrqmts] and [funded]. This is my main query I use to create other queries that sum the amounts and group my info in a variety of ways. And this is where I get stuck because when I add a record that changes the fund amount, it never makes it back to the main table correctly because I never enter in the amt type. The field doesn't exist in my query and if I put it in, it messes up my groupings. So my design is still flawed (i just don't understand how) and I think I have too many tables, but I tried to keep info grouped to avoid a lot of duplication. And now I'm rambling...so if anyone out there has an urge to do a random act of kindness, I'm a willing (and somewhat able) recipient. Thanks! Laura |
#2
|
|||
|
|||
'Lord Help Me' Project
If you stuck to what you know you would not be working in your current job,
or for that matter you would never have learned to read or write. Perhaps somebody else will be able to follow your description, but I may not be the only one who is puzzled as to your exact aims. Let me suggest a few things so that the problem is clearly stated. None of us can be counted on to know the real-world situation you are trying to resolve. A good starting place is to describe that situation in non-database terms. When you do so, some explanation of terms may help. I don't know what to make of "our command's budget", for instance. Then, describe briefly how the spreadsheet handled the situation. From what I can tell it captured the necessary information, but was cumbersome. It does help to describe what you have tried. For one thing, you may be on the right track, but just need to do some tweaking. For another, people will see that you have made an effort, and are not just asking that somebody design the project for you. It can be helpful to outline the structure of tables, and to describe relationships. It is not necessary to add every field; a personnel table may look something like this: tblPersonnel PersonnelID (autonumber primary key) FirstName LastName etc. Is [funded] a running total? What are [rqmts] and [valrqmts]? These are the sorts of things you will probably need to define. I'm not sure how much I will be able to help with the project, but it may be possible to increase your chances of getting a targeted response. "laura reid" wrote in message ... I am SO lost. I started out on something I thought would be easy and have dug myself into a hole of no return. I promise if I can get this program to work, I'll stick to what I know and leave the programming to the experts. I'm in charge of building and tracking our command's budget and converted an old spreadsheet that had columns out to infinity into a relational database. There are other tables that contain account code information, office/unit information etc, but the main table captures requirements, validated requirements, and amount funded. Orignally, I had three sep. columns to capture this data and had my reports, queries, subforms, etc...all working fine, but realized I had a problem when it came time to switch into the new year and start over, but still retain prior year info for historical purposes. After extensive posting on this community site I was later advised my design was flawed and the best way was to have one column called amount and then another called amount type where I would choose [rqmt, valrqmt, or funded]. So I've done this and recreated my reports, relationships, forms etc...but now I can't get a critical form to operate and I'm so down in the weeds that I don't even know where to start explaining it. My goal is to create a form (audit trail) that let's me capture funding changes that occur through out the year, thus increasing the number of records to the main table. While the amounts for Rqmt and valrqmt will not change, funding amts will change based on budget increases/decreases or reprogramming actions. So in theory, I'd like a main form that identifies the unit, and accting information, and then a subforms in datasheet view showing the requirement description along with the amount funded as a startpoint. In the subform I would then be able to add additional records by duplicating the requirement description and either increasing or decreasing the funded amount and attach a remark. Then through the awesome magic of automation, the other fields in the record would automatically populate with the fields in the main form. See that's not too much to ask, is it? I have created a query off the maintable that 'restructures' my database so I have 3 columns titled (guess??) [ rqmts], [valrqmts] and [funded]. This is my main query I use to create other queries that sum the amounts and group my info in a variety of ways. And this is where I get stuck because when I add a record that changes the fund amount, it never makes it back to the main table correctly because I never enter in the amt type. The field doesn't exist in my query and if I put it in, it messes up my groupings. So my design is still flawed (i just don't understand how) and I think I have too many tables, but I tried to keep info grouped to avoid a lot of duplication. And now I'm rambling...so if anyone out there has an urge to do a random act of kindness, I'm a willing (and somewhat able) recipient. Thanks! Laura |
#3
|
|||
|
|||
'Lord Help Me' Project
First, thanks for helping me put this in bite size pieces. 2d, I apologize
in advance if my ‘db lingo’ is incorrect. Database Purpose: Our office (Military command) has a $99M budget that funds payroll, various contracts, and office daily operations (to include training, travel and supply expenses). This budget used to be captured on a spreadsheet that had rows for each office followed by rows for the fund category (ie contract, payroll, supply, travel) and then columns to show the initial funding startpoint amount, a remarks column, a column for funding changes (increases/decreases) and a remarks changes column, and a total column. Then with each successive year, the columns were recreated and the old ones hidden. I’ve successfully recreated this into a relational database with the following tables to capture unique information. Definitions: Requirement - $ amount the office says is required perform a certain function at 100% with bells and whistles. ValRqmt - $ amount that has been validated by leadership to say “no you can’t have have $50,000 for all your travel needs, eliminate conferences and you can have $40,000” Funded – $ amount that usually matches Val Rqmt, but not always. This is the amount that can actually be afforded, based on what we receive from our higher headquarters. Description – gives a title text description of what the requirement is buying Here are my tables & fields: Requirements (Main table that holds the ‘many’ side of the relationship) Description - txt Amount – number field Year – date RqmtRmks – txt to provide short info on the amounts entered (used for historical purposes and document decision making) ValRmks – s/a FunRmks – s/a WorkYears – number (tells how may people can be funded for payroll) Rqmts ID – PK CmdID – FK SubCmdID – FK ObjClassID – FK AcctID – FK AuditID – FK PriorityID – FK FunctionID – FK AmountID - FK Naturally each of the FKs above has their own tables (that provide the one side of the relationship) with descriptions provided below. (Note, some of these tables could/should probably be combined, but since I wasn’t sure, I just broke everything out) Command Table – contains only 3 records for the 3 commands we’re responsible for. SubCommand Table – each of the above 3 commands have separate offices (some up to 12) ObjClass Table– defines up to 12 different main funding categories, ie pay, travel, supplies etc. Acct Tble – provides the customer accounting information AuditTable – describes the change to the funded amt field. There are 6 options to choose from to document a change (baseline/increase/decrease/reprogram/unfund/transfer) PriorityTable – each requirement description is either a level 1, 2 or 3 priority Function Table – describes what mission function the requirement supports. Anount Table – 3 records only to describes if the amount is a Requirement, Validated Requirement or Funded amount. I then built a base ‘budget query’ that sums and groups with the only change being how the amount field is captured. I know Rqmt and Val Rqmt amounts will not change throughout out the year, but Funded will. So in my query I added three additional columns (one for each amount type) with the following expression: Requested: Sum(IIf([amtaction]="requested",[amount],0)) It’s this query that I use as an underlying base for additional queries/forms/reports. The only field that is missing is the date field because the date is a mm/dd/yyyy auto fill field and it messes up when I group. I really only need the date field to capture the year, not the mm/dd but I don’t know how to do this as an autofill. So I’ll stop here and ask if this design sounds acceptable? Thanks Laura "laura reid" wrote: I am SO lost. I started out on something I thought would be easy and have dug myself into a hole of no return. I promise if I can get this program to work, I'll stick to what I know and leave the programming to the experts. I'm in charge of building and tracking our command's budget and converted an old spreadsheet that had columns out to infinity into a relational database. There are other tables that contain account code information, office/unit information etc, but the main table captures requirements, validated requirements, and amount funded. Orignally, I had three sep. columns to capture this data and had my reports, queries, subforms, etc...all working fine, but realized I had a problem when it came time to switch into the new year and start over, but still retain prior year info for historical purposes. After extensive posting on this community site I was later advised my design was flawed and the best way was to have one column called amount and then another called amount type where I would choose [rqmt, valrqmt, or funded]. So I've done this and recreated my reports, relationships, forms etc...but now I can't get a critical form to operate and I'm so down in the weeds that I don't even know where to start explaining it. My goal is to create a form (audit trail) that let's me capture funding changes that occur through out the year, thus increasing the number of records to the main table. While the amounts for Rqmt and valrqmt will not change, funding amts will change based on budget increases/decreases or reprogramming actions. So in theory, I'd like a main form that identifies the unit, and accting information, and then a subforms in datasheet view showing the requirement description along with the amount funded as a startpoint. In the subform I would then be able to add additional records by duplicating the requirement description and either increasing or decreasing the funded amount and attach a remark. Then through the awesome magic of automation, the other fields in the record would automatically populate with the fields in the main form. See that's not too much to ask, is it? I have created a query off the maintable that 'restructures' my database so I have 3 columns titled (guess??) [ rqmts], [valrqmts] and [funded]. This is my main query I use to create other queries that sum the amounts and group my info in a variety of ways. And this is where I get stuck because when I add a record that changes the fund amount, it never makes it back to the main table correctly because I never enter in the amt type. The field doesn't exist in my query and if I put it in, it messes up my groupings. So my design is still flawed (i just don't understand how) and I think I have too many tables, but I tried to keep info grouped to avoid a lot of duplication. And now I'm rambling...so if anyone out there has an urge to do a random act of kindness, I'm a willing (and somewhat able) recipient. Thanks! Laura |
#4
|
|||
|
|||
'Lord Help Me' Project
If by autofill you mean default value:
format field as integer default value: Format(Date(),"yyyy") "laura reid" wrote in message ... ... The only field that is missing is the date field because the date is a mm/dd/yyyy auto fill field and it messes up when I group. I really only need the date field to capture the year, not the mm/dd but I don't know how to do this as an autofill. So I'll stop here and ask if this design sounds acceptable? Thanks Laura "laura reid" wrote: ..... Thanks! Laura |
#5
|
|||
|
|||
'Lord Help Me' Project
Laura,
There are a lot of details and considerations here. Sometimes an off-the-shelf product is the most efficient way to go. If you're in a hurry it is almost certainly the only way to go. Access is a powerful and versatile program, but its learning curve is steep. An oft-recommended book is Database design for Mere Mortals. I think the author is named Hernandez (Michael Hernandez, maybe). I suspect there are some anomalies in your design, but I doubt I have the aptitude to advise you on the details of the project, nor could I take the time even if I knew exactly how to proceed. I can point out a few things. You mention the "Main table that holds the 'many' side of the relationship". Typically the main table is on the 'one' side. For instance, the main table is the command table (I will call it tblCommand, and will use this naming convention for all tables), and is on the 'one' side of a one-to-many relationship between tblCommand and tblSubCommand. tblCommand CommandID (primary key, or PK) CommandName other fields for information specific to the command tblSubCommand SubCommandID (PK) CommandID (foreign key, or FK) SubCommandName other fields for information specific to the subcommand Each command may have several subcommands, so there is a one-to-many relationship between CommandID in tblCommand and tblSubcommand. A foreign key is established through a relationship; it is not defined in table design view as is a PK. Once you have established the relationship you can build a form (frmCommand) based on tblCommand and a subform (fsubSubCommand) based on tblSubCommand. One way to do that is to drag the icon for fsubSubCommand onto frmCommand in form design view. Click the edge of the subform control (the thing you just added to frmCommand), click View Properties, and verify that Link Parent Field and Link Child Field properties are set to CommandID. If they are not, click the three dots on the property sheet next to those properties to make the change. You can build several layers of forms in this way, but starting with just these two will give you an idea of how this works. I think you need to make a distinction between a lookup table (to choose items for a field -- tblFunction, for instance, seems to serve this purpose, if I understand correctly) and a table that may be the basis for a form (such as tblCommand). I think the way to handle the funding categories is to have a table with fields for Category, Requirement, ValRequirement, Year, and so forth; in other words, a record for each category, with fields for information relative to that category. Next year you would create new records and start the process again; you can filter so the records from last year do not appear. Let me suggest that if you are determined to do this yourself you do some reading, and try just the Command/SubCommand thing I suggested. Also, while I believe my approach is sound, I am not completely confident it is the best approach. "laura reid" wrote in message ... First, thanks for helping me put this in bite size pieces. 2d, I apologize in advance if my 'db lingo' is incorrect. Database Purpose: Our office (Military command) has a $99M budget that funds payroll, various contracts, and office daily operations (to include training, travel and supply expenses). This budget used to be captured on a spreadsheet that had rows for each office followed by rows for the fund category (ie contract, payroll, supply, travel) and then columns to show the initial funding startpoint amount, a remarks column, a column for funding changes (increases/decreases) and a remarks changes column, and a total column. Then with each successive year, the columns were recreated and the old ones hidden. I've successfully recreated this into a relational database with the following tables to capture unique information. Definitions: Requirement - $ amount the office says is required perform a certain function at 100% with bells and whistles. ValRqmt - $ amount that has been validated by leadership to say "no you can't have have $50,000 for all your travel needs, eliminate conferences and you can have $40,000" Funded - $ amount that usually matches Val Rqmt, but not always. This is the amount that can actually be afforded, based on what we receive from our higher headquarters. Description - gives a title text description of what the requirement is buying Here are my tables & fields: Requirements (Main table that holds the 'many' side of the relationship) Description - txt Amount - number field Year - date RqmtRmks - txt to provide short info on the amounts entered (used for historical purposes and document decision making) ValRmks - s/a FunRmks - s/a WorkYears - number (tells how may people can be funded for payroll) Rqmts ID - PK CmdID - FK SubCmdID - FK ObjClassID - FK AcctID - FK AuditID - FK PriorityID - FK FunctionID - FK AmountID - FK Naturally each of the FKs above has their own tables (that provide the one side of the relationship) with descriptions provided below. (Note, some of these tables could/should probably be combined, but since I wasn't sure, I just broke everything out) Command Table - contains only 3 records for the 3 commands we're responsible for. SubCommand Table - each of the above 3 commands have separate offices (some up to 12) ObjClass Table- defines up to 12 different main funding categories, ie pay, travel, supplies etc. Acct Tble - provides the customer accounting information AuditTable - describes the change to the funded amt field. There are 6 options to choose from to document a change (baseline/increase/decrease/reprogram/unfund/transfer) PriorityTable - each requirement description is either a level 1, 2 or 3 priority Function Table - describes what mission function the requirement supports. Anount Table - 3 records only to describes if the amount is a Requirement, Validated Requirement or Funded amount. I then built a base 'budget query' that sums and groups with the only change being how the amount field is captured. I know Rqmt and Val Rqmt amounts will not change throughout out the year, but Funded will. So in my query I added three additional columns (one for each amount type) with the following expression: Requested: Sum(IIf([amtaction]="requested",[amount],0)) It's this query that I use as an underlying base for additional queries/forms/reports. The only field that is missing is the date field because the date is a mm/dd/yyyy auto fill field and it messes up when I group. I really only need the date field to capture the year, not the mm/dd but I don't know how to do this as an autofill. So I'll stop here and ask if this design sounds acceptable? Thanks Laura "laura reid" wrote: I am SO lost. I started out on something I thought would be easy and have dug myself into a hole of no return. I promise if I can get this program to work, I'll stick to what I know and leave the programming to the experts. I'm in charge of building and tracking our command's budget and converted an old spreadsheet that had columns out to infinity into a relational database. There are other tables that contain account code information, office/unit information etc, but the main table captures requirements, validated requirements, and amount funded. Orignally, I had three sep. columns to capture this data and had my reports, queries, subforms, etc...all working fine, but realized I had a problem when it came time to switch into the new year and start over, but still retain prior year info for historical purposes. After extensive posting on this community site I was later advised my design was flawed and the best way was to have one column called amount and then another called amount type where I would choose [rqmt, valrqmt, or funded]. So I've done this and recreated my reports, relationships, forms etc...but now I can't get a critical form to operate and I'm so down in the weeds that I don't even know where to start explaining it. My goal is to create a form (audit trail) that let's me capture funding changes that occur through out the year, thus increasing the number of records to the main table. While the amounts for Rqmt and valrqmt will not change, funding amts will change based on budget increases/decreases or reprogramming actions. So in theory, I'd like a main form that identifies the unit, and accting information, and then a subforms in datasheet view showing the requirement description along with the amount funded as a startpoint. In the subform I would then be able to add additional records by duplicating the requirement description and either increasing or decreasing the funded amount and attach a remark. Then through the awesome magic of automation, the other fields in the record would automatically populate with the fields in the main form. See that's not too much to ask, is it? I have created a query off the maintable that 'restructures' my database so I have 3 columns titled (guess??) [ rqmts], [valrqmts] and [funded]. This is my main query I use to create other queries that sum the amounts and group my info in a variety of ways. And this is where I get stuck because when I add a record that changes the fund amount, it never makes it back to the main table correctly because I never enter in the amt type. The field doesn't exist in my query and if I put it in, it messes up my groupings. So my design is still flawed (i just don't understand how) and I think I have too many tables, but I tried to keep info grouped to avoid a lot of duplication. And now I'm rambling...so if anyone out there has an urge to do a random act of kindness, I'm a willing (and somewhat able) recipient. Thanks! Laura |
#6
|
|||
|
|||
'Lord Help Me' Project
David, thanks for the tip reference the date. This is exactly what I was
trying to do, however I was entering the expression in the wrong property. Bruce, I have come too far to quit now, and actually I'm only stuck on one piece (although big piece) of the puzzle; dealing with subforms, and even that's not quite the problem as I can get those designed so the right records are matching. The problem lies with the fact that not all the fields are filling in when I add a new record to the subform. I think this has to do with the fact that my main and subform are based off different queries vs actual tables. I will process and practice what you advise, and see if I can trouble shoot more. I agree with your idea of creating separate fields for the 3 different amt categories (rqmt, valrqmt & funded) and had originally set it up this way AND had everything working...but the date. I was advised to change this structure and that's when my problems started. Not saying the advice was wrong, just that I didn't know how to make the data give me what I wanted in that kind of structure. I'm not clear on your guidance for the relationships though. (I may have mistated in my original post). I have created the relationship of tblReqirements to the other tables through the use of foreign keys. I called this my main table because it's the table that hold each individual transaction. If you think of your checkbook, it's the "transaction description" column. All the data (fields) in this table are unique except where I've used foreign keys to tie the transaction to a certain account line, subcommand, command, program, function etc. Is that clear as mud? Appreciate your help. Laura "laura reid" wrote: First, thanks for helping me put this in bite size pieces. 2d, I apologize in advance if my ‘db lingo’ is incorrect. Database Purpose: Our office (Military command) has a $99M budget that funds payroll, various contracts, and office daily operations (to include training, travel and supply expenses). This budget used to be captured on a spreadsheet that had rows for each office followed by rows for the fund category (ie contract, payroll, supply, travel) and then columns to show the initial funding startpoint amount, a remarks column, a column for funding changes (increases/decreases) and a remarks changes column, and a total column. Then with each successive year, the columns were recreated and the old ones hidden. I’ve successfully recreated this into a relational database with the following tables to capture unique information. Definitions: Requirement - $ amount the office says is required perform a certain function at 100% with bells and whistles. ValRqmt - $ amount that has been validated by leadership to say “no you can’t have have $50,000 for all your travel needs, eliminate conferences and you can have $40,000” Funded – $ amount that usually matches Val Rqmt, but not always. This is the amount that can actually be afforded, based on what we receive from our higher headquarters. Description – gives a title text description of what the requirement is buying Here are my tables & fields: Requirements (Main table that holds the ‘many’ side of the relationship) Description - txt Amount – number field Year – date RqmtRmks – txt to provide short info on the amounts entered (used for historical purposes and document decision making) ValRmks – s/a FunRmks – s/a WorkYears – number (tells how may people can be funded for payroll) Rqmts ID – PK CmdID – FK SubCmdID – FK ObjClassID – FK AcctID – FK AuditID – FK PriorityID – FK FunctionID – FK AmountID - FK Naturally each of the FKs above has their own tables (that provide the one side of the relationship) with descriptions provided below. (Note, some of these tables could/should probably be combined, but since I wasn’t sure, I just broke everything out) Command Table – contains only 3 records for the 3 commands we’re responsible for. SubCommand Table – each of the above 3 commands have separate offices (some up to 12) ObjClass Table– defines up to 12 different main funding categories, ie pay, travel, supplies etc. Acct Tble – provides the customer accounting information AuditTable – describes the change to the funded amt field. There are 6 options to choose from to document a change (baseline/increase/decrease/reprogram/unfund/transfer) PriorityTable – each requirement description is either a level 1, 2 or 3 priority Function Table – describes what mission function the requirement supports. Anount Table – 3 records only to describes if the amount is a Requirement, Validated Requirement or Funded amount. I then built a base ‘budget query’ that sums and groups with the only change being how the amount field is captured. I know Rqmt and Val Rqmt amounts will not change throughout out the year, but Funded will. So in my query I added three additional columns (one for each amount type) with the following expression: Requested: Sum(IIf([amtaction]="requested",[amount],0)) It’s this query that I use as an underlying base for additional queries/forms/reports. The only field that is missing is the date field because the date is a mm/dd/yyyy auto fill field and it messes up when I group. I really only need the date field to capture the year, not the mm/dd but I don’t know how to do this as an autofill. So I’ll stop here and ask if this design sounds acceptable? Thanks Laura "laura reid" wrote: I am SO lost. I started out on something I thought would be easy and have dug myself into a hole of no return. I promise if I can get this program to work, I'll stick to what I know and leave the programming to the experts. I'm in charge of building and tracking our command's budget and converted an old spreadsheet that had columns out to infinity into a relational database. There are other tables that contain account code information, office/unit information etc, but the main table captures requirements, validated requirements, and amount funded. Orignally, I had three sep. columns to capture this data and had my reports, queries, subforms, etc...all working fine, but realized I had a problem when it came time to switch into the new year and start over, but still retain prior year info for historical purposes. After extensive posting on this community site I was later advised my design was flawed and the best way was to have one column called amount and then another called amount type where I would choose [rqmt, valrqmt, or funded]. So I've done this and recreated my reports, relationships, forms etc...but now I can't get a critical form to operate and I'm so down in the weeds that I don't even know where to start explaining it. My goal is to create a form (audit trail) that let's me capture funding changes that occur through out the year, thus increasing the number of records to the main table. While the amounts for Rqmt and valrqmt will not change, funding amts will change based on budget increases/decreases or reprogramming actions. So in theory, I'd like a main form that identifies the unit, and accting information, and then a subforms in datasheet view showing the requirement description along with the amount funded as a startpoint. In the subform I would then be able to add additional records by duplicating the requirement description and either increasing or decreasing the funded amount and attach a remark. Then through the awesome magic of automation, the other fields in the record would automatically populate with the fields in the main form. See that's not too much to ask, is it? I have created a query off the maintable that 'restructures' my database so I have 3 columns titled (guess??) [ rqmts], [valrqmts] and [funded]. This is my main query I use to create other queries that sum the amounts and group my info in a variety of ways. And this is where I get stuck because when I add a record that changes the fund amount, it never makes it back to the main table correctly because I never enter in the amt type. The field doesn't exist in my query and if I put it in, it messes up my groupings. So my design is still flawed (i just don't understand how) and I think I have too many tables, but I tried to keep info grouped to avoid a lot of duplication. And now I'm rambling...so if anyone out there has an urge to do a random act of kindness, I'm a willing (and somewhat able) recipient. Thanks! Laura |
#7
|
|||
|
|||
'Lord Help Me' Project
A few more thoughts inline.
"laura reid" wrote in message ... David, thanks for the tip reference the date. This is exactly what I was trying to do, however I was entering the expression in the wrong property. Bruce, I have come too far to quit now, and actually I'm only stuck on one piece (although big piece) of the puzzle; dealing with subforms, and even that's not quite the problem as I can get those designed so the right records are matching. The problem lies with the fact that not all the fields are filling in when I add a new record to the subform. I think this has to do with the fact that my main and subform are based off different queries vs actual tables. With what do you expect to fill in the fields in a new subform record? If it is with existing information, are you copying it to the subform record? In a one-to-many relationship, with a form-subform user interface, the main form and subform are based on the related tables (or queries based on those tables). I will process and practice what you advise, and see if I can trouble shoot more. I agree with your idea of creating separate fields for the 3 different amt categories (rqmt, valrqmt & funded) and had originally set it up this way AND had everything working...but the date. I was advised to change this structure and that's when my problems started. Not saying the advice was wrong, just that I didn't know how to make the data give me what I wanted in that kind of structure. I can't say that I fully understand your situation, and I certainly don't know what you were previously advised (or if that advice was based on a mistaken or incomplete understanding of your situation), but if a requirement includes a date field you can filter the records according to the date (all dates from the current year, all dates within the past twelve months, etc.). I'm not clear on your guidance for the relationships though. (I may have mistated in my original post). I have created the relationship of tblReqirements to the other tables through the use of foreign keys. I called this my main table because it's the table that hold each individual transaction. If you think of your checkbook, it's the "transaction description" column. All the data (fields) in this table are unique except where I've used foreign keys to tie the transaction to a certain account line, subcommand, command, program, function etc. To use your checkbook analogy, the line items would probably be on the many side of a relationship. Let's say you have two checking accounts. Account1 can contain or be associated with many transactions, as can Account2, but the reverse is not true: a transaction would not be associated with more than one account. When the bank sends you a statement it contains only the transactions associated with your account. Your transactions are not associated with other accounts, even though you may sometimes wish they were. One account / many transactions, but not the reverse. You may be saying that each requirement ("transaction description") may contain any number of entries (for instance, AmountReceived may arrive in several installments), in which case a related table may well be the way to handle that. . However, if AmountRequested and AmountReceived are separate records it may be difficult to perform the necessary comparisons. Is that clear as mud? Appreciate your help. Laura "laura reid" wrote: First, thanks for helping me put this in bite size pieces. 2d, I apologize in advance if my 'db lingo' is incorrect. Database Purpose: Our office (Military command) has a $99M budget that funds payroll, various contracts, and office daily operations (to include training, travel and supply expenses). This budget used to be captured on a spreadsheet that had rows for each office followed by rows for the fund category (ie contract, payroll, supply, travel) and then columns to show the initial funding startpoint amount, a remarks column, a column for funding changes (increases/decreases) and a remarks changes column, and a total column. Then with each successive year, the columns were recreated and the old ones hidden. I've successfully recreated this into a relational database with the following tables to capture unique information. Definitions: Requirement - $ amount the office says is required perform a certain function at 100% with bells and whistles. ValRqmt - $ amount that has been validated by leadership to say "no you can't have have $50,000 for all your travel needs, eliminate conferences and you can have $40,000" Funded - $ amount that usually matches Val Rqmt, but not always. This is the amount that can actually be afforded, based on what we receive from our higher headquarters. Description - gives a title text description of what the requirement is buying Here are my tables & fields: Requirements (Main table that holds the 'many' side of the relationship) Description - txt Amount - number field Year - date RqmtRmks - txt to provide short info on the amounts entered (used for historical purposes and document decision making) ValRmks - s/a FunRmks - s/a WorkYears - number (tells how may people can be funded for payroll) Rqmts ID - PK CmdID - FK SubCmdID - FK ObjClassID - FK AcctID - FK AuditID - FK PriorityID - FK FunctionID - FK AmountID - FK Naturally each of the FKs above has their own tables (that provide the one side of the relationship) with descriptions provided below. (Note, some of these tables could/should probably be combined, but since I wasn't sure, I just broke everything out) Command Table - contains only 3 records for the 3 commands we're responsible for. SubCommand Table - each of the above 3 commands have separate offices (some up to 12) ObjClass Table- defines up to 12 different main funding categories, ie pay, travel, supplies etc. Acct Tble - provides the customer accounting information AuditTable - describes the change to the funded amt field. There are 6 options to choose from to document a change (baseline/increase/decrease/reprogram/unfund/transfer) PriorityTable - each requirement description is either a level 1, 2 or 3 priority Function Table - describes what mission function the requirement supports. Anount Table - 3 records only to describes if the amount is a Requirement, Validated Requirement or Funded amount. I then built a base 'budget query' that sums and groups with the only change being how the amount field is captured. I know Rqmt and Val Rqmt amounts will not change throughout out the year, but Funded will. So in my query I added three additional columns (one for each amount type) with the following expression: Requested: Sum(IIf([amtaction]="requested",[amount],0)) It's this query that I use as an underlying base for additional queries/forms/reports. The only field that is missing is the date field because the date is a mm/dd/yyyy auto fill field and it messes up when I group. I really only need the date field to capture the year, not the mm/dd but I don't know how to do this as an autofill. So I'll stop here and ask if this design sounds acceptable? Thanks Laura "laura reid" wrote: I am SO lost. I started out on something I thought would be easy and have dug myself into a hole of no return. I promise if I can get this program to work, I'll stick to what I know and leave the programming to the experts. I'm in charge of building and tracking our command's budget and converted an old spreadsheet that had columns out to infinity into a relational database. There are other tables that contain account code information, office/unit information etc, but the main table captures requirements, validated requirements, and amount funded. Orignally, I had three sep. columns to capture this data and had my reports, queries, subforms, etc...all working fine, but realized I had a problem when it came time to switch into the new year and start over, but still retain prior year info for historical purposes. After extensive posting on this community site I was later advised my design was flawed and the best way was to have one column called amount and then another called amount type where I would choose [rqmt, valrqmt, or funded]. So I've done this and recreated my reports, relationships, forms etc...but now I can't get a critical form to operate and I'm so down in the weeds that I don't even know where to start explaining it. My goal is to create a form (audit trail) that let's me capture funding changes that occur through out the year, thus increasing the number of records to the main table. While the amounts for Rqmt and valrqmt will not change, funding amts will change based on budget increases/decreases or reprogramming actions. So in theory, I'd like a main form that identifies the unit, and accting information, and then a subforms in datasheet view showing the requirement description along with the amount funded as a startpoint. In the subform I would then be able to add additional records by duplicating the requirement description and either increasing or decreasing the funded amount and attach a remark. Then through the awesome magic of automation, the other fields in the record would automatically populate with the fields in the main form. See that's not too much to ask, is it? I have created a query off the maintable that 'restructures' my database so I have 3 columns titled (guess??) [ rqmts], [valrqmts] and [funded]. This is my main query I use to create other queries that sum the amounts and group my info in a variety of ways. And this is where I get stuck because when I add a record that changes the fund amount, it never makes it back to the main table correctly because I never enter in the amt type. The field doesn't exist in my query and if I put it in, it messes up my groupings. So my design is still flawed (i just don't understand how) and I think I have too many tables, but I tried to keep info grouped to avoid a lot of duplication. And now I'm rambling...so if anyone out there has an urge to do a random act of kindness, I'm a willing (and somewhat able) recipient. Thanks! Laura |
#8
|
|||
|
|||
'Lord Help Me' Project
Bruce,
Oh joy!!! I changed the db format to have 3 separate colums to catch the different amount types. Whether right or wrong, I have the damn thing working now the way I want and my forms work correctly (for the most part) as well. I do have a couple glitches involving the record navigation buttons and the total field on the mainform not updating whenever the totals in the subform are changed, but I'll post those issues on the forms community. Thanks for your help or I wouldn't have made it this far! Laura "BruceM" wrote: A few more thoughts inline. "laura reid" wrote in message ... David, thanks for the tip reference the date. This is exactly what I was trying to do, however I was entering the expression in the wrong property. Bruce, I have come too far to quit now, and actually I'm only stuck on one piece (although big piece) of the puzzle; dealing with subforms, and even that's not quite the problem as I can get those designed so the right records are matching. The problem lies with the fact that not all the fields are filling in when I add a new record to the subform. I think this has to do with the fact that my main and subform are based off different queries vs actual tables. With what do you expect to fill in the fields in a new subform record? If it is with existing information, are you copying it to the subform record? In a one-to-many relationship, with a form-subform user interface, the main form and subform are based on the related tables (or queries based on those tables). I will process and practice what you advise, and see if I can trouble shoot more. I agree with your idea of creating separate fields for the 3 different amt categories (rqmt, valrqmt & funded) and had originally set it up this way AND had everything working...but the date. I was advised to change this structure and that's when my problems started. Not saying the advice was wrong, just that I didn't know how to make the data give me what I wanted in that kind of structure. I can't say that I fully understand your situation, and I certainly don't know what you were previously advised (or if that advice was based on a mistaken or incomplete understanding of your situation), but if a requirement includes a date field you can filter the records according to the date (all dates from the current year, all dates within the past twelve months, etc.). I'm not clear on your guidance for the relationships though. (I may have mistated in my original post). I have created the relationship of tblReqirements to the other tables through the use of foreign keys. I called this my main table because it's the table that hold each individual transaction. If you think of your checkbook, it's the "transaction description" column. All the data (fields) in this table are unique except where I've used foreign keys to tie the transaction to a certain account line, subcommand, command, program, function etc. To use your checkbook analogy, the line items would probably be on the many side of a relationship. Let's say you have two checking accounts. Account1 can contain or be associated with many transactions, as can Account2, but the reverse is not true: a transaction would not be associated with more than one account. When the bank sends you a statement it contains only the transactions associated with your account. Your transactions are not associated with other accounts, even though you may sometimes wish they were. One account / many transactions, but not the reverse. You may be saying that each requirement ("transaction description") may contain any number of entries (for instance, AmountReceived may arrive in several installments), in which case a related table may well be the way to handle that. . However, if AmountRequested and AmountReceived are separate records it may be difficult to perform the necessary comparisons. Is that clear as mud? Appreciate your help. Laura "laura reid" wrote: First, thanks for helping me put this in bite size pieces. 2d, I apologize in advance if my 'db lingo' is incorrect. Database Purpose: Our office (Military command) has a $99M budget that funds payroll, various contracts, and office daily operations (to include training, travel and supply expenses). This budget used to be captured on a spreadsheet that had rows for each office followed by rows for the fund category (ie contract, payroll, supply, travel) and then columns to show the initial funding startpoint amount, a remarks column, a column for funding changes (increases/decreases) and a remarks changes column, and a total column. Then with each successive year, the columns were recreated and the old ones hidden. I've successfully recreated this into a relational database with the following tables to capture unique information. Definitions: Requirement - $ amount the office says is required perform a certain function at 100% with bells and whistles. ValRqmt - $ amount that has been validated by leadership to say "no you can't have have $50,000 for all your travel needs, eliminate conferences and you can have $40,000" Funded - $ amount that usually matches Val Rqmt, but not always. This is the amount that can actually be afforded, based on what we receive from our higher headquarters. Description - gives a title text description of what the requirement is buying Here are my tables & fields: Requirements (Main table that holds the 'many' side of the relationship) Description - txt Amount - number field Year - date RqmtRmks - txt to provide short info on the amounts entered (used for historical purposes and document decision making) ValRmks - s/a FunRmks - s/a WorkYears - number (tells how may people can be funded for payroll) Rqmts ID - PK CmdID - FK SubCmdID - FK ObjClassID - FK AcctID - FK AuditID - FK PriorityID - FK FunctionID - FK AmountID - FK Naturally each of the FKs above has their own tables (that provide the one side of the relationship) with descriptions provided below. (Note, some of these tables could/should probably be combined, but since I wasn't sure, I just broke everything out) Command Table - contains only 3 records for the 3 commands we're responsible for. SubCommand Table - each of the above 3 commands have separate offices (some up to 12) ObjClass Table- defines up to 12 different main funding categories, ie pay, travel, supplies etc. Acct Tble - provides the customer accounting information AuditTable - describes the change to the funded amt field. There are 6 options to choose from to document a change (baseline/increase/decrease/reprogram/unfund/transfer) PriorityTable - each requirement description is either a level 1, 2 or 3 priority Function Table - describes what mission function the requirement supports. Anount Table - 3 records only to describes if the amount is a Requirement, Validated Requirement or Funded amount. I then built a base 'budget query' that sums and groups with the only change being how the amount field is captured. I know Rqmt and Val Rqmt amounts will not change throughout out the year, but Funded will. So in my query I added three additional columns (one for each amount type) with the following expression: Requested: Sum(IIf([amtaction]="requested",[amount],0)) It's this query that I use as an underlying base for additional queries/forms/reports. The only field that is missing is the date field because the date is a mm/dd/yyyy auto fill field and it messes up when I group. I really only need the date field to capture the year, not the mm/dd but I don't know how to do this as an autofill. So I'll stop here and ask if this design sounds acceptable? Thanks Laura "laura reid" wrote: I am SO lost. I started out on something I thought would be easy and have dug myself into a hole of no return. I promise if I can get this program to work, I'll stick to what I know and leave the programming to the experts. I'm in charge of building and tracking our command's budget and converted an old spreadsheet that had columns out to infinity into a relational database. There are other tables that contain account code information, office/unit information etc, but the main table captures requirements, validated requirements, and amount funded. Orignally, I had three sep. columns to capture this data and had my reports, queries, subforms, etc...all working fine, but realized I had a problem when it came time to switch into the new year and start over, but still retain prior year info for historical purposes. After extensive posting on this community site I was later advised my design was flawed and the best way was to have one column called amount and then another called amount type where I would choose [rqmt, valrqmt, or funded]. So I've done this and recreated my reports, relationships, forms etc...but now I can't get a critical form to operate and I'm so down in the weeds that I don't even know where to start explaining it. My goal is to create a form (audit trail) that let's me capture funding changes that occur through out the year, thus increasing the number of records to the main table. While the amounts for Rqmt and valrqmt will not change, funding amts will change based on budget increases/decreases or reprogramming actions. So in theory, I'd like a main form that identifies the unit, and accting information, and then a subforms in datasheet view showing the requirement description along with the amount funded as a startpoint. In the subform I would then be able to add additional records by duplicating the requirement description and either increasing or decreasing the funded amount and attach a remark. Then through the awesome magic of automation, the other fields in the record would automatically populate with the fields in the main form. See that's not too much to ask, is it? I have created a query off the maintable that 'restructures' my database so I have 3 columns titled (guess??) [ rqmts], [valrqmts] and [funded]. This is my main query I use to create other queries that sum the amounts and group my info in a variety of ways. And this is where I get stuck because when I add a record that changes the fund amount, it never makes it back to the main table correctly because I never enter in the amt type. The field doesn't exist in my query and if I put it in, it messes up my groupings. So my design is still flawed (i just don't understand how) and I think I have too many tables, but I tried to keep info grouped to avoid a lot of duplication. And now I'm rambling...so if anyone out there has an urge to do a random act of kindness, I'm a willing (and somewhat able) recipient. Thanks! Laura |
#9
|
|||
|
|||
'Lord Help Me' Project
Glad to learn I helped, but I have to admit I don't quite see how I managed
to do that. If the total on the main form is correct if you navigate away from the record, then back to it, it may be as simple as requerying the main form. Good luck with your project. "laura reid" wrote in message ... Bruce, Oh joy!!! I changed the db format to have 3 separate colums to catch the different amount types. Whether right or wrong, I have the damn thing working now the way I want and my forms work correctly (for the most part) as well. I do have a couple glitches involving the record navigation buttons and the total field on the mainform not updating whenever the totals in the subform are changed, but I'll post those issues on the forms community. Thanks for your help or I wouldn't have made it this far! Laura "BruceM" wrote: A few more thoughts inline. "laura reid" wrote in message ... David, thanks for the tip reference the date. This is exactly what I was trying to do, however I was entering the expression in the wrong property. Bruce, I have come too far to quit now, and actually I'm only stuck on one piece (although big piece) of the puzzle; dealing with subforms, and even that's not quite the problem as I can get those designed so the right records are matching. The problem lies with the fact that not all the fields are filling in when I add a new record to the subform. I think this has to do with the fact that my main and subform are based off different queries vs actual tables. With what do you expect to fill in the fields in a new subform record? If it is with existing information, are you copying it to the subform record? In a one-to-many relationship, with a form-subform user interface, the main form and subform are based on the related tables (or queries based on those tables). I will process and practice what you advise, and see if I can trouble shoot more. I agree with your idea of creating separate fields for the 3 different amt categories (rqmt, valrqmt & funded) and had originally set it up this way AND had everything working...but the date. I was advised to change this structure and that's when my problems started. Not saying the advice was wrong, just that I didn't know how to make the data give me what I wanted in that kind of structure. I can't say that I fully understand your situation, and I certainly don't know what you were previously advised (or if that advice was based on a mistaken or incomplete understanding of your situation), but if a requirement includes a date field you can filter the records according to the date (all dates from the current year, all dates within the past twelve months, etc.). I'm not clear on your guidance for the relationships though. (I may have mistated in my original post). I have created the relationship of tblReqirements to the other tables through the use of foreign keys. I called this my main table because it's the table that hold each individual transaction. If you think of your checkbook, it's the "transaction description" column. All the data (fields) in this table are unique except where I've used foreign keys to tie the transaction to a certain account line, subcommand, command, program, function etc. To use your checkbook analogy, the line items would probably be on the many side of a relationship. Let's say you have two checking accounts. Account1 can contain or be associated with many transactions, as can Account2, but the reverse is not true: a transaction would not be associated with more than one account. When the bank sends you a statement it contains only the transactions associated with your account. Your transactions are not associated with other accounts, even though you may sometimes wish they were. One account / many transactions, but not the reverse. You may be saying that each requirement ("transaction description") may contain any number of entries (for instance, AmountReceived may arrive in several installments), in which case a related table may well be the way to handle that. . However, if AmountRequested and AmountReceived are separate records it may be difficult to perform the necessary comparisons. Is that clear as mud? Appreciate your help. Laura "laura reid" wrote: First, thanks for helping me put this in bite size pieces. 2d, I apologize in advance if my 'db lingo' is incorrect. Database Purpose: Our office (Military command) has a $99M budget that funds payroll, various contracts, and office daily operations (to include training, travel and supply expenses). This budget used to be captured on a spreadsheet that had rows for each office followed by rows for the fund category (ie contract, payroll, supply, travel) and then columns to show the initial funding startpoint amount, a remarks column, a column for funding changes (increases/decreases) and a remarks changes column, and a total column. Then with each successive year, the columns were recreated and the old ones hidden. I've successfully recreated this into a relational database with the following tables to capture unique information. Definitions: Requirement - $ amount the office says is required perform a certain function at 100% with bells and whistles. ValRqmt - $ amount that has been validated by leadership to say "no you can't have have $50,000 for all your travel needs, eliminate conferences and you can have $40,000" Funded - $ amount that usually matches Val Rqmt, but not always. This is the amount that can actually be afforded, based on what we receive from our higher headquarters. Description - gives a title text description of what the requirement is buying Here are my tables & fields: Requirements (Main table that holds the 'many' side of the relationship) Description - txt Amount - number field Year - date RqmtRmks - txt to provide short info on the amounts entered (used for historical purposes and document decision making) ValRmks - s/a FunRmks - s/a WorkYears - number (tells how may people can be funded for payroll) Rqmts ID - PK CmdID - FK SubCmdID - FK ObjClassID - FK AcctID - FK AuditID - FK PriorityID - FK FunctionID - FK AmountID - FK Naturally each of the FKs above has their own tables (that provide the one side of the relationship) with descriptions provided below. (Note, some of these tables could/should probably be combined, but since I wasn't sure, I just broke everything out) Command Table - contains only 3 records for the 3 commands we're responsible for. SubCommand Table - each of the above 3 commands have separate offices (some up to 12) ObjClass Table- defines up to 12 different main funding categories, ie pay, travel, supplies etc. Acct Tble - provides the customer accounting information AuditTable - describes the change to the funded amt field. There are 6 options to choose from to document a change (baseline/increase/decrease/reprogram/unfund/transfer) PriorityTable - each requirement description is either a level 1, 2 or 3 priority Function Table - describes what mission function the requirement supports. Anount Table - 3 records only to describes if the amount is a Requirement, Validated Requirement or Funded amount. I then built a base 'budget query' that sums and groups with the only change being how the amount field is captured. I know Rqmt and Val Rqmt amounts will not change throughout out the year, but Funded will. So in my query I added three additional columns (one for each amount type) with the following expression: Requested: Sum(IIf([amtaction]="requested",[amount],0)) It's this query that I use as an underlying base for additional queries/forms/reports. The only field that is missing is the date field because the date is a mm/dd/yyyy auto fill field and it messes up when I group. I really only need the date field to capture the year, not the mm/dd but I don't know how to do this as an autofill. So I'll stop here and ask if this design sounds acceptable? Thanks Laura "laura reid" wrote: I am SO lost. I started out on something I thought would be easy and have dug myself into a hole of no return. I promise if I can get this program to work, I'll stick to what I know and leave the programming to the experts. I'm in charge of building and tracking our command's budget and converted an old spreadsheet that had columns out to infinity into a relational database. There are other tables that contain account code information, office/unit information etc, but the main table captures requirements, validated requirements, and amount funded. Orignally, I had three sep. columns to capture this data and had my reports, queries, subforms, etc...all working fine, but realized I had a problem when it came time to switch into the new year and start over, but still retain prior year info for historical purposes. After extensive posting on this community site I was later advised my design was flawed and the best way was to have one column called amount and then another called amount type where I would choose [rqmt, valrqmt, or funded]. So I've done this and recreated my reports, relationships, forms etc...but now I can't get a critical form to operate and I'm so down in the weeds that I don't even know where to start explaining it. My goal is to create a form (audit trail) that let's me capture funding changes that occur through out the year, thus increasing the number of records to the main table. While the amounts for Rqmt and valrqmt will not change, funding amts will change based on budget increases/decreases or reprogramming actions. So in theory, I'd like a main form that identifies the unit, and accting information, and then a subforms in datasheet view showing the requirement description along with the amount funded as a startpoint. In the subform I would then be able to add additional records by duplicating the requirement description and either increasing or decreasing the funded amount and attach a remark. Then through the awesome magic of automation, the other fields in the record would automatically populate with the fields in the main form. See that's not too much to ask, is it? I have created a query off the maintable that 'restructures' my database so I have 3 columns titled (guess??) [ rqmts], [valrqmts] and [funded]. This is my main query I use to create other queries that sum the amounts and group my info in a variety of ways. And this is where I get stuck because when I add a record that changes the fund amount, it never makes it back to the main table correctly because I never enter in the amt type. The field doesn't exist in my query and if I put it in, it messes up my groupings. So my design is still flawed (i just don't understand how) and I think I have too many tables, but I tried to keep info grouped to avoid a lot of duplication. And now I'm rambling...so if anyone out there has an urge to do a random act of kindness, I'm a willing (and somewhat able) recipient. Thanks! Laura |
#10
|
|||
|
|||
'Lord Help Me' Project
I'm very excited about the program now from when I first posted. I'm sure
I'm not doing everything as efficiently as I should be, but I'm proud of what I've accomplished so far. And you're to be congratulated because I know I have problems getting my brain to coherently 'output' what its thinking and somehow in all that fuzzy logic, we intuited what the other meant. scarey, huh! Yes, requery is the way I went. However since the user is never permitted to change any fields in the main form, I ended up placeing a button on the main form footer and after all the changes in the various subforms are made, the user then presses the button to get a new total. The fields in the main form only serve as account identification information, Go forth and aid other helpless souls! Thanks again Laura "BruceM" wrote: Glad to learn I helped, but I have to admit I don't quite see how I managed to do that. If the total on the main form is correct if you navigate away from the record, then back to it, it may be as simple as requerying the main form. Good luck with your project. "laura reid" wrote in message ... Bruce, Oh joy!!! I changed the db format to have 3 separate colums to catch the different amount types. Whether right or wrong, I have the damn thing working now the way I want and my forms work correctly (for the most part) as well. I do have a couple glitches involving the record navigation buttons and the total field on the mainform not updating whenever the totals in the subform are changed, but I'll post those issues on the forms community. Thanks for your help or I wouldn't have made it this far! Laura "BruceM" wrote: A few more thoughts inline. "laura reid" wrote in message ... David, thanks for the tip reference the date. This is exactly what I was trying to do, however I was entering the expression in the wrong property. Bruce, I have come too far to quit now, and actually I'm only stuck on one piece (although big piece) of the puzzle; dealing with subforms, and even that's not quite the problem as I can get those designed so the right records are matching. The problem lies with the fact that not all the fields are filling in when I add a new record to the subform. I think this has to do with the fact that my main and subform are based off different queries vs actual tables. With what do you expect to fill in the fields in a new subform record? If it is with existing information, are you copying it to the subform record? In a one-to-many relationship, with a form-subform user interface, the main form and subform are based on the related tables (or queries based on those tables). I will process and practice what you advise, and see if I can trouble shoot more. I agree with your idea of creating separate fields for the 3 different amt categories (rqmt, valrqmt & funded) and had originally set it up this way AND had everything working...but the date. I was advised to change this structure and that's when my problems started. Not saying the advice was wrong, just that I didn't know how to make the data give me what I wanted in that kind of structure. I can't say that I fully understand your situation, and I certainly don't know what you were previously advised (or if that advice was based on a mistaken or incomplete understanding of your situation), but if a requirement includes a date field you can filter the records according to the date (all dates from the current year, all dates within the past twelve months, etc.). I'm not clear on your guidance for the relationships though. (I may have mistated in my original post). I have created the relationship of tblReqirements to the other tables through the use of foreign keys. I called this my main table because it's the table that hold each individual transaction. If you think of your checkbook, it's the "transaction description" column. All the data (fields) in this table are unique except where I've used foreign keys to tie the transaction to a certain account line, subcommand, command, program, function etc. To use your checkbook analogy, the line items would probably be on the many side of a relationship. Let's say you have two checking accounts. Account1 can contain or be associated with many transactions, as can Account2, but the reverse is not true: a transaction would not be associated with more than one account. When the bank sends you a statement it contains only the transactions associated with your account. Your transactions are not associated with other accounts, even though you may sometimes wish they were. One account / many transactions, but not the reverse. You may be saying that each requirement ("transaction description") may contain any number of entries (for instance, AmountReceived may arrive in several installments), in which case a related table may well be the way to handle that. . However, if AmountRequested and AmountReceived are separate records it may be difficult to perform the necessary comparisons. Is that clear as mud? Appreciate your help. Laura "laura reid" wrote: First, thanks for helping me put this in bite size pieces. 2d, I apologize in advance if my 'db lingo' is incorrect. Database Purpose: Our office (Military command) has a $99M budget that funds payroll, various contracts, and office daily operations (to include training, travel and supply expenses). This budget used to be captured on a spreadsheet that had rows for each office followed by rows for the fund category (ie contract, payroll, supply, travel) and then columns to show the initial funding startpoint amount, a remarks column, a column for funding changes (increases/decreases) and a remarks changes column, and a total column. Then with each successive year, the columns were recreated and the old ones hidden. I've successfully recreated this into a relational database with the following tables to capture unique information. Definitions: Requirement - $ amount the office says is required perform a certain function at 100% with bells and whistles. ValRqmt - $ amount that has been validated by leadership to say "no you can't have have $50,000 for all your travel needs, eliminate conferences and you can have $40,000" Funded - $ amount that usually matches Val Rqmt, but not always. This is the amount that can actually be afforded, based on what we receive from our higher headquarters. Description - gives a title text description of what the requirement is buying Here are my tables & fields: Requirements (Main table that holds the 'many' side of the relationship) Description - txt Amount - number field Year - date RqmtRmks - txt to provide short info on the amounts entered (used for historical purposes and document decision making) ValRmks - s/a FunRmks - s/a WorkYears - number (tells how may people can be funded for payroll) Rqmts ID - PK CmdID - FK SubCmdID - FK ObjClassID - FK AcctID - FK AuditID - FK PriorityID - FK FunctionID - FK AmountID - FK Naturally each of the FKs above has their own tables (that provide the one side of the relationship) with descriptions provided below. (Note, some of these tables could/should probably be combined, but since I wasn't sure, I just broke everything out) Command Table - contains only 3 records for the 3 commands we're responsible for. SubCommand Table - each of the above 3 commands have separate offices (some up to 12) ObjClass Table- defines up to 12 different main funding categories, ie pay, travel, supplies etc. Acct Tble - provides the customer accounting information AuditTable - describes the change to the funded amt field. There are 6 options to choose from to document a change (baseline/increase/decrease/reprogram/unfund/transfer) PriorityTable - each requirement description is either a level 1, 2 or 3 priority Function Table - describes what mission function the requirement supports. Anount Table - 3 records only to describes if the amount is a Requirement, Validated Requirement or Funded amount. I then built a base 'budget query' that sums and groups with the only change being how the amount field is captured. I know Rqmt and Val Rqmt amounts will not change throughout out the year, but Funded will. So in my query I added three additional columns (one for each amount type) with the following expression: Requested: Sum(IIf([amtaction]="requested",[amount],0)) It's this query that I use as an underlying base for additional queries/forms/reports. The only field that is missing is the date field because the date is a mm/dd/yyyy auto fill field and it messes up when I group. I really only need the date field to capture the year, not the mm/dd but I don't know how to do this as an autofill. So I'll stop here and ask if this design sounds acceptable? Thanks Laura "laura reid" wrote: I am SO lost. I started out on something I thought would be easy and have dug myself into a hole of no return. I promise if I can get this program to work, I'll stick to what I know and leave the programming to the experts. I'm in charge of building and tracking our command's budget and converted an old spreadsheet that had columns out to infinity into a relational database. There are other tables that contain account code information, office/unit information etc, but the main table captures requirements, validated requirements, and amount funded. Orignally, I had three sep. columns to capture this data and had my reports, queries, subforms, etc...all working fine, but realized I had a problem when it came time to switch into the new year and start over, but still retain prior year info for historical purposes. After extensive posting on this community site I was later advised my design was flawed and the best way was to have one column called amount and then another called amount type where I would choose [rqmt, valrqmt, or funded]. So I've done this and recreated my reports, relationships, forms etc...but now I can't get a critical form to operate and I'm so down in the weeds that I don't even know where to start explaining it. My goal is to create a form (audit trail) that let's me capture funding changes that occur through out the year, thus increasing the number of records to the main table. While the amounts for Rqmt and valrqmt |
|
Thread Tools | |
Display Modes | |
|
|