Query Builder Help (1 Viewer)

[firozmoidhu]

Hi Experts,

am struggling to create below result

i want to Multiply one value with another say A X B (To Identify "B" There are few Parameters) For Ex:

B Called Machine Rates- If Machine is Fixed Monthly Rental plan Then "B" will be Monthly rental amount/Number of Days in the Particular Month which machine had worked. If Machine rate is Variable then rate will be based on hourly rate specified on the table. so I Have Listed Table Like Rates for each machine & another Table for Machine Hours with identical on both table with Machine ID. Let me write one more scenario for better understanding

If X Machine is Fixed Rental Amount ($1000) & it worked up 20th Jun then Cost will be 1000/30*20 = $666.67 - (Two Decimal R/O Required), If in May then $645.16.

If Y Machine is Variable Rental (Two Monthly Rates are Available Single Shift / Double Shift- 1000/2000 respectively), If machine worked in Jun 100 hours as single Shift and 200 hours Double Shift then it should be 100 * 1000/260 & 200*1800/520. (Here 260 & 520 is constant fraction for Single & Double Shift)= $1076.93.

Please help with solution as earliest as possible.

Thanks.

 

[June7]

Provide data for us to work with - build some tables in post or if you want to provide db for analysis, follow instructions at bottom of my post.

 

[firozmoidhu]

Hi June7, Thanks alot for your time, i have attached here as you requested. Please assist me on resolving This

 

[theDBguy]

Hi. Welcome to AWF!

I agree. Some sample data and table/field descriptions would be helpful.

Edit: Oops, a little too slow...

 

[June7]

Makes no sense to link ResourceID fields as PK and FK because they do not have same values. Records are not related. There is no way to associate equipment with hours records. Appear to be saving wrong value in EquipmentHours.

Advise not to use spaces nor punctuation/special characters in naming convention.

 

[mike60smart]

I agree with June, the database makes no sense whatsoever.

 

[firozmoidhu]

Makes no sense to link Resou

see Query2.

Thanks Arnel, I liked It works but it looks more tables to calculate EOMONTH, think it could be avoided if we can tune in modules isn't it.

use they do not have same values. Records are not related. There is no way to associate equipment with hours records. Appear to be saving wrong value in EquipmentHours.

Advise not to use spaces nor punctuation/special characters in naming convention.

 

[firozmoidhu]

Makes no sense to link ResourceID fields as PK and FK because they do not have same values. Records are not related. There is no way to associate equipment with hours records. Appear to be saving wrong value in EquipmentHours.

Advise not to use spaces nor punctuation/special characters in naming convention.

Hi June & Mike, I got you. can you please check attached one here

 

[June7]

ResourceID is set as primary key in EquipmentDetails and RatesRevised. So why not just have one table?

Otherwise, normalize RatesRevised and ResourceID will be a foreign key in RatesRevised. Or normalize and don't have ResourceID in RatesRevised and instead have a unique identifier (autonumber) in RatesRevised and save that value as foreign key in EquipmentHours.

Your calculations may be complex enough to need a VBA custom function.

 

[firozmoidhu]

ResourceID is set as primary key in EquipmentDetails and RatesRevised. So why not just have one table?

Otherwise, normalize RatesRevised and ResourceID will be a foreign key in RatesRevised. Or normalize and don't have ResourceID in RatesRevised and instead have a unique identifier (autonumber) in RatesRevised and save that value as foreign key in EquipmentHours.

Your calculations may be complex enough to need a VBA custom function.

Two Tables data entered by two different users on day to day basis.

Resource ID is each machine's ID which should be reflected in report. we have few more table to be associated with resource ID. My knowledge in access is very basic currently work in excel but due to linking worksheet & data size the file is getting heavier & hanging some times. so just want to get those workings take to Access.

 

[June7]

Still can be one table as long as they don't edit same record at same time

Is actual database split design? Should be if there are multiple simultaneous users.

The more I look at this, the more complicated it appears. Will take more time than I wish to dedicate. Maybe someone else will provide input or you may have to find a professional consultant.