Audit Database Advice

[Nokoff]

Good day everyone



I am trying to develop a database that will be used by myself and my colleagues to visit 150 stores in the company and capture the audit findings over the next few months. The audit consists of +- 100 checklist items,

First question: If we all have a copy of the database and use it to capture our audits individually, will it be possible to easily combine the data from each auditor at the end of the audit? Will it simply be a matter of exporting a table and importing it into a consolidated database? What about unique primary keys and that type of thing?

Below is an example of the way data used to be captured, in excel, which is then pulled through to word via a mail merge.



As you can see, the structure consists of:

Interview Question/Statement, framed in the positive, so that if the answer is no, a comment should be attached with an explanation, and I also need to display the "Reference" text and the "finding" text via the mail merge in a specific location (report is grouped by audit category, in this case, inventory security). It is imperative that AN answer is recorded, to ensure that no questions are skipped over and that there is positive assurance of the check being done.

Side question: If I enter all of the Interview questions into a table (instead of defining a field for each question), is there an easy way to populate a form with all of the question text as labels?

What would be the best structure of my database to follow, in order to not only have audit weaknesses identified but also audit strength text in the case of there not being a problem for that item.

I have attempted the database here:
Attempt 5 16.07.2015.zip

I will gladly reward you in any way I can for helping me with this, as I do understand that it must be frustrating to explain things to a noob like me.

Thank you
Nokoff

[ranman256]

Look into SYNCHRONIZING db.
You dont want to make questions as labels, instead make the questions show in a text box, but the text box will be transparent, and locked from edits.
It will look like a label.

the answers will be a checkbox ,hooked to the ANSWER field.

[CJ_London]

If we all have a copy of the database and use it to capture our audits individually, will it be possible to easily combine the data from each auditor at the end of the audit?

It can be if you set it up right.

You need to decide what will be a unique primary key - which may be multiple fields. So it might be a combination of store and questionid or store, questionid and auditor name/id for example.

then the data can be easily appended to a master table. Note that in this scenario a simple autonumber primary key is insufficient since each auditor will be potentially generating the same numbers.

Having said that, the simple autonumber key is incremental, starts from 1 and increments by 1 - but this is not guaranteed. There are two other types of autonumber key, random and replicationID. The random will generate a random number which may be positive or negative and may be sufficient for your purposes, but there is still a risk that two systems could generate the same number (you select this by changing the new values property of an autonumber key from increment to random) - I often do this to discourage users who have access to this field to giving the field a meaning which is not intended (e.g. order of input, find previous etc)

To choose a replicationid which is guaranteed to be totally unique, set your autonumber field size to replicationid - you'll also need to set the field to indexed, no duplicates. This will generate a number like {75BFF0F3-DF09-4ACB-8154-880DC6879C47}. Note that you will need to ensure any family keys are also of numeric type replicationid

[June7]

See reply in post 19 of https://www.accessforums.net/access/...22/index2.html

[Nokoff]

It can be if you set it up right.

To choose a replicationid which is guaranteed to be totally unique, set your autonumber field size to replicationid - you'll also need to set the field to indexed, no duplicates. This will generate a number like {75BFF0F3-DF09-4ACB-8154-880DC6879C47}. Note that you will need to ensure any family keys are also of numeric type replicationid

Thank you for your suggestion.

I have modified my database to use the replication ID primary key, and it is working as expected.

What I did not anticipate was that there is no longer an order to my records. New records could be added at any point in the dataset, since it is sorted by primary key.

One of my forms is set to open on the last record (most recently added) to edit the detail. Is there a way that I can still achieve this functionality? To open the most recently edited record?

Thanks

[CJ_London]

even an 'ordinary' autonumber does not guarantee you will find the last record so auto number should not be used for anything other that as a unique ID for the record. I would base the last record entered on a timestamp field (date/time with default set to now()) but this won't work for existing records. This also won't necessarily work if you are importing data - import happens so quickly that several records can have the same timestamp.

However, since you are only talking about last added record, this should not matter, but if you want to populate this field for existing records if you still have your old autonumber field available you can create a dummy unique timestamp based on a date and adding the autonumber as a number of seconds e.g.

timestamp=dateadd("s",autonumber,#01/01/2015#)

[Nokoff]

even an 'ordinary' autonumber does not guarantee you will find the last record so auto number should not be used for anything other that as a unique ID for the record. I would base the last record entered on a timestamp field (date/time with default set to now()) but this won't work for existing records. This also won't necessarily work if you are importing data - import happens so quickly that several records can have the same timestamp.

However, since you are only talking about last added record, this should not matter, but if you want to populate this field for existing records if you still have your old autonumber field available you can create a dummy unique timestamp based on a date and adding the autonumber as a number of seconds e.g.

timestamp=dateadd("s",autonumber,#01/01/2015#)

Thank you I really appreciate all of your advice!

I have a follow up question to using the Replication ID that I have used on my main table, tblAudits.

My database has grown to incorporate more tables that are linked to tblAudits. For example tblAccessories and tblGreyStock. There is a one to many relationship between tblAudits and these new tables, using AuditID (Type: Replication ID) as a Primary Key.

Now my question is, can I merge these child tables as well? The main reason for this is to divide the work amongst people. So I will create 5 copies of the database and then after collecting all of the data in the field, we will all meet back at the office to combine our work and send out reports. Do you have any suggestions on how to do this?

Thank you!
Nokoff

[CJ_London]

you would need to follow the same principle - any table you want to synchronise would need to have a replicationid primary key and a timestamp