Remote Databasing - User Implementation and Incremental Numbering

[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.

 

[Nick]

I have just read some of the stuff on the Wiki on replication. Particularly
the FAQ:

http://www.dfenton.com/DFA/Replicat...a_network._Can_I_still_use_Jet_Replication.3F

This highlights that what the organisation needs to do IS NOT POSSIBLE with
replication as I can not use a web server solution, nor can I “hook up†user
laptops to the home base network.

As such, the question remains: How does one use a database in a multi-user
environment where the database must be updated and the users can not hook up
to any common system?

Though I know there are COTS solutions to this problem, as I have already
stated these are not options within the organisation (for many reasons). It
must be achievable by Access only.

Once again, thank you for any consideration or advice with this.


--

Nick B

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.

 

[Nick]

OK, so more research has been done and Replication (in any form) is NOT the
way to do this. Happy with that.

What is the though process on Random Numbers?

Lets say that the remote databases have an autonumber for the local computer
reference ID in the Procurement and Purchase Order table AND another column
that is an autonumber that is Random. Using this random number AND some other
piece of information on the Purchase Order form (like Project Number) could
be used to link the Contractor Quotation Table information.

When the remote personnel send updates back to the Master, all they send
back is the Random Number (and relevant table information) and the relevant
Contractor Quoation Table information. The “Master†Database then enters all
the updates (amend and update queries) and then “issues†Purchase Order
Numbers, which can then be sent out as updates (same style in reverse).

The master table has an autonumber as its ID, a column for the Random Number
(collected from the update) and a Purchase Order number generation column.
This way the master can still reference the correct Contract Quotaiton Table
information using the Random Number and Project Number.

Could the above concept work? I know it does not sound pretty, but there
does not seem to be any “simple†solution here.


--

Nick B

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.

 

[Nick]

After considering the ongoing research, I am thinking about the following
solution (for the forum’s consideration), which is essentially two types of
database that can collect the required information and allow for
updates/amends:

Remote Users:

Remote Users do not have PO Numbers generated. Their [Procurement and
Purchase Order Table] has an autonumber (PK) for stand alone database
referencing, an autonumber (Random) for unique identification for the
“global†system and an empty field for the PO number to be added to once it
is determined by the “Master†at head office.

The Remote User (who in reality is inputting most of the data) [Contract
Quotation Table] links contract quotations to the [Procurement and Purchase
Order Table] (and its form as a sub-form) through linking the Random Number
generated on the [Procurement and Purchase Order Table] AND some other
relevant field (maybe Project Number) AND the PO Number (which is unknown
when the record is first raised – but will be when uploaded to the Master).
This is done in order to ensure that each individual record in the “globalâ€
system is unique. Here I am trying to minimise the likelihood of a record by
User 1 generating the same random number as User 2 and confusing everything.

Each remote database will have relevant queries and (once I work out how)
coding to, at the push of a button, export all new entries into Excel so that
the information can be either emailed or transferred by CD/Disk to the Master
(so 30 Excel spreadsheets).

Master Database

It is true that the term is a misnomer. This is not a “Master†in the sense
of replication. It is a Master in the sense that it is the Primary Database
and it holds ALL information after an update.

The Master will have relevant coding and queries to import data from an
excel spreadsheet and then add the new entries to its [Procurement and
Purchase Order Table] AND the [Contractor Quotations Table]. As it amends the
table, it will assign a PO Number to each new record and have this cascade
into the [Contractor Quotations Table].

The mechanics of this are still being thought about, but I have read through
the MultiUser Custom Counter information at:

http://support.microsoft.com/kb/210194

This provides the basic coding for the PO Number that I am seeking (and I
can have it reset at the start of each FY through a simple delete and amend
system – hopefully).

It is with this function that I plan to add PO Numbers to the new entries.
However, I am not sure how yet. My thinking is to make this function (and I
know this is not “good formâ€) the default value of the Master [Procurement
and Purchase Order Table] PO Number field. I hope (to be tested) that once I
add new fields, it will automatically update the PO Number to the next in the
sequence. That is the plan to be tested.

Cascading this to the [Contractor Quotation Table] will require an Update
Query once the PO Numbers are assigned.

The database will then export the table to Excel and remote users can import
the updated table back into their system.

All good in theory.

The question is now how do I deal with users changing already existing
records in the [Procurement and Purchase Order Table] (and all other tables,
as the question is the same for all of them).

Thoughts, opinions, feelings?


--

Nick B

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.