Advice please - going round in circles

[matt beamish]

Hi, I occassionally design databases for use within my organisation - mostly these have been multi table relational databases in Access 2003 for up to 12 users to use on a network, and have worked successfully. But I have virtually no training in database design, and its only a small part of my job.

Currently I am trying to design a simple employee time recording database that will dovetail with an accounts and costing database that already exists.

I find my enduring problems with the design of the databases using Access 2003 come back to
1. That the results of multi-table queries become uneditable (the Dynaset inconsistent records hasnt helped)
2. That continuous forms cannot contain subforms (I generally dont like using subforms as the data held becomes unsearchable).

As I find that the only solution that I can think of to enable data entry into multiple tables from one form is to use either method 1 or 2, I get really stumped by this, and I must simply be designing my databases the wrong way. I endeavour to break data down so that the tables are only holding positive values (i.e. not any null values), but I subsequently need to find a way of knitting the tables back together in the forms.
As I am not a programmer my understanding of language is a bit lacking.
Can anybody recommend any books I can read to try and improve my database designing abilities.

Thanks in advance

Matt

 

[RuralGuy]

Here's a link that you might find useful: Why is my query read-only?

 

[matt beamish]

Thankyou for the comments Rural Guy and Pat.
I have tried to ensure that my joining query does not breach any condition listed here http://allenbrowne.com/ser-61.html but still I cannot edit the results in the child tables, so I am not sure what to do here. The peculiar thing is that up until yesterday I had a query based on four tables parts of which were visible on a form, and I was able to enter data into three of the tables with no problems (the other was a look up only) but after a fruitless day yesterday attempting to get a Time Summing bit to work, it has gone wrong.

Joining tables in queries and then entering data in one form is so elegant and easy to set up that I am loathe to abandon it but perhaps I should...

Generally speaking, each form should update only a single table. The form's query may join to other tables for lookup purposes but those lookup fields shold not be updated.

When you want to use dependent continuous forms, put them both on an unbound main form. You can use the master/child links to have the dependent subform change based on the record selected in the master subform.

It sounds like I should be setting my forms up differently Pat - and using an unbound form - but how then do I would I control which records appear and in what order? Would you have an example of this?

Thanks for the help

Matt

 

[matt beamish]

T_Daily_Jobs records time spent on a job during a day using date, start_time, end_time and a job number (which comes from another database). Primary key is "Daily_Record_ID" which is an autonumber.

T_Daily_Jobs_Comment records comments about that particular piece of work - the comment does not have to be made, so a record is only generated when a comment is made. This table holds its own primary key autonumber, but also a copy of the same record ID number as the T_Daily_Jobs. The tables are related with a one to one with enforced referential integ.

T_Daily_Jobs_Mileage holds a possible mileage box for that particular piece of work. Again only some jobs will have a mileage so I only want to create a record for those that do. The table is related to T_Daily_Jobs similarly to T_Daily_Jobs_comment.

The following is the query:

SELECT T_Daily_Jobs.StaffInit, T_Daily_Jobs.Job_No, T_Daily_Jobs.Start_time, T_Daily_Jobs.End_time, (([End_time]-[Start_time])) AS Time_Spent, T_Daily_Jobs.Job_Date, T_Daily_Jobs.Daily_Record_ID, T_Daily_Jobs_comment.Comments, T_Daily_Jobs_Mileage.Mileage, T_Daily_Jobs_Mileage.Daily_Record_ID, T_Daily_Jobs_comment.Daily_Record_ID
FROM (T_Daily_Jobs LEFT JOIN T_Daily_Jobs_comment ON T_Daily_Jobs.Daily_Record_ID = T_Daily_Jobs_comment.Daily_Record_ID) LEFT JOIN T_Daily_Jobs_Mileage ON T_Daily_Jobs.Daily_Record_ID = T_Daily_Jobs_Mileage.Daily_Record_ID
ORDER BY T_Daily_Jobs.Start_time, T_Daily_Jobs.Job_Date;

Initially if within the query results, I rentered a comment, the relevant autonumber for T_Daily_Jobs would be copied automatically into the T_Daily_Jobs_comment.Daily_Record_ID (and same for T_Daily_Jobs_Mileage. There was no need to write any events to set values - the database did it automatically.

Now the fields will not auto populate, and I am unable to edit the query results.

Thanks for the help.

Matt

 

[matt beamish]

When basing a form on a 1-1 or multiple 1-1 joins, you must populate at least one field in each table in order for a record to be created. The whole point of sparse tables is that rows are NOT created automatically.

OK Thanks.

But, why have 1-1 relationships at all. Comments would be valid if you allowed multiples but then the relationship would be 1-m but mileage seems a bit of a stretch.

But as there could only be one comment per job entry it seemed better to set the relationship to reflect the reality.

In the interim today, to make the database more robust and avoid this sometimes editable sometimes not problem, I wrote some small event procedures using the SetValue command, so that when a comment is made or Mileage entered, the relevant DailyJobIDs are written into the relevant tables. So that's that bit done and hopefully sorted.

Now I have an issue with summing a time balance. I have had some little success in converting Dates into decimal values that can be summed, but I guess I will need to ask some more questions soon.

Matt
If I get stuck, I will ask again.

 

[The_Doc_Man]

First and foremost, if everything is in a single record, you should have no problems entering it via a single form. If you have multiple records, you have pitfalls galore. So let's examine your design issues.

If nobody makes a comment for that bit of work, you don't want an entry. But it is easy enough to make that a "Memo" field that will contain an "empty" string if you make no entry. That takes up only a little bit of space, probably on the order of 8 bytes per record, to have an empty string associated with a record. What else is in the comment table that would require it to be separate?

If nobody incurs a mileage charge for that bit of work, you don't want an entry. But it is easy enough to make an INTEGER field (for integer miles only) or a SINGLE field (for miles and fractions). And if that is zero, you waste only 4 bytes per record. What else is in the mileage table that would require it to be separate?

Doing this as a single-table entry with two possibly zero-valued or zero-length fields, you have NO joins, a single-table query, and only one record ever needs to be created. If you are worried about working with this for some report, it is easy enough to write queries that look for memo fields being empty (IsEmpty) and for mileage to be greater than zero or equal to zero. Then base the report on the query results.

So... the justification for splitting into three tables - and incurring the overhead of having to do sparse-table relational linkages - must be based on something you have not yet described to us. Or, the justification was a mirage.

The only issue that would slow me down on this would be if the tables would eventually grow to several tens of thousands of records without being archived, up to several hundred thousand. THEN you might consider splitting tables. But even then, its a close call.

 

[RuralGuy]

Welcome back DocMan. I have missed your posting; or have I just been missing them?

 

[matt beamish]

So... the justification for splitting into three tables - and incurring the overhead of having to do sparse-table relational linkages - must be based on something you have not yet described to us. Or, the justification was a mirage.

Thankyou for your thoughts. The justification simply comes from a perception that holding null values is bad practise. I absolutely agree that holding all the values in one table would be much, much simpler and I would have finished what I needed to do days/weeks ago....
However, although the reality of the quantity of data involved might encourage the pragmatic "lob it all in one table, nulls and all" approach, surely to a novice like me, splitting/normalising, and getting it to work smoothly is all good practise that will stand me in a much better stread in the long run.
*If* I had written the SetValue controls in the first instance, rather than relying on the Automagic lookups that sometimes do and sometimes dont work, I would not have needed to write a post.

Thanks

Matt