Forum Discussion
Solved
Relationships and Junction Table to create - Nice Challenge!
Hi! Intermediate user but this is beyond my pay grade! Help! I need help to set up relationships and would like a Junction Table – would make the production of reports much easier. I would like r...
Can you add a rates tbl? It contains: the type of process (Service, Service at Install...), the Process description, the type of rate (flat, hourly, by SQFT), and the amounts paid, according to the building type (what you called here "PayRate" in the Location table) so 3 currency fields: RateLR, RateHR, RateCT (low rise, high rise or custom building/property types).
Unsure where to add this... does it change anything else?
Unsure where to add this... does it change anything else?
- You stated that “all payrates are pre-defined upon the loacation’s property type”. So that is why I added PayRate to Location. Now it is different?
Yes - but prices are all different according to what process (the job) was done. Let's make up an example: Joe works on June 18, he install 10 counters which is process 12. The LOCATION where he worked was say 11211 Riverside Drive is a property that is a High Rise building. So Joe will get paid for process #12 x 10 (I will do the calculations on a report) - for the sum of $5 an hour.
Now, John, on that same day, does the same job, even 10 times like Joe BUT he worked at a location (a property/a building) that is a Low Rise - therefore Joe is not paid $5 per hour but $10 per hour. So, same job, same day or other day, get paid different money (at a flat rate, hourly rate or by SQFT) according to the BUILDING type they worked in. Each Process (job description) has a unique $rate according to the building type WHERE the duties were performed.Now here is how I linked the PropertyRate (LR, HR, CT) in the Location's table to ONE of the following Rate table's fields: RateLR, RatesHr, RatesCT :
In the Invoice report query, I created:
Amount: (IIf([TS_LocPropType]="LR" And [TS_Hrs] Is Null,[TS_Qty]*[RatesLR],IIf([TS_LocPropType]="CT" And [TS_Hrs] Is Null,[TS_Qty]*[RatesCT],IIf([TS_LocPropType]="HR" And [TS_Hrs] Is Null,[TS_Qty]*[RatesHR],[AmountHourly]))))and
AmountHourly: (IIf([TS_Hrs] Is Not Null And [TS_LocPropType]="HR",[TS_Hrs]*[RatesHR],IIf([TS_Hrs] Is Not Null And [TS_LocPropType]="LR",[TS_Hrs]*[RatesLR],IIf([TS_Hrs] Is Not Null And [TS_LocPropType]="CT",[TS_Hrs]*[RatesCT],0))))
But maybe you have a "cleaner" way to do this... And, just FYI, took me 4+hours looking at previous databases from various sources to create that coding! I have never been able to write codes (have no schooling) but I am pretty good at reading, deciphering and copying codes! Hope this helps! Very sorry if I mislead you in any way. Sometimes, as English is my 2nd language, I do explain things poorly. THANK YOU! I look 3-5 times a day to see your response - I really look forward to hearing from you!
My ultimate goal would be to have the forms in that database (or new forms) be on a website for all workers who are finding this billing so confusing and difficult.
