help with query

[cipo]

Hi i am creating a database where i want to have 3 tables:

• one with machine details including lift capacity
• One with bucket details including the weight of the bucket and the capaicty
• one with material details including the weight per cubic meter

I want to know if i can search using the machine a customer has and the material that they will be using to find the bucket that will be suitable for them. that fills the criteria of the combined weight of the bucket and the full capacity depending on the material.
This search needs to bring back multiple results as more than one bucket will be able to go on a given machine.


I was thinking of an iff function where the weight per cubic metre is multiplied by the capacity and added to teh weight of the bucket and if this is less than the lift capacity of the machine



however i am not sure how this would fit in with how i want to just search using the machine and the material




any help would be very much appreciated thank you

 

[Isskint]

This one has really grabbed my attention. I possibly have a similar scenario for working out which bodies and containers to assign to rigid demountable LGVs.

No iif statements needed. You need a query based on the buckets table. Add a calculated field to calculate the GWL (gross Working Load). This is simply the ([BucketCapacity]*Cubic weight of material)+[BucketWeight]. Then put a condition on the GWL < the machines lift capacity.
In the below SQL for a query, [Forms]![Form1] refers to a form where you select Machine type and material. Text6 is the material weight per m3 and text4 is the lifting capacity of the machine.

SELECT tblBuckets.BucketID, tblBuckets.BucketName, tblBuckets.BucketWeight, tblBuckets.BucketCapacity, ([BucketCapacity]*[Forms]![Form1]![Text6])+[BucketWeight] AS GWL
FROM tblBuckets
WHERE (((([BucketCapacity]*[Forms]![Form1]![Text6])+[BucketWeight])<[Forms]![Form1]![Text4]));

 

[cipo]

Thank you for that help on my tables is there any way that they need to be linked for this to work ? what relationships do the tables need to have

thanks again

 

[Isskint]

In the example there is no relationships in place. There are some potential relationships I can see;

• A particular bucket can only be attached to a particular machine
• A particular bucket can only lift a particular material
• A particular machine can only lift a particular material

If these scenarios play out then you would need to include a FK field in the relevant table to identify the match(s). That would make your problem a little more tricky to query, but you would just need to run a pre query to restrict the data the main query operates on.

 

[cipo]

Im not sure that i have put the code in correctly into the sql on the query i have :

SELECT Bucket.ID, Bucket.Bucket, Bucket.Capacity, Bucket.Weight, Bucket.[Dealer Price], Machine.Machine, Machine.[Maximum Lift Capacity (KG)], Material.Material, Material.[KG/M3], ([capacity]*[machine]![material]![Kg/M3])+[weight] AS GWL
FROM Bucket, Machine, Material
WHERE (((([capacity]*[machine]![material]![kg/m3])+[weight])<[machine]![material]![maximum lift capacity (KG)]));

it does bring up two search boxes but it does not find anything its the firs ttime i have wrote any sql so any extra help would be appreciated thank you

 

[Isskint]

I have duplicated your query with my demo DB and the query works!!

What 2 parts of the SQL are bringing up the search boxes? Or do you mean [machine]![material]![Kg/M3] AND [machine]![material]![maximum lift capacity (KG)] which I assume are controls on a form? If so, then you should not get prompted by the query. It would be handy if you could post the DB, but you do not have that ability yet.

Of course it is possible that the criteria you have entered means there is no bucket capable of performing the job!

Have a look at my demo DB and see if this makes sense?