Have Data Input Design Problem - STUMPED!

[Micron]

Short version: I need to enter/edit data based on a dynamic date range against a job ID and a list of trades. The job id will come from a listbox double-click action on a different form.

General opinion on using a grid control seems negative, so I didn't pursue whether or not it was even appropriate for the task. My thought thus far is to open a form/subform whereby main form combobox selections for month and year indirectly determine the number of rows in an unbound subform datasheet (e.g. 29 subform rows for February 2016). So, column 1 in datasheet for February 2015 would contain numbers 1 through 28. Columns 2 to 7 need to be 7 identified trades from tblTrades. User would enter a number at the intersection of multiple day rows and trade columns. More explicitly, row 15 (for Feb 15) might contain numerical entries in 3 non-contiguous trade columns. Row 16 might contain entries in 4 contiguous trade columns. When this form is closed, I need to drive the job ID and numerical entry for each trade along with the date from each date row that contains a value.

There is no relationship that I can think to create between date values and a trades list, so I have created tblMonth (January, February, etc.) and tblDayCount (1 to 31) strictly for the combos. qryDayCount uses nested date functions to correctly return Month | Day# in columns and reacts to changes in the combos. Now for the hard part: putting these unrelated fields together in a datasheet. So far, I have experimented with ADODB recordsets and have constructed rst3 based on fields from rst2 (trades data) and rst1 (day count) and manually added values. I seem to have a properly defined recordset (MS Windows Dev Center showed me how to persist the recordset to an xml file, which I've verified contains the correct values). HOWEVER, not only do I do not yet know how to drive rst3 fields and values to a table, I question whether or not this is the right approach. I suppose I would have to loop through each rst1 fields and rst2 fields to add those values to rst3, but before I go down that road, I need to ask is there a better approach?? Maybe I should use an array? My thanks to anyone who takes the time to respond.

MSA 2007; MSW ver. 7

[rpeare]

You are considering a non-normalized data structure like this (I think):

SkillDate	Skill1	Skill2	Skill3	Skill4	Skill5	Skill6
1/1/2015	1
1/2/2015	3	1
1/3/2015	4	1	2	3
1/4/2015	1	2	3

If I am correct this is very bad practice. Let's say at some point in the future you want to count all the times skill 1 was used in february, you would have to process each column for a specific value which programmatically becomes a problem.

What you really want is something more like:

ProcessID	ProcessDate	Process Other info ---->
1	1/1/2015
2	1/2/2015
3	1/3/2015
4	1/4/2015

Then a sub table

SP_ID (skill process ID)	ProcessID	SkillID	Other Skill related stuff --->
1	1	1
2	2	3
3	2	1
4	3	4
5	3	1
6	3	2
7	3	3
8	4	1
9	4	2
10	4	3

If you follow a normalized structure it's pretty easy to deal with the data entry AND manipulate how it looks on a form/report

[Micron]

Your first table looks like how I envisoned the data entry tool but not necessarily the table structure. Look at your row 3 (total = 10), which we currently record against the date and jobID in tblWorkDteDtl (work_date_detail). Code pushes this to an Excel graph on Share Point. However, mgt now wants to see the breakdown, not the total. Forgive me, but isn't normalization achieved via storing information in one place whereupon if changed, the change propagates everywhere it is used? Example would be change the company name in a table and it changes everywhere, likely due to referencing a row id related to that name. At least that's what I read. Maybe I haven't understood your point, because I believe a table like this

Date JobID Trade1 Trade2 ... Trade7
01/15/2015 46 2 5 8
01/16/2015 55 6 8 3

is easily graphed or reported on. Perhaps I am missing what is "un-normalized" about this. So far, I think things are normalized because I have separated job details from the dates they were done. If I understand your example, I would have supporting tables:

tblJobID tblWrkDteDtl.dte tblTrades.ID & .desc
110 01/10/2015 1 | ELE
200 01/07/2015 2 | PIP
305 02/06/2015 3 | IRW

and sub table
ID jobID SkillID Dte Count
1 110 1 01/01/2015 6
2 110 2 01/01/2015 11
3 200 1 02/02/2015 3
4 200 2 02/02/2015 7
5 200 3 02/02/2015 15

What worries me about this is, there are 264 jobs in the system since Jan 01 of this year. The work date detail table is 1195 rows. The sub table you suggest will be massive in a year or two will it not (due to job id x work date entries x trades used on job)? I appreciate your input and will check back tomorrow to read your further opinion if you care to oblige. Thanks.

[June7]

I'll chime in. Multiple similar name fields that hold essentially the same type data is usually considered inefficient data storage, especially if not all fields are required to have values, leaving a lot of blank 'cells'. This is called 'flat file' storage, like a big, wide spreadsheet. Ideally, relational database tables are long and narrow. As rpeare noted, searching such a 'flat file' structure can be frustrating.

A UNION query can rearrange the 'flat file' into 'long, narrow' structure.

A CROSSTAB query would denormalize the 'long, narrow' structure for charting purposes.

Having said that, I do have tables as shown in rpeare's first example table because I don't have issues of searching or filtering on the similar fields. It is a balancing act between normalization and ease of data entry/output. In my database, ease of data entry/output took precedence because we do very little statistical analysis of the data. Purpose of db is to track and publish individual sample lab reports.

[rpeare]

You can arrange your data entry so it LOOKS like what you want, but acts like a normalized table. (by the way your definition of normalized is not quite right). If your work date table has 1195 and each row has 7 jobs that's less than 10,000 normalized records which is really miniscule. I am just offering what I think is the correct structure because building reports to try and find a specific job in any of 6 fields for each record is a real nightmare. let alone do anything more complex than report them. I use tables that are 150+ fields and 250,000 records in access regularly, what you're approaching isn't nearly that troublesome but based on your description you would have three 'tiers' of tables

Jobs Table
JOBID and JOB related fields

WorkDates table
WorkDateID with a FK (foreign key) to the JOBID table and other Work DATE related information

WorkDateSkills table
WorkDateSkillID PK with a FK to the WorkDates table and a FK to the Skills table

I am not sure what you mean by the 'total' in my first example being 10, I was assuming those were foreign keys to your skills table. If you are storing more information than a skill used (like number of hours, etc) it is even more important that you properly normalize.

All that being said I was working on an example database, but june has pointed out you can artificially normalize a non-normalized structure with a union query so I guess it's no longer needed

[Micron]

I printed out one page from each table, allowing me to "see the light" more clearly. Based on your and rpeare's input, it seems the proper approach is to create tblTrades (for the trade & description) and make the current composite key in tblWrkDteDtl (jobID and WrkDte) to include TradeID (would not want the same jobID/date/TradeID combination in the table more than once). I would also add a Trade and Count field:

jobID-----WrkDte----TradeID--Count
5857----02/02/2015----7--------6
5857----02/02/2015----5--------8
6009----02/10/2015----2--------12
5555----02/10/2015----1--------1

I still have not figured out how to create a data sheet for input to these tables that will have 28 rows for 02/2015 and 29 for 2016 that will encompass the fields I need to drive data into this table. Since that was the subject of my original post, I'm not sure if I should consider this as solved. Thanks to you both for your guidance. Any suggestions on the input method would be appreciated.

[June7]

I would avoid composite keys. Multiple fields can be set as compound index to prevent duplicate combinations but they don't have to also be the primary key. That's where autonumber comes into play. This is how I resolved a situation that would have called for 4 fields to be compound key - ugh!

Why would you have a Count field? Wouldn't this be calculated data?

Do you mean data entry in non-normalized table? Code can create the records. Given a start date, last day of the month can be calculated and the difference would be the number of times to iterate a loop that writes records to table. This is a rather common procedure. Will every day of month always have data?

[Micron]

... If your work date table has 1195 and each row has 7 jobs that's less than 10,000 normalized records which is really miniscule. I am just offering what I think is the correct structure because building reports to try and find a specific job in any of 6 fields for each record is a real nightmare. let alone do anything more complex than report them. I use tables that are 150+ fields and 250,000 records in access regularly, what you're approaching isn't nearly that troublesome but based on your description you would have three 'tiers' of tables

Jobs Table
JOBID and JOB related fields

WorkDates table
WorkDateID with a FK (foreign key) to the JOBID table and other Work DATE related information

WorkDateSkills table
WorkDateSkillID PK with a FK to the WorkDates table and a FK to the Skills table

I am not sure what you mean by the 'total' in my first example being 10, I was assuming those were foreign keys to your skills table. If you are storing more information than a skill used (like number of hours, etc) it is even more important that you properly normalize.

All that being said I was working on an example database, but june has pointed out you can artificially normalize a non-normalized structure with a union query so I guess it's no longer needed

I think you are getting close to understanding our process. To clarify: I put a job in the tblAllwork; enter a body count of tradesmen (the 10, for example) against that job in tblWrkDteDtl for each day as is necessary. So for 1 week, I could have 5 entries in detail table. Now management wants me to break out the 10 as 5*trade1+3*trade2+2*trade3 (or whatever). Presently, I don't have (& can't imagine) a FK I can use in the job or detail table that is in the skills (trades) table. There would be no date in a trades table as you sugggest above. It would be a list of trades and their descriptions. I agree that 10,000 or so records in an Access table is not a concern, but I believe I need to be concerned with the possibilities (depending on the solution arrived at) if this math is correct: avg. 7 jobs/day * 5 days * 7 trades (including 0 entries for a trade?) = 245 rows per day * 260 work days = 63,700 records per year? A team people are entering their own jobs and counts daily, so this is where my concern for database bloat comes from. Based on this thread's suggestions (thanks!), I think I am getting closer to a solution for a schema. I really want to present an input method that looks like your original assumption on what I'm trying to do.

[Micron]

I would avoid composite keys. Multiple fields can be set as compound index to prevent duplicate combinations but they don't have to also be the primary key. That's where autonumber comes into play. This is how I resolved a situation that would have called for 4 fields to be compound key - ugh!

Why would you have a Count field? Wouldn't this be calculated data?

Do you mean data entry in non-normalized table? Code can create the records. Given a start date, last day of the month can be calculated and the difference would be the number of times to iterate a loop that writes records to table. This is a rather common procedure. Will every day of month always have data?

Say you have 3 FK's that are PK's somewhere else. Composite key AB would allow ABC, AC and BC but nothing else. I don't know how else to set that up since no field properties are displayed in table design view if I select 3 fields. The only option seems to be to create a composite primary key. An autonumber would not prevent rows with AC and AC. As for "Count": I confused you by using a function name. I mean "body count" for a trade and this would be data input. In the end, I need to chart the sum of those individual counts against time, but no, some days of the month would have no data.

The data entry may end up being in a non-normalized table and my original post was related to creating the records via code. I was struggling with how to present a datasheet type of form for input. I can create 29 date rows for 02/2016 or 30 for 04/2015 with a query but can't see how to present those rows in a data sheet via a query with, say, 1 column for each trade beside the date column because I see no relationship between date and a list of trades. I began playing with ADODB but figured I'd have to loop through a recordset of date rows to get 28, 31 or whatever) AND trades table rows and add them to the ADODB recorset fields collection. I came here to see if I had my head on straight before venturing down the code road. Really appreciate everyone's valuable input so far!

[rpeare]

Simple Example:

Micron.zip

[Micron]

Simple Example:

Micron.zip

I downloaded the file but cannot open it. I suspect you created it with MS 2010. Would appreciate a 2007 mdb if you can. Thanks.

[June7]

2007 is not mdb, it is accdb.

2007 should open 2010 file if it doesn't use 2010 features. Not seeing anything exclusive to 2010 in the sample db. It certainly has a lot of code. rpeare, you had this just sitting around?

Your profile shows Access 2010. Might change that if you really can't work with 2010 files.

And rpeare, your profile still reflects the dark ages.

[rpeare]

Micron.zip

Try this

This is an unbound form (I don't use bound forms)

[rpeare]

Would you believe I still work on machines that range from access 2003 through access 2013 so .... I haven't changed it!

A lot of that code is adapted from my major 'global functions' (I cut a lot of it out of this example) module, I just cut and paste it into the form module and modified field/form names so it only took an couple of hours.

[June7]

The form has a RecordSource so I would say it is bound but none of the controls are bound to fields. (Okay, just nit-picking).

Apparently you do a LOT of db development.

Would there be an advantage for your profile to show the highest level system/software you work with?