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
|
|||
|
|||
Remote Databasing Problems - Design and Incremental Numbering
OK, the following is actually 2 questions; however, they are linked and as
such are posted together. I have put all the information I can in this – and so it is very long. I am sorry for this, but this database design seems to be a very complex problem. The organisation I work in has a financial management database that we are looking at upgrading. The new database has been developed (in testing stage only) and currently works fine in a normal multi-user environment. However, the organisation has the following quirks: 1. Web based server usage is not an option. 2. Approximately 10 personnel use the database on the organisation’s server in a “normal” multi user environment. 3. Approximately another 30 personnel need to use the database in a “remote” database style where they can utilise the database and then update the Master back at the organisation that the 10 pers from point 2 use. 4. Internet can not be used as the synchronisation method, and synchronisation needs to be as “user friendly” and “painless” as possible. 5. This is an Australian Based database and as such the Financial Year (FY) is from 01 Jul to 30 Jun. Initially this sounds like a replication problem, but I am not so sure. Also, the logical answer is use a dedicated Remote Database program; however, that is also not an option (many reasons). The job must be done by Access or not at all. The database has several tables that are interlinked. These are (either in current state, or about to be updated to this state): Project Information Table – used to store initial project information. PK is project number (though this is likely to be updated to an autonumber with searches done by FY and Project Number). The form for this table uses DLookup to set the default value of the Project Budget Exchange rate to the current exchange rate of the system (and an update query up dates this as required by the users). A DLookup field is also used on the form to set the default value of the current FY. Project Resource Table – A 1 to Many relationship with the Project Information table (one project can have many records on this table). This table stores all the Sub-Budgets and Resources of the relevant project. PK is an autonumber. Project Variation Table – Like above (1 to Many relationship). Stores all the variations to overall project budget. PK is an autonumber. Procurement and Purchase Order Table – used to store the information for all purchase orders, including procurement decision comments. This table utilises (on the form) a look up table to collect the Project Number (by FY), and another look up table (on the form) to identify which resource account the procurement is assigned to based on the resource accounts available to the Project Number. It uses DLookup on the form to set the default value of the FY to that of the current FY. The default value of the date is Date(). Currently an Autonumber is used as the PK AND the PO Number. I want to update this to an Incremental Number that resets to 1 every new FY (More on that later), and leave the Autonumber as the PK only. Contractor Quotations Table. This table has a 1 to many relationship with the Procurement and Purchase Order Table AND the Company Information table. The table stores the information of all quotations relevant to a Purchase Order, as well as who the Winning Contractor was. It uses the Purchase Order Number, FY and Project Number from the Purchase Order table, and the Company Name from the Company Information Table (a look up on the form). As such, a Purchase Order on the Procurement and Purchase Order Form might have several contractor quotations, with one chosen as the winner. The table uses an autonumber field as its PK. FY Table. This table is just a list of Financial Years. They are a string value with an input mask to make every FY be FY##/##. Last FY (1 Jul 08 to 30 Jun 09) was FY08/09. The FY value is the PK (as they will all be unique). Exchange Rate Table. This table stores relevant information about the budget exchange rate changes. Particularly important to this is the date the exchange rate is effective, the exchange rate (obviously) and the FY that the exchange rate is relevant for. The FY value is a DLookup field in the form to insert the current FY as the default value. Current FY Query. This query sorts all the FY in the relevant order (decending) and returns the top value – which without human error – is the current FY. Current Exchange Rate Query. This query sorts all the exchange rates that are “Less Than” today’s date and returns the top value – which is the current in use Budget Exchange Rate. The database also has several other tables that provide Lookup information, and are not really relevant to the problem at the moment. Also, it utilises tables called “Past” tables to store old information. This requirement is likely to be removed IF I can solve the current problems. The PROBLEMS A Setting incremental numbering based on Australian FY set ups and Utilising the Database as a Remote Multi-User Database. PROBLEM 1 – Incremental Numbering. The organisation utilises the following numbering format for its purchase orders (which is an important number throughout the system): #-FY##/## So the first PO for Financial Year 08/09 (1 Jul 08 to 30 Jun 09) is 1-FY08/09. At the start of each new FY, the PO numbers reset to 1. I have scrolled through the forum and found numerous examples of how to set up an Incremental Numbering system using DMax. That by itself is no problem. The problem is setting the Incremental Numbering to reset based on an FY. I have seen the “Reset by Year” problem and the solution that BruceM provided. I thought this would work for the situation; however, instead of using the criteria of Year in the DMax function, I would have it as FY (a string). Below is the expression I have set in the Default Value of the Form’s bounded text box: =Nz(DMax("[PONum]","[Procurement and Purchase Order Table]","[FY]=" & [Forms]![POForm]![FY]),0)+1 Where PONum is to be the Incremental number and FY is the Financial Year (Which uses a DLookup on the form to set the default value to be current FY). The problem is, this expression returns a “#Name?” error and I have no idea why. Nor do I know how to “teach” the computer to understand how to search by Australian Financial Years, less using the string value set as a default on the form. This problem becomes more complex when the user environment is considered. PROBLEM 2 – Remote Multi-User Database The incremental number option of the database will be very valuable and will sort out many of the “oddities” of the database. However, it would seem to only work effectively in a “normal” multi-user environment where everyone worked off of a shared server. The context of this database is that it must operate as both a multi-user and remote database capability. In reality, the remote users can not raise Purchase Orders by themselves, and organisational procedures mean that the only group that can print a PO is head office. However, remote users can raise all the information required for a PO and then “synch” it with head office for them to print out and have signed by the relevant authorities. This also allows remote users to track Committed/Expended amounts and their projects. The question is, how do I allow remote users to enter information in the Procurement and Purchase Order table (which will be linked with their Contactor Quotation Table), and then have this information synch with the main database. The problem I see is the following: Using incremental numbering will mean that remote users will create a PO number prior to synch (which is used to assist the Contractor Quotation Table). This will create double ups on the Master System, which stuffs the whole system up. If I do not have Incremental Numbering set on the database, and have it set only when a synch happens on the master, then the alignment between Procurement and Purchase Order Table and the Contractor Quotation Table must be the PK only. How does the computer know to maintain the relationship between the relevant records when the synch happens. Also, how would I get the Incremental Numbering to work on the Master when it adds the new records? Also, synching though simple for most people, is not easily understood and will be difficult to enact with our current computer system (due to security locks and the fact that laptops and the like can not be “hooked up” to the system). Another method would be to “quasi-synch” the system using amend and update queries and Excel Import/Export. How would that work and is it viable? Ultimately, I am looking for a quasi-replication solution to the overall database sharing problem that possibly includes import/export with excel and amend/update queries as actual replication is probably not viable with the organisation’s security infrastructure. The biggest problem is that the unique Incremental Number and relationships between Purchase Orders and Contractor Quotations must be maintained. I am trying to push the PO numbering system to change (ie, it is not based on FY, it is just a number forever). This would solve the Incremental Number problem and the Purchase Order Table “synch”, but how would you maintain the relevant relationship between a record on the PO table and the Contractor Quotation Tables when you did an amend/update? Thank you all for your help with this. -- Nick B |
#2
|
|||
|
|||
Accidental Double Up
This post is a DOUBLE UP.
I appologise for this, there was a computer internet problem. PLEASE READ : "Remote Databasing - User Implementation and Incremental Numbering" (posted above this one). -- Nick B "Nick" wrote: OK, the following is actually 2 questions; however, they are linked and as such are posted together. I have put all the information I can in this – and so it is very long. I am sorry for this, but this database design seems to be a very complex problem. The organisation I work in has a financial management database that we are looking at upgrading. The new database has been developed (in testing stage only) and currently works fine in a normal multi-user environment. However, the organisation has the following quirks: 1. Web based server usage is not an option. 2. Approximately 10 personnel use the database on the organisation’s server in a “normal” multi user environment. 3. Approximately another 30 personnel need to use the database in a “remote” database style where they can utilise the database and then update the Master back at the organisation that the 10 pers from point 2 use. 4. Internet can not be used as the synchronisation method, and synchronisation needs to be as “user friendly” and “painless” as possible. 5. This is an Australian Based database and as such the Financial Year (FY) is from 01 Jul to 30 Jun. Initially this sounds like a replication problem, but I am not so sure. Also, the logical answer is use a dedicated Remote Database program; however, that is also not an option (many reasons). The job must be done by Access or not at all. The database has several tables that are interlinked. These are (either in current state, or about to be updated to this state): Project Information Table – used to store initial project information. PK is project number (though this is likely to be updated to an autonumber with searches done by FY and Project Number). The form for this table uses DLookup to set the default value of the Project Budget Exchange rate to the current exchange rate of the system (and an update query up dates this as required by the users). A DLookup field is also used on the form to set the default value of the current FY. Project Resource Table – A 1 to Many relationship with the Project Information table (one project can have many records on this table). This table stores all the Sub-Budgets and Resources of the relevant project. PK is an autonumber. Project Variation Table – Like above (1 to Many relationship). Stores all the variations to overall project budget. PK is an autonumber. Procurement and Purchase Order Table – used to store the information for all purchase orders, including procurement decision comments. This table utilises (on the form) a look up table to collect the Project Number (by FY), and another look up table (on the form) to identify which resource account the procurement is assigned to based on the resource accounts available to the Project Number. It uses DLookup on the form to set the default value of the FY to that of the current FY. The default value of the date is Date(). Currently an Autonumber is used as the PK AND the PO Number. I want to update this to an Incremental Number that resets to 1 every new FY (More on that later), and leave the Autonumber as the PK only. Contractor Quotations Table. This table has a 1 to many relationship with the Procurement and Purchase Order Table AND the Company Information table. The table stores the information of all quotations relevant to a Purchase Order, as well as who the Winning Contractor was. It uses the Purchase Order Number, FY and Project Number from the Purchase Order table, and the Company Name from the Company Information Table (a look up on the form). As such, a Purchase Order on the Procurement and Purchase Order Form might have several contractor quotations, with one chosen as the winner. The table uses an autonumber field as its PK. FY Table. This table is just a list of Financial Years. They are a string value with an input mask to make every FY be FY##/##. Last FY (1 Jul 08 to 30 Jun 09) was FY08/09. The FY value is the PK (as they will all be unique). Exchange Rate Table. This table stores relevant information about the budget exchange rate changes. Particularly important to this is the date the exchange rate is effective, the exchange rate (obviously) and the FY that the exchange rate is relevant for. The FY value is a DLookup field in the form to insert the current FY as the default value. Current FY Query. This query sorts all the FY in the relevant order (decending) and returns the top value – which without human error – is the current FY. Current Exchange Rate Query. This query sorts all the exchange rates that are “Less Than” today’s date and returns the top value – which is the current in use Budget Exchange Rate. The database also has several other tables that provide Lookup information, and are not really relevant to the problem at the moment. Also, it utilises tables called “Past” tables to store old information. This requirement is likely to be removed IF I can solve the current problems. The PROBLEMS A Setting incremental numbering based on Australian FY set ups and Utilising the Database as a Remote Multi-User Database. PROBLEM 1 – Incremental Numbering. The organisation utilises the following numbering format for its purchase orders (which is an important number throughout the system): #-FY##/## So the first PO for Financial Year 08/09 (1 Jul 08 to 30 Jun 09) is 1-FY08/09. At the start of each new FY, the PO numbers reset to 1. I have scrolled through the forum and found numerous examples of how to set up an Incremental Numbering system using DMax. That by itself is no problem. The problem is setting the Incremental Numbering to reset based on an FY. I have seen the “Reset by Year” problem and the solution that BruceM provided. I thought this would work for the situation; however, instead of using the criteria of Year in the DMax function, I would have it as FY (a string). Below is the expression I have set in the Default Value of the Form’s bounded text box: =Nz(DMax("[PONum]","[Procurement and Purchase Order Table]","[FY]=" & [Forms]![POForm]![FY]),0)+1 Where PONum is to be the Incremental number and FY is the Financial Year (Which uses a DLookup on the form to set the default value to be current FY). The problem is, this expression returns a “#Name?” error and I have no idea why. Nor do I know how to “teach” the computer to understand how to search by Australian Financial Years, less using the string value set as a default on the form. This problem becomes more complex when the user environment is considered. PROBLEM 2 – Remote Multi-User Database The incremental number option of the database will be very valuable and will sort out many of the “oddities” of the database. However, it would seem to only work effectively in a “normal” multi-user environment where everyone worked off of a shared server. The context of this database is that it must operate as both a multi-user and remote database capability. In reality, the remote users can not raise Purchase Orders by themselves, and organisational procedures mean that the only group that can print a PO is head office. However, remote users can raise all the information required for a PO and then “synch” it with head office for them to print out and have signed by the relevant authorities. This also allows remote users to track Committed/Expended amounts and their projects. The question is, how do I allow remote users to enter information in the Procurement and Purchase Order table (which will be linked with their Contactor Quotation Table), and then have this information synch with the main database. The problem I see is the following: Using incremental numbering will mean that remote users will create a PO number prior to synch (which is used to assist the Contractor Quotation Table). This will create double ups on the Master System, which stuffs the whole system up. If I do not have Incremental Numbering set on the database, and have it set only when a synch happens on the master, then the alignment between Procurement and Purchase Order Table and the Contractor Quotation Table must be the PK only. How does the computer know to maintain the relationship between the relevant records when the synch happens. Also, how would I get the Incremental Numbering to work on the Master when it adds the new records? Also, synching though simple for most people, is not easily understood and will be difficult to enact with our current computer system (due to security locks and the fact that laptops and the like can not be “hooked up” to the system). Another method would be to “quasi-synch” the system using amend and update queries and Excel Import/Export. How would that work and is it viable? Ultimately, I am looking for a quasi-replication solution to the overall database sharing problem that possibly includes import/export with excel and amend/update queries as actual replication is probably not viable with the organisation’s security infrastructure. The biggest problem is that the unique Incremental Number and relationships between Purchase Orders and Contractor Quotations must be maintained. I am trying to push the PO numbering system to change (ie, it is not based on FY, it is just a number forever). This would solve the Incremental Number problem and the Purchase Order Table “synch”, but how would you maintain the relevant relationship between a record on the PO table and the Contractor Quotation Tables when you did an amend/update? Thank you all for your help with this. -- Nick B |
Thread Tools | |
Display Modes | |
|
|