N
Nick
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 Are: 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.
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 Are: 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.