Help with primary key

[JimmyRN]

I am not a programmer, or even an Access super-user. I'm hardly an Access sometimes user, but I have a manager that insists on handwritten reports being filled out, which he then scans into his computer as an "electronic" record. I have used Access in the past successfully, but it was like Access 2003.....

So, I have all the information for the report he wants generated, each segment is separated into different tables, no table holds more than one type of information, and all the records in the tables are generated from a form that I intend to open, allow edits, etc until "submitted" when the report will be generated and emailed to this manager.....



So far so good? Except. We have 2 twelve hour shifts to report on every day. The unique record identifier that makes the most sense is "date() & "AM"" or "date() & "PM"", but access 2010 won't seem to let me work with that. Autonumber keeps screwing up my test data, so that's out....

Any ideas? Thanks.

[orange]

Work through this tutorial to understand how a good description of your business is key to designing a database to support that business. You will learn a procedure to get the proper tables, attributes and relationships to support your business.
You have to work through it, but you will learn.

If you need more info- see this for Normalization.

[JimmyRN]

Okay, so the tutorial didn't help with this particular issue. The only unique data that ties all the different fields/tables together is the date and the "shift" either AM or PM. There are four unique tables that should be tied to the date and shift, staffing (that is pulled from a unique table that lists available staff), tasks (yes/no in it's own table), volume (again, several fields that define the date and shift volume) and occurrences (unique table).

The only field that remains the same in all these tables, and would be used to query data for reports, forms, etc is the date and the shift.

[June7]

That is a compound key. You will have to build queries and form/subform arrangement that link on both fields. I avoid compound keys.

Alternative is to use an autonumber field as PK in parent table and a number field in related table to store the PK as FK.

The PK/FK fields are used to link records of related tables in queries and form/subform. Searches can still be done by applying filter criteria to other fields.

What does 'Autonumber keeps screwing up my test data' mean - what happens?

[JimmyRN]

Just to continue this thread from so very long ago, with more specifics this time.
Narrative:
Company XYZ has supervisors on each of two shifts every day. These supervisors are responsible for reporting very specific information about the goings-on during their respective shifts, via email, to a distribution list of stakeholders. Currently, each supervisor is opening a Word doc with report formatting (all data is blank), entering the information in 19 different cells at end of shift, and copy/pasting the result into an Outlook email, choosing the distribution list, and sending. This seems inefficient and wasteful. In addition, not every supervisor is “tech savvy” enough to complete this task in a way that promotes homogeneity.

Optimum use of the tech available would enable supervisors to enter data into a database via user friendly, novice proof forms throughout the entirety of their shift, and upon completion of all editing behaviors, click a button to send completed report to stakeholders.

I have designed the tables. I have designed the reports. I have in mind a query based interface system that will allow a previously completed report to be generated/viewed by any stakeholder at any time for any day of the year, should questions arise at a later date.

The issue is that the only attributes I can find to uniquely identify each attribute of the 16 tables is the date (could be represented as an integer 20170730 for 07/30/2017) and the shift (AM and PM, could also be represented as integers “AM = 1” and “PM = 2”). With that in mind, could I generate a unique PK from the date and shift? For example, above would result in the PK for the tables being 201707301 for the AM shift and 201707302 for the PM. Seems to make sense to me….

I cannot, however, seem to figure out how to assign the PK to the attributes in the tables initially. A “new record” form would open with just date/shift, on “continue” would advance to the next form allowing entry of associated data into the remaining 16 tables. I was thinking about having a form open on db open with choices such as “create new report,” “edit existing report” and “view/print previous reports”. Create new report would accomplish just that. Edit existing would ask for parameter input (date/shift) to find the appropriate records and allow editing in a form. Finally, view/print previous reports would take user input to find and display only a report designed like the one below, based on a specific previous date and shift.
So any help with discovering how to set this up relationally would be greatly appreciated.

d/s = "date/shift" PK in each of 16 tables. The end product will have appropriately named tables, this is a visual example of the report, and from where each cell's data is populated. Looks WAY better in MS than in this post.....

Date:		Shift:		Total Traffic:
Most often today, but could be any day. Selectable from date picker on form.		Only two possible values: “AM” or “PM”, selectable via drop down box on form.		Completely variable, entered as free text on form, will ALWAYS be no larger than 3 digit number
	Demographic 1		data from d/s tbl 01 entered as free text on form, defaults to “NONE”
Demographic 2		data from d/s tbl 02 entered as free text on form, defaults to “NONE”
Demographic 3		data from d/s tbl 03 entered as free text on form, defaults to “NONE”
Demographic 4		data from d/s tbl 04 entered as free text on form, defaults to “NONE”
Tbl 5	data from d/s tbl 05 entered as free text on form, defaults to “NONE”
Tbl 6	data from d/s tbl 06 entered as free text on form, defaults to “NONE”
Tbl 7	data from d/s tbl 07 entered as free text on form, defaults to “NONE”
Tbl 8	data from d/s tbl 08 entered as free text on form, defaults to “NONE”
Tbl 9	data from d/s tbl 09 entered as free text on form, defaults to “NONE”
Tbl 10	data from d/s tbl 10 entered as free text on form, defaults to “NONE”
Tbl 11	data from d/s tbl 11 entered as free text on form, defaults to “NONE”
Grouping for report: label only, no data in this field	Tbl 12: data from d/s tbl 12 entered as free text on form, defaults to “NONE”
Tbl 13: data from d/s tbl 13 entered as free text on form, defaults to “NONE”
Tbl 14: data from d/s tbl 14 entered as free text on form, defaults to “NONE”
Tbl 15: data from d/s tbl 15 entered as free text on form, defaults to “NONE”
Tbl 16: data from d/s tbl 16 entered as free text on form, defaults to “NONE”

[orange]

Looks WAY better in MS than in this post.....

Perhaps you could show us the MS version ---attach a jpg or whatever you have.

Are you really suggesting 16 tables ? What is the subject of each of these? Examples please.
Are you aware that data storage and data presentation are often very different?

It would seem that Supervisor would also be part of your compound unique info.

Shift
DateofInfo
Supervisor

Or you could use an autonumber pk and create a unique composite index of the 3 fields mentioned.

[Micron]

Just did a quick read through, and if I understand this statement

The issue is that the only attributes I can find to uniquely identify each attribute of the 16 tables is the date

then this is probably the wrong approach. It certainly would make your task nearly impossible without repeating information in several tables, which is usually bad design. I take the statement to mean that the same data (maybe shift ID for example) has to appear in each table. This is wrong. Something has to appear in its native table (A) as a PK and in another table (B) as an FK but Something doesn't (and in many cases, shouldn't) have to appear in table C as well. Certainly not in C, D, E... and so on. Whatever is the PK of A is an FK of B, whose PK is an FK of C, whose PK is an FK of D and so on.

I think this

data from d/s tbl 02”

is also wrong if that means date/shift from other tables. Can this not be simplified as was suggested in #4 wherein you use the PK (autonumber?) of the parent table rather than trying to associate data from two foreign fields? Not trying to be rude, but I wonder if you need a refresher on normalization and relational theory?

While I'm in a cautionary mood, note that if you intend to do historical reporting through queries that use the BETWEEN operator, you will not get data created after midnight on the ending date. I mention this because you're dealing with 12 hour shifts and will have to either incorporate time into the criteria, or use DateAdd to a date to go beyond midnight.

[ssanfu]

===============================================

"Old Programmer's Rule" is this: If you can't do it on paper then you can't do it in Access. Until you can draw out your tables AND rules for how they change for each function you want to perform, you are going nowhere fast.

===============================================

AutoNumber
Purpose: Use an AutoNumber field to provide a unique value that serves no other purpose than to make each record unique. The most common use for an AutoNumber field is as a primary key, with a field in another table as a foreign key (FK) that stores the PK value (to link the records). These two fields are used to form relationships between tables.

Microsoft Access Tables: Primary Key Tips and Techniques
http://www.fmsinc.com/free/newtips/primarykey.asp

Autonumbers--What they are NOT and What They Are
http://www.utteraccess.com/wiki/Autonumbers

[JimmyRN]

This is great advice. As I think this through and ask questions, I am beginning to see other possibilities beyond my original simplistic design. Thank you for your input! At this point, you are entirely correct. I am going nowhere fast.... but, man, the VIEW is amazing!
Please see the other replies. I KNOW I'm in the right place for this. I may be overthinking, but once I can cogently communicate what I want to you all, I believe the solution will be simple.

Thank you!
JimmyRN

===============================================

"Old Programmer's Rule" is this: If you can't do it on paper then you can't do it in Access. Until you can draw out your tables AND rules for how they change for each function you want to perform, you are going nowhere fast.

===============================================

AutoNumber
Purpose: Use an AutoNumber field to provide a unique value that serves no other purpose than to make each record unique. The most common use for an AutoNumber field is as a primary key, with a field in another table as a foreign key (FK) that stores the PK value (to link the records). These two fields are used to form relationships between tables.

Microsoft Access Tables: Primary Key Tips and Techniques
http://www.fmsinc.com/free/newtips/primarykey.asp

Autonumbers--What they are NOT and What They Are
http://www.utteraccess.com/wiki/Autonumbers

[orange]

JimmyRN,

How about providing examples of your form -completed with typical information? You can use names/terms like Porky Pig, Centerville, up state..... But to help with context it would be helpful if we could see a completed form or a few.

Also, you might want to consider some focused codified responses. That is, establish some categories of responses or standard terms. Free form text can be "less than helpful" with typos, poor spelling, bad grammar.... but it is a step up to get rid of "bad handwriting".

Good luck.

[JimmyRN]

Just composed a pretty detailed response with a sample table structure. Was called away from my desk, computer went to sleep and when I returned I lost it ALL!

Bear with me guys....