Query based on value ranges in other table

[amrut]

Here is the input
This is database to record boreholes drilled at various locations,dates. One client can have more than one type of contract. There is a clause in contract which specifies work rates for different hole depths.These details are in the ContractDetails table. For ex Work rate is 10 units per metre for bore holes upto a depth of 6 metres and 15 units per metre for bore holes above 6 metres depth.

There may be more than one location on a single day for a client. Workdetails table keeps record of the location and related bore hole data for that location.


I hope the table and relationships are correct. Please comment.

Below is summary of related tables :
Clients - ClientID(PK) and other details like name, address etc.
Contracts - ContractID(PK), Client(FK from above), ContractType
ContractDetails - ContractFK(FK from Contracts),QtyLimit, WorkRate
WorkMain - JobID(PK), ContractFK(FK from Contracts)
WorkDetails - LocationID(PK), JobCode(FK)
HoleData - HoleID(PK), LocationCode(FK from above), holeDepth and other fields

Problem -
How to create a query to show sum of borehole depths falling between the QtyLimits mentioned in the ContractDetails for any client on a work day?

[orange]

Do you have a sample database you can post -remove anything confidential?
Do you have a list of business rules/facts or specifications?
see the facts associated with this client/locations set up B,c and D sections(for concepts only)
http://www.databaseanswers.org/data_...ions/facts.htm

Also should include some test data.
Locations, jobs, holes,...

I see project (ProjectLocation) in the Client table - can a client only have 1 project?
Location is also in WorkDetails and HoleData -- maybe there are some relationship issues.
Sounds like there is/should be a table of ContractTypes

[amrut]

Thanks for your reply.
Below are the answers to the queries -
"Do you have a list of business rules/facts or specifications?" --------- The only rule is different work rates (per metre of bore hole) for different depth ranges
"I see project (ProjectLocation) in the Client table - can a client only have 1 project?" ------------ Yes (Project location in client table is referred in broad sense, for ex. say New York is Project Location and LocationName in workdetails will be the suburbs in New York.)
Location is also in WorkDetails and HoleData ----- Multiple locations on a single work date and multiple holes in a single location.
There is no table of contract types, but has only possible 3 values (added as value list in table itself)

[orange]

Did you see the sort of business facts/rules that were shown in the link I gave?
What about posting a "dumbed down"(remove confidential info) version of your database?

[amrut]

I have seen the link. In this case, very rarely a client has work in more than one city. Basic purpose of all the location related fields is to identify the client's work area. Only thing is that one client can have more than one contract for the same project.The database with sample data has been attached in mdb format.

[June7]

This would be easier if the QtyLimits were two fields in one record: LimitMin and LimitMax. Why not have those two fields in the Contracts table?

[amrut]

Thanks for the reply.

Why not have those two fields in the Contracts table?

Single contract has different work rates for different depths. Further,how to specify the contract ID for that job in WorkMain table ? All the bore hole data is in a single table. The upper and lower limits can be added to the ContractDetails table though. Your comments please.

[orange]

I would remove the lookups at the table level.

Seems from your relationships and data:
. a Client can have Many Contracts
. a Contract can have Many ContractDetails
. a Contract can have Many Jobs (WorkMain) (but your data shows 1 Job per Contract??)
. a Job can take place in Many Locations (Jobcode1 occurs in Loc1 and Loc2)
. a LocationName can apply to Many LocationIds(Loc1 refers to LocationId1 and LocationId19)
. a LocationCode can have Many Holes

It would be helpful if you could identify specifically what these things are/are not

LocationId
LocationName
LocationCode

My suspicion is that your model may not reflect your business, but that's just based on my confusion with the names and data.

[amrut]

. a Client can have Many Contracts --------------True
. a Contract can have Many ContractDetails ------ this table is required to add work rates for different depths as some clients may have single work rate while some may have three.
. a Contract can have Many Jobs (WorkMain) (but your data shows 1 Job per Contract??) -------- True Sample data shows 1 Job per Contract but a Contract can have Many Jobs (WorkMain)
. a Job can take place in Many Locations (Jobcode1 occurs in Loc1 and Loc2) ----------True
Boreholes are drilled at 2 locations on 1st April (Job ID#1) hence two records for that day in workdetails table. Jobcode & JobID refer to MS access FK and PK arrangement only, irrelevant to business terms. I always use ***ID as PK in Master table and ***Code as FK in child table.
. a LocationName can apply to Many LocationIds(Loc1 refers to LocationId1 and LocationId19)
. a LocationCode can have Many Holes --------True


LocationId-------this is only an Autonumber PK field required to connect with the child table (HoleData)
LocationName------- this is name of the small portion of land where the holes are drilled and is irrelevant to the work rates or any other fields. You can simply ignore it.
LocationCode----- this is the FK to join with the WorkDetails table

It is very obvious to get confused by the field names and appreciate your patience. I hope the concept is clear now.

[June7]

I tried to create a query that gets the ContractDetails records onto one row:

SELECT ContractDetails.ContractFK, ContractDetails.QtyLimit AS LowLimit, ContractDetails.WorkRate AS LowRate, Q1.QtyLimit AS HighLimit, Q1.WorkRate AS HighRate
FROM (SELECT ContractDetails.ContractFK, ContractDetails.QtyLimit, ContractDetails.WorkRate, [CID]-1 AS RecID
FROM ContractDetails) AS Q1 INNER JOIN ContractDetails ON Q1.RecID = ContractDetails.CID
WHERE (((ContractDetails.ContractFK)=[Q1].[ContractFK]));

It seemed to work until I realized that one of the contracts has 3 limit records. Since a contract can have any number of limit records, this could be tough. Back to the drawing board. How should additional limit records factor into calculations?

Does this help:

QryLowLimit
SELECT ContractDetails.ContractFK, Min(ContractDetails.QtyLimit) AS LowLimit
FROM ContractDetails
GROUP BY ContractDetails.ContractFK;

Now include that query in your existing query and use LowLimit field in your calculations.

[orange]

As I mentioned in my last post, I think there may be missing data/entities and/or missing business facts.
A flag goes up when I see comments such as

irrelevant to business terms.

.
In my view the data model should identify the business entities, and the business entities and attributes should be clearly defined. There shouldn't be any guessing of what the QtyLimit means. If there is a MinLimit and a MaxLimit, then those are attributes and should be represented.

Any way, good luck with the project.

[amrut]

Orange ,

irrelevant to business terms.

Sorry, I meant to say irrelevant for calculation purpose only.

June7,
I am not good at queries.I could not follow this one.

Now include that query in your existing query and use LowLimit field in your calculations.

Can populating a temporary table with ContractDetails records onto one row be used ?

[June7]

Save my suggested SQL as a query object then open your query in Design View and add my suggested query and join on the ContractFK fields.

Yes, I think ContractDetails transposed to one row would help if you want all QtyLimits available for the calculations. That's what I tried in my initial effort, which worked if there were only two QtyLimits per Contract. Don't see how a query alone can do this for more than two, need VBA.

[amrut]

This is the SQL-
SELECT Query1.ContractFK, QryLowlimit.LowLimit, Query1.LowLimit, Query1.HighLimit
FROM Query1 INNER JOIN QryLowlimit ON Query1.ContractFK = QryLowlimit.ContractFK;


The result for all contracts except for the one having three limits appear in a single row.

I will try the VBA way. Loop through the contractdetails recordset and populate the ContractDetails to a temporary table to one row as I want all QtyLimits available for the calculations.

Thanks for your help and suggestions.

[June7]

That query is not quite what I suggested. However, is irrelevent if you are going with VBA approach. Good Luck.