|
| 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
February 23rd, 2005, 06:34 PM
|
|||
|
|||
Help with database structure!
I'm looking for help on database construction. We have a current database
that would appear to be not normalised so I am looking for help on the correct structure. Here is our problem. We have 40 companies that supply us year to date data on a quarterly basis. The data covers different areas of business activity. There are 7 main groups that I will call Types 1 to 7 and within that we get data on two subcategories that I will refer to as Type1A and Type1B and so on within those 7 groups. The sum of Type1A PLUS Type1B EQUALS the value of Type1 so the data comes in like this: Type1A + Type1B=Type1 Type2A + Type2B=Type2 Type3A + Type3B=Type3 Type4A + Type4B=Type4 Type5A + Type5B=Type5 Type6A + Type6B=Type6 Type7A + Type7B=Type7 These equations represent the format in which the numerical data is sent to us. All the above are numbers with 3 decimal places. Each company sends us a form with this data. At the end of each quarter we need to produce a report that summarises the 40 companies' figures. The report for say December 2004 would look like this Qtr ended YTD Dec 04 Dec03 Dec 04 Dec03 Total of Type 1 2222.222 1111.111 44444.444 55555.555 Total of Type 2 2222.222 1111.111 44444.444 55555.555 Total of Type 3 2222.222 1111.111 44444.444 55555.555 Total of Type 4 2222.222 1111.111 44444.444 55555.555 Total of Type 5 2222.222 1111.111 44444.444 55555.555 Total of Type 6 2222.222 1111.111 44444.444 55555.555 Total of Type 7 2222.222 1111.111 44444.444 55555.555 You will see that we need to calculate the "quarter" totals which is in the above example the difference between the totals for December 04 and the totals for September 04 and similarly for 03. Is anyone able to point me in the right direction as to how we should consider constructing the database given that we need to store the values for each type and subtype, the company providing the data and the quarter date to which the data applies. Thank you Tony 
|
|
#2
February 23rd, 2005, 11:05 PM
|
|||
|
|||
|
Hi Tony,
Perhaps something like this. * indicates that a field is, or is in, the primary key. tblActivityCategories ActivityCategory* maybe other fields related to the ActivityCategory tblActivitySubCategories ActivitySubCategory* ParentCategory* FK into tblActivityCategories tblCompanies CompanyID* CompanyName maybe other fields related to the company, e.g. SIC, Country, State, PostCode tblData CompanyID* FK into tblCompanies DataYear* DataQuarter* ActivityCategory* FK into tblActivityCategories ActivitySubCategory* FK into tblActivitySubCategories DataValue If you are going to be storing millions of DataValues, you could reduce the mdb size (at the cost of needing more complex queries) by adding a unique Long value to each ActivitySubCategory record and using that as the foreign key in tblData instead of the two text fields that are there now. On Wed, 23 Feb 2005 18:34:32 +0000 (UTC), "Tony Williams" wrote: I'm looking for help on database construction. We have a current database that would appear to be not normalised so I am looking for help on the correct structure. Here is our problem. We have 40 companies that supply us year to date data on a quarterly basis. The data covers different areas of business activity. There are 7 main groups that I will call Types 1 to 7 and within that we get data on two subcategories that I will refer to as Type1A and Type1B and so on within those 7 groups. The sum of Type1A PLUS Type1B EQUALS the value of Type1 so the data comes in like this: Type1A + Type1B=Type1 Type2A + Type2B=Type2 Type3A + Type3B=Type3 Type4A + Type4B=Type4 Type5A + Type5B=Type5 Type6A + Type6B=Type6 Type7A + Type7B=Type7 These equations represent the format in which the numerical data is sent to us. All the above are numbers with 3 decimal places. Each company sends us a form with this data. At the end of each quarter we need to produce a report that summarises the 40 companies' figures. The report for say December 2004 would look like this Qtr ended YTD Dec 04 Dec03 Dec 04 Dec03 Total of Type 1 2222.222 1111.111 44444.444 55555.555 Total of Type 2 2222.222 1111.111 44444.444 55555.555 Total of Type 3 2222.222 1111.111 44444.444 55555.555 Total of Type 4 2222.222 1111.111 44444.444 55555.555 Total of Type 5 2222.222 1111.111 44444.444 55555.555 Total of Type 6 2222.222 1111.111 44444.444 55555.555 Total of Type 7 2222.222 1111.111 44444.444 55555.555 You will see that we need to calculate the "quarter" totals which is in the above example the difference between the totals for December 04 and the totals for September 04 and similarly for 03. Is anyone able to point me in the right direction as to how we should consider constructing the database given that we need to store the values for each type and subtype, the company providing the data and the quarter date to which the data applies. Thank you Tony -- John Nurick [Microsoft Access MVP] Please respond in the newgroup and not by email.
|
|
#3
February 24th, 2005, 01:21 PM
|
|||
|
|||
|
Thanks John. Could you explain what FK is? I assume it is Foreign Key which
I've seen mentioned elsewhere but what actually is it? Thanks again Tony "John Nurick" wrote: Hi Tony, Perhaps something like this. * indicates that a field is, or is in, the primary key. tblActivityCategories ActivityCategory* maybe other fields related to the ActivityCategory tblActivitySubCategories ActivitySubCategory* ParentCategory* FK into tblActivityCategories tblCompanies CompanyID* CompanyName maybe other fields related to the company, e.g. SIC, Country, State, PostCode tblData CompanyID* FK into tblCompanies DataYear* DataQuarter* ActivityCategory* FK into tblActivityCategories ActivitySubCategory* FK into tblActivitySubCategories DataValue If you are going to be storing millions of DataValues, you could reduce the mdb size (at the cost of needing more complex queries) by adding a unique Long value to each ActivitySubCategory record and using that as the foreign key in tblData instead of the two text fields that are there now. On Wed, 23 Feb 2005 18:34:32 +0000 (UTC), "Tony Williams" wrote: I'm looking for help on database construction. We have a current database that would appear to be not normalised so I am looking for help on the correct structure. Here is our problem. We have 40 companies that supply us year to date data on a quarterly basis. The data covers different areas of business activity. There are 7 main groups that I will call Types 1 to 7 and within that we get data on two subcategories that I will refer to as Type1A and Type1B and so on within those 7 groups. The sum of Type1A PLUS Type1B EQUALS the value of Type1 so the data comes in like this: Type1A + Type1B=Type1 Type2A + Type2B=Type2 Type3A + Type3B=Type3 Type4A + Type4B=Type4 Type5A + Type5B=Type5 Type6A + Type6B=Type6 Type7A + Type7B=Type7 These equations represent the format in which the numerical data is sent to us. All the above are numbers with 3 decimal places. Each company sends us a form with this data. At the end of each quarter we need to produce a report that summarises the 40 companies' figures. The report for say December 2004 would look like this Qtr ended YTD Dec 04 Dec03 Dec 04 Dec03 Total of Type 1 2222.222 1111.111 44444.444 55555.555 Total of Type 2 2222.222 1111.111 44444.444 55555.555 Total of Type 3 2222.222 1111.111 44444.444 55555.555 Total of Type 4 2222.222 1111.111 44444.444 55555.555 Total of Type 5 2222.222 1111.111 44444.444 55555.555 Total of Type 6 2222.222 1111.111 44444.444 55555.555 Total of Type 7 2222.222 1111.111 44444.444 55555.555 You will see that we need to calculate the "quarter" totals which is in the above example the difference between the totals for December 04 and the totals for September 04 and similarly for 03. Is anyone able to point me in the right direction as to how we should consider constructing the database given that we need to store the values for each type and subtype, the company providing the data and the quarter date to which the data applies. Thank you Tony -- John Nurick [Microsoft Access MVP] Please respond in the newgroup and not by email.
|
|
#4
February 24th, 2005, 02:33 PM
|
|||
|
|||
|
John, in tbldata two fields are marked as Primary Keys Access seems to let me
have only one? I am using Access 2000. Also are these two fields DataYear and DataQtr date fields? Thanks "John Nurick" wrote: Hi Tony, Perhaps something like this. * indicates that a field is, or is in, the primary key. tblActivityCategories ActivityCategory* maybe other fields related to the ActivityCategory tblActivitySubCategories ActivitySubCategory* ParentCategory* FK into tblActivityCategories tblCompanies CompanyID* CompanyName maybe other fields related to the company, e.g. SIC, Country, State, PostCode tblData CompanyID* FK into tblCompanies DataYear* DataQuarter* ActivityCategory* FK into tblActivityCategories ActivitySubCategory* FK into tblActivitySubCategories DataValue If you are going to be storing millions of DataValues, you could reduce the mdb size (at the cost of needing more complex queries) by adding a unique Long value to each ActivitySubCategory record and using that as the foreign key in tblData instead of the two text fields that are there now. On Wed, 23 Feb 2005 18:34:32 +0000 (UTC), "Tony Williams" wrote: I'm looking for help on database construction. We have a current database that would appear to be not normalised so I am looking for help on the correct structure. Here is our problem. We have 40 companies that supply us year to date data on a quarterly basis. The data covers different areas of business activity. There are 7 main groups that I will call Types 1 to 7 and within that we get data on two subcategories that I will refer to as Type1A and Type1B and so on within those 7 groups. The sum of Type1A PLUS Type1B EQUALS the value of Type1 so the data comes in like this: Type1A + Type1B=Type1 Type2A + Type2B=Type2 Type3A + Type3B=Type3 Type4A + Type4B=Type4 Type5A + Type5B=Type5 Type6A + Type6B=Type6 Type7A + Type7B=Type7 These equations represent the format in which the numerical data is sent to us. All the above are numbers with 3 decimal places. Each company sends us a form with this data. At the end of each quarter we need to produce a report that summarises the 40 companies' figures. The report for say December 2004 would look like this Qtr ended YTD Dec 04 Dec03 Dec 04 Dec03 Total of Type 1 2222.222 1111.111 44444.444 55555.555 Total of Type 2 2222.222 1111.111 44444.444 55555.555 Total of Type 3 2222.222 1111.111 44444.444 55555.555 Total of Type 4 2222.222 1111.111 44444.444 55555.555 Total of Type 5 2222.222 1111.111 44444.444 55555.555 Total of Type 6 2222.222 1111.111 44444.444 55555.555 Total of Type 7 2222.222 1111.111 44444.444 55555.555 You will see that we need to calculate the "quarter" totals which is in the above example the difference between the totals for December 04 and the totals for September 04 and similarly for 03. Is anyone able to point me in the right direction as to how we should consider constructing the database given that we need to store the values for each type and subtype, the company providing the data and the quarter date to which the data applies. Thank you Tony -- John Nurick [Microsoft Access MVP] Please respond in the newgroup and not by email.
|
|
#5
February 24th, 2005, 03:41 PM
|
|||
|
|||
|
John I've tried your suggested layout and I'm not sure it fits what I'm
looking for. We input the figures from a form that the companies send to us. Is there any way I can send you a copy of the form so that you can see what the structure is? "John Nurick" wrote: Hi Tony, Perhaps something like this. * indicates that a field is, or is in, the primary key. tblActivityCategories ActivityCategory* maybe other fields related to the ActivityCategory tblActivitySubCategories ActivitySubCategory* ParentCategory* FK into tblActivityCategories tblCompanies CompanyID* CompanyName maybe other fields related to the company, e.g. SIC, Country, State, PostCode tblData CompanyID* FK into tblCompanies DataYear* DataQuarter* ActivityCategory* FK into tblActivityCategories ActivitySubCategory* FK into tblActivitySubCategories DataValue If you are going to be storing millions of DataValues, you could reduce the mdb size (at the cost of needing more complex queries) by adding a unique Long value to each ActivitySubCategory record and using that as the foreign key in tblData instead of the two text fields that are there now. On Wed, 23 Feb 2005 18:34:32 +0000 (UTC), "Tony Williams" wrote: I'm looking for help on database construction. We have a current database that would appear to be not normalised so I am looking for help on the correct structure. Here is our problem. We have 40 companies that supply us year to date data on a quarterly basis. The data covers different areas of business activity. There are 7 main groups that I will call Types 1 to 7 and within that we get data on two subcategories that I will refer to as Type1A and Type1B and so on within those 7 groups. The sum of Type1A PLUS Type1B EQUALS the value of Type1 so the data comes in like this: Type1A + Type1B=Type1 Type2A + Type2B=Type2 Type3A + Type3B=Type3 Type4A + Type4B=Type4 Type5A + Type5B=Type5 Type6A + Type6B=Type6 Type7A + Type7B=Type7 These equations represent the format in which the numerical data is sent to us. All the above are numbers with 3 decimal places. Each company sends us a form with this data. At the end of each quarter we need to produce a report that summarises the 40 companies' figures. The report for say December 2004 would look like this Qtr ended YTD Dec 04 Dec03 Dec 04 Dec03 Total of Type 1 2222.222 1111.111 44444.444 55555.555 Total of Type 2 2222.222 1111.111 44444.444 55555.555 Total of Type 3 2222.222 1111.111 44444.444 55555.555 Total of Type 4 2222.222 1111.111 44444.444 55555.555 Total of Type 5 2222.222 1111.111 44444.444 55555.555 Total of Type 6 2222.222 1111.111 44444.444 55555.555 Total of Type 7 2222.222 1111.111 44444.444 55555.555 You will see that we need to calculate the "quarter" totals which is in the above example the difference between the totals for December 04 and the totals for September 04 and similarly for 03. Is anyone able to point me in the right direction as to how we should consider constructing the database given that we need to store the values for each type and subtype, the company providing the data and the quarter date to which the data applies. Thank you Tony -- John Nurick [Microsoft Access MVP] Please respond in the newgroup and not by email.
|
|
#6
February 24th, 2005, 08:40 PM
|
|||
|
|||
|
A foreign key is (usually) one field in one table that contains the
primary key values of related records in another table. Open the Northwind sample database that's installed with every copy of Access and check out the Orders and Order Details tables: [OrderID] is the primary key of one and a foreign key in the other. On Thu, 24 Feb 2005 05:21:03 -0800, "Tony Williams" wrote: Thanks John. Could you explain what FK is? I assume it is Foreign Key which I've seen mentioned elsewhere but what actually is it? -- John Nurick [Microsoft Access MVP] Please respond in the newgroup and not by email.
|
|
#7
February 24th, 2005, 08:48 PM
|
|||
|
|||
|
Tony,
A table can only have one primary key, but a PK can include more than one field. Instead of right-clicking on the field in Table Design View, select both the fields and then use the Primary Key command on the Edit menu. If you're only getting quarterly data it's simpler not to use Date/Time fields. The DataYear field should be a Number (Integer). If you number your quarters, DataQtr should be Number (Integer) too. If the data refers to specific dates, or even just to months, I'd use a single Date/Time field. (By the way, I called it DataYear because Year is the name of a VBA function, and having a field with the same name as a function can cause confusion. On Thu, 24 Feb 2005 06:33:01 -0800, "Tony Williams" wrote: John, in tbldata two fields are marked as Primary Keys Access seems to let me have only one? I am using Access 2000. Also are these two fields DataYear and DataQtr date fields? Thanks "John Nurick" wrote: Hi Tony, Perhaps something like this. * indicates that a field is, or is in, the primary key. tblActivityCategories ActivityCategory* maybe other fields related to the ActivityCategory tblActivitySubCategories ActivitySubCategory* ParentCategory* FK into tblActivityCategories tblCompanies CompanyID* CompanyName maybe other fields related to the company, e.g. SIC, Country, State, PostCode tblData CompanyID* FK into tblCompanies DataYear* DataQuarter* ActivityCategory* FK into tblActivityCategories ActivitySubCategory* FK into tblActivitySubCategories DataValue If you are going to be storing millions of DataValues, you could reduce the mdb size (at the cost of needing more complex queries) by adding a unique Long value to each ActivitySubCategory record and using that as the foreign key in tblData instead of the two text fields that are there now. On Wed, 23 Feb 2005 18:34:32 +0000 (UTC), "Tony Williams" wrote: I'm looking for help on database construction. We have a current database that would appear to be not normalised so I am looking for help on the correct structure. Here is our problem. We have 40 companies that supply us year to date data on a quarterly basis. The data covers different areas of business activity. There are 7 main groups that I will call Types 1 to 7 and within that we get data on two subcategories that I will refer to as Type1A and Type1B and so on within those 7 groups. The sum of Type1A PLUS Type1B EQUALS the value of Type1 so the data comes in like this: Type1A + Type1B=Type1 Type2A + Type2B=Type2 Type3A + Type3B=Type3 Type4A + Type4B=Type4 Type5A + Type5B=Type5 Type6A + Type6B=Type6 Type7A + Type7B=Type7 These equations represent the format in which the numerical data is sent to us. All the above are numbers with 3 decimal places. Each company sends us a form with this data. At the end of each quarter we need to produce a report that summarises the 40 companies' figures. The report for say December 2004 would look like this Qtr ended YTD Dec 04 Dec03 Dec 04 Dec03 Total of Type 1 2222.222 1111.111 44444.444 55555.555 Total of Type 2 2222.222 1111.111 44444.444 55555.555 Total of Type 3 2222.222 1111.111 44444.444 55555.555 Total of Type 4 2222.222 1111.111 44444.444 55555.555 Total of Type 5 2222.222 1111.111 44444.444 55555.555 Total of Type 6 2222.222 1111.111 44444.444 55555.555 Total of Type 7 2222.222 1111.111 44444.444 55555.555 You will see that we need to calculate the "quarter" totals which is in the above example the difference between the totals for December 04 and the totals for September 04 and similarly for 03. Is anyone able to point me in the right direction as to how we should consider constructing the database given that we need to store the values for each type and subtype, the company providing the data and the quarter date to which the data applies. Thank you Tony -- John Nurick [Microsoft Access MVP] Please respond in the newgroup and not by email. -- John Nurick [Microsoft Access MVP] Please respond in the newgroup and not by email.
|
|
#8
February 24th, 2005, 09:46 PM
|
|||
|
|||
|
Tony,
Don't confuse the layout of the form and the structure of your data. The great thing about relational databases such as Access is that they separate the way the data is *stored* from the way it is *presented*. First, you need to understand your data and create a relational structure that will accommodate it. When the structure is right, you start designing the data entry forms and the reports. If you try to make the *structure* reflect the layout of existing paper forms you're likely to end up with a database that can't create the reports you need - or can only do so with contorted code and queries. Once the structure is right it's always possible to build data entry forms that mimic just about any paper forms (although it's sometimes a lot of work) - but often it's better to compromise with something that's easy to use and not to hard to build. On Thu, 24 Feb 2005 07:41:07 -0800, "Tony Williams" wrote: John I've tried your suggested layout and I'm not sure it fits what I'm looking for. We input the figures from a form that the companies send to us. Is there any way I can send you a copy of the form so that you can see what the structure is? "John Nurick" wrote: Hi Tony, Perhaps something like this. * indicates that a field is, or is in, the primary key. tblActivityCategories ActivityCategory* maybe other fields related to the ActivityCategory tblActivitySubCategories ActivitySubCategory* ParentCategory* FK into tblActivityCategories tblCompanies CompanyID* CompanyName maybe other fields related to the company, e.g. SIC, Country, State, PostCode tblData CompanyID* FK into tblCompanies DataYear* DataQuarter* ActivityCategory* FK into tblActivityCategories ActivitySubCategory* FK into tblActivitySubCategories DataValue If you are going to be storing millions of DataValues, you could reduce the mdb size (at the cost of needing more complex queries) by adding a unique Long value to each ActivitySubCategory record and using that as the foreign key in tblData instead of the two text fields that are there now. On Wed, 23 Feb 2005 18:34:32 +0000 (UTC), "Tony Williams" wrote: I'm looking for help on database construction. We have a current database that would appear to be not normalised so I am looking for help on the correct structure. Here is our problem. We have 40 companies that supply us year to date data on a quarterly basis. The data covers different areas of business activity. There are 7 main groups that I will call Types 1 to 7 and within that we get data on two subcategories that I will refer to as Type1A and Type1B and so on within those 7 groups. The sum of Type1A PLUS Type1B EQUALS the value of Type1 so the data comes in like this: Type1A + Type1B=Type1 Type2A + Type2B=Type2 Type3A + Type3B=Type3 Type4A + Type4B=Type4 Type5A + Type5B=Type5 Type6A + Type6B=Type6 Type7A + Type7B=Type7 These equations represent the format in which the numerical data is sent to us. All the above are numbers with 3 decimal places. Each company sends us a form with this data. At the end of each quarter we need to produce a report that summarises the 40 companies' figures. The report for say December 2004 would look like this Qtr ended YTD Dec 04 Dec03 Dec 04 Dec03 Total of Type 1 2222.222 1111.111 44444.444 55555.555 Total of Type 2 2222.222 1111.111 44444.444 55555.555 Total of Type 3 2222.222 1111.111 44444.444 55555.555 Total of Type 4 2222.222 1111.111 44444.444 55555.555 Total of Type 5 2222.222 1111.111 44444.444 55555.555 Total of Type 6 2222.222 1111.111 44444.444 55555.555 Total of Type 7 2222.222 1111.111 44444.444 55555.555 You will see that we need to calculate the "quarter" totals which is in the above example the difference between the totals for December 04 and the totals for September 04 and similarly for 03. Is anyone able to point me in the right direction as to how we should consider constructing the database given that we need to store the values for each type and subtype, the company providing the data and the quarter date to which the data applies. Thank you Tony -- John Nurick [Microsoft Access MVP] Please respond in the newgroup and not by email. -- John Nurick [Microsoft Access MVP] Please respond in the newgroup and not by email.
|
|
#9
February 25th, 2005, 10:23 AM
|
|||
|
|||
|
Thansk John some useful tips there. I'll go away and try rethinking what
we're doing Tony "John Nurick" wrote: Tony, Don't confuse the layout of the form and the structure of your data. The great thing about relational databases such as Access is that they separate the way the data is *stored* from the way it is *presented*. First, you need to understand your data and create a relational structure that will accommodate it. When the structure is right, you start designing the data entry forms and the reports. If you try to make the *structure* reflect the layout of existing paper forms you're likely to end up with a database that can't create the reports you need - or can only do so with contorted code and queries. Once the structure is right it's always possible to build data entry forms that mimic just about any paper forms (although it's sometimes a lot of work) - but often it's better to compromise with something that's easy to use and not to hard to build. On Thu, 24 Feb 2005 07:41:07 -0800, "Tony Williams" wrote: John I've tried your suggested layout and I'm not sure it fits what I'm looking for. We input the figures from a form that the companies send to us. Is there any way I can send you a copy of the form so that you can see what the structure is? "John Nurick" wrote: Hi Tony, Perhaps something like this. * indicates that a field is, or is in, the primary key. tblActivityCategories ActivityCategory* maybe other fields related to the ActivityCategory tblActivitySubCategories ActivitySubCategory* ParentCategory* FK into tblActivityCategories tblCompanies CompanyID* CompanyName maybe other fields related to the company, e.g. SIC, Country, State, PostCode tblData CompanyID* FK into tblCompanies DataYear* DataQuarter* ActivityCategory* FK into tblActivityCategories ActivitySubCategory* FK into tblActivitySubCategories DataValue If you are going to be storing millions of DataValues, you could reduce the mdb size (at the cost of needing more complex queries) by adding a unique Long value to each ActivitySubCategory record and using that as the foreign key in tblData instead of the two text fields that are there now. On Wed, 23 Feb 2005 18:34:32 +0000 (UTC), "Tony Williams" wrote: I'm looking for help on database construction. We have a current database that would appear to be not normalised so I am looking for help on the correct structure. Here is our problem. We have 40 companies that supply us year to date data on a quarterly basis. The data covers different areas of business activity. There are 7 main groups that I will call Types 1 to 7 and within that we get data on two subcategories that I will refer to as Type1A and Type1B and so on within those 7 groups. The sum of Type1A PLUS Type1B EQUALS the value of Type1 so the data comes in like this: Type1A + Type1B=Type1 Type2A + Type2B=Type2 Type3A + Type3B=Type3 Type4A + Type4B=Type4 Type5A + Type5B=Type5 Type6A + Type6B=Type6 Type7A + Type7B=Type7 These equations represent the format in which the numerical data is sent to us. All the above are numbers with 3 decimal places. Each company sends us a form with this data. At the end of each quarter we need to produce a report that summarises the 40 companies' figures. The report for say December 2004 would look like this Qtr ended YTD Dec 04 Dec03 Dec 04 Dec03 Total of Type 1 2222.222 1111.111 44444.444 55555.555 Total of Type 2 2222.222 1111.111 44444.444 55555.555 Total of Type 3 2222.222 1111.111 44444.444 55555.555 Total of Type 4 2222.222 1111.111 44444.444 55555.555 Total of Type 5 2222.222 1111.111 44444.444 55555.555 Total of Type 6 2222.222 1111.111 44444.444 55555.555 Total of Type 7 2222.222 1111.111 44444.444 55555.555 You will see that we need to calculate the "quarter" totals which is in the above example the difference between the totals for December 04 and the totals for September 04 and similarly for 03. Is anyone able to point me in the right direction as to how we should consider constructing the database given that we need to store the values for each type and subtype, the company providing the data and the quarter date to which the data applies. Thank you Tony -- John Nurick [Microsoft Access MVP] Please respond in the newgroup and not by email. -- John Nurick [Microsoft Access MVP] Please respond in the newgroup and not by email.
|
| Thread Tools | |
| Display Modes | |
|
|
Similar Threads
|
||||
| Thread | Thread Starter | Forum | Replies | Last Post |
| Access Error Message when opening database | eah | General Discussion | 3 | January 26th, 2005 10:04 AM |
| Exclusive access to the database | Steve Huff | General Discussion | 17 | December 24th, 2004 06:23 PM |
| add field to back-end existing table | James | Database Design | 7 | December 6th, 2004 01:41 AM |
| Template Wizard Problem - Database Structure | setoFairfax | General Discussion | 1 | September 17th, 2004 09:26 AM |
| help to make database structure | database structure | New Users | 2 | May 17th, 2004 07:21 PM |
Linear Mode
