hello everyone

i found myself trying to write this outer join query and i realised that probably i need to improve my design rather than write the query! so here goes

i've created a form which a user can navigate through to look at various jobs which have been done (its a loft insulation database)

now one of the things the users need to do is to arbitarily choose jobs for which they are going to phone up the customer and check the work was completed satisfactorily for QA purposes

the table containing information about the jobs has a yes/no field "selectedForQA" which comes up as a checkbox on the form so they can just check the jobs they decide to check up on

i wrote a query which runs on click of a a command button "send selected to QA" which is just an append query sticking primary keys of the jobs table into another table TBL_QAList which has other fields 'contacted', 'satisfactory' and 'notes' for them to check when they have phoned the customer

of course i can't simply rely on the Append query because the jobs previouly loaded into QAList will still be checked and the append will try and append the same data twice cauzing a primary key violation

i don't doubt that it must be possible to only append those records which aren't already in the table (i believe its an outer join to return the records in neither of two tables? - i could then append the results of that query?) but anyhow the fact that i'd have to do something of that sort suggests to me my design is screwed up in the first place

i know that its bad to have the same data in two tables but all i would have replicated here would be the primary key and when i present the data to the user i use the table relationships to get them all the contact details etc that doesn't get copied across tables

maybe the better solution is to have no QAList table just the jobs table (with sentToQA field) and a QADone table - then the form the user sees can get its data from a query which finds those jobs for which Jobs.selectedForQA = True AND which are not in the QADone table and when they check the done box on their form the record gets added to QADone

have i just answered my own question or is there a yet better way of doing this? or was i on the right track in the first place?

Personally I'd have both SelectedForQA and QADone as fields in the jobs table. I'd keep the contact history table separate, linked by the job id.

mmm yeah that makes sense i hadn't even thought about the potentially extensive contact history which is a better candidate for its own table

that can form the basis of the user form and they can have a button for each QA record 'history closed' which then writes to the QA done field preventing the job from coming up on their list

on a related note i feel i'm breaking principles of normaisation by including customer details in the jobs table (insterad of a separate but linked customers table) but the nature of the problem is such that customer details are no more than a contact name / number for this QA purpose - other than that we really don't care who had the work done we aren't interested in sales or the like we simply calculate energy savings gained by installing loft and cavity wall insulation

plus there isn't any capacity for a customer to have multiple jobs done - you can only have your loft insulation done once! they could feasibly want loft and cavity wall insulation, maybe at separate times but we would want to treat these jobs as separate entitys and rather than the end customer we are actually more interested in who did the work at various stages of the chain and what government regulations it relates to (this is all handled by nicely normalised tables)

basically it seems to me it makes my life easier to keep job and customer together in a single record (and i have been told to do that) the data comes to us that way and will always be accessed that way but i can't help thinking that i'm breaking fundemental tenants of db design

I wouldn't worry about it. You're saying that the customer is not a logical entity in your design - it's just an attribute of a job. That seems fair enough to me.

great thanks a lot

it was something nagging me in the back of my mind - i'm actually pretty happy with my structure now

The only reason you would need a separate TBL_QAList is if this table is transactional i.e. one to many, otherwise there is no need to hold this information anywhere else but the job.

To answer your original question to stop updating records that are already populated you them to create a query with the tables table and then put an inner join between the two. Identify a field in the table that you want to update and in the criteria:

TBL_QAList.QAJob is Null

Simon

for the purposes of anyone else reading this looking for help like me i should probably extend on johnw's post by saying that the clincher for me is that we have no way of uniquly identifing people

if we had access to national insurance numbers or some such thing then maybe i could have had a persons table but we don't - the best we can do is a postcode which is an attribute of the property anyway

as it is we have no way to get a primary key for a persons table we could do this for a property by combining postcode and house number (and this is how we search for duplicates) but the property is so intimatly linked to the job by the nature of the data that there is no point really