Problem Problem!

[skea]

Attached is a sample of my db with tables,queries and a report.
The Problem is deep but i will try to explain.
Some of the contacts work in more than one field office implying that they work in either Work_District1 or Work_District1 or Work_District1 or two of them or three of them.(e.g Mr Joseph Oneka who works in Lira,Gulu and Pader as in the Contacts Table)

As in the image,Work_District1 and Work_District2 and Work_District3 are meant to have an inner join in the query(qryRepOrgsByDist) because they are connected but this can't be done with an inner join (inner joins are 1-1 and not 1-M).This gives me no results, so my ultimate goal here is bieng blown off.

My Test is with UN/FAO which is in 6 Field Offices and Contact(Joseph Oneka who works in Gulu,lira and Pader) and fieldoffice Districts Kitgum , Gulu and Pader.

What i want is that in my Report, if a field(Work_District1 or Work_District2 or Work_District3 ) is null, then the record of that section shouldnt appear.
I have tried to use the formattting i commented out in the code but it was failing.
As you see in query(qryRepOrgsByDist2) which is the Record source of my report, i have criteria (Like "* pader *" Or Like "*gulu*" Or Like "* kitgum *"), but the record of Joseph Oneka in Lira shows up.

Any help is highly appreciated.

 

[neileg]

Your question is not clear. What does this mean What i want is that in my Report, if a field(Work_District1 or Work_District2 or Work_District3 ) is null, then the record of that section shouldnt appear.

Your design is not normailised, which is part of the problem. Work_District1 or Work_District2 and Work_District3 is a repeating group and should be three records in a separate table, not three fields in this one.

qryRepOrgsByDist2 doesn't work as you expect because you have put the criteria on separate lines. This makes them 'or' tests. If you want both to be true you need to put them on one line to 'and' the criteria.

 

[skea]

Thanks very much Neileg.
As i quoted before, the question would be hard for any one to understand. Anyway the work districts bieng in the same table make it look de-normalised but thats the way it has to be because some of the guys work in more than one district or rather field Office.

As per the moment iam thinking of creating sepatate tables for each district with NewStaffID as a forign key.... but it will be an over haul.

 

[Len Boorman]

Anyway the work districts bieng in the same table make it look de-normalised but thats the way it has to be because some of the guys work in more than one district or rather field Office.

No. It is not Normalised and it does not have to be that way. Creating tables for each district will probably make bad matters worse.

So you have a people table, A District table and a people_District table.

PK from People and PK from District are FK's in people_District and also the pair make the PK in people_District.

Normalised.

A person can now be associated with 1 or more districts, no Null values in the tables and life with queries will become easier, quicker and simpler.

Len

 

[skea]

Thanks. I will give that a go.

 

[skea]

No no no,
thanks neileg and Len Boorman but your ideas kept me lingering about my db normalisation scheme which was not worth the time spent. The only thing i had to do was to change my query to look as in the image.That gave me the results i wanted.

 

[Pat Hartman]

As has already been pointed out, your design is incorrect. You need to do some reading on normalization.

Also, the join between contacts and tblFieldOffice is not on the correct field. The FieldOfficeID is the primary key to the tblFieldOffice and so THAT is the field that needs to be in the contacts table. So remove NewOrg_ID and replace it with FieldOfficeID. Of course, you might have had to do this because of the mistake you made with work district.

 

[skea]

Thanks Pat,i guess when you see a db that looks de-normalised, it implies that once...at some time,normalisation had been carried out.
Putting the FieldOfficeID in the contacts table is a very very good idea from you....and i think i have been overlooking it. I will give it a go,only that i cannot remove NewORG_ID from the Contacts table.Its the base.
Any way, my work district fields bieng in the same table with contacts really make the db look de-normalised but thats the only way i thought i would go. And i thought any thing apart from that would be an over-haul and had since failed the tests.I guess i had reached a confidency normalisation level which led me to de-normalise it so as to reap early performance benefits.

I conquer with you that the join between contacts and tblFieldOffice is not on the correct field, actually it should have been between the Organisation and the Field Office table as in the image below. I think i will leave it as that if your idea fails me out.
Otherwise thanks.

 

[neileg]

Thanks Pat,i guess when you see a db that looks de-normalised, it implies that once...at some time,normalisation had been carried out.

Any way, my work district fields bieng in the same table with contacts really make the db look de-normalised but thats the only way i thought i would go. And i thought any thing apart from that would be an over-haul and had since failed the tests.I guess i had reached a confidency normalisation level which led me to de-normalise it so as to reap early performance benefits.

De-normalisation does not produce performance benefits, quite the reverse. I had similar view to you, but time and experience has shown that normalisation is always the way to go. I cringe now when I have to go back and amend an Access application that I built before I saw the light.

If you persist with a bad design, you will continue to expereince problems that would not have existed with a good design.

 

[skea]

Yap...i guess thats why i am reaping some pain~ in the a**.
The DB has lasted for some time now and it captures lots and lots of staff, that i had to develop other separate dBs and just link them to it. i think you get the picture...