Forum Discussion
Relationships and Junction Table to create - Nice Challenge!
- ValMayoJul 25, 2023Copper ContributorWOW!!!!! Thank you! Will try it out right now! And let you know.
- ValMayoJul 25, 2023Copper ContributorI am unable to duplicate the join types. Plus I am not always sure which one is primary key vs foreign key. Would it be possible for you to send me this DTB? I will add fields and start working with it to test it out. Thank you.
- ValMayoJul 25, 2023Copper ContributorSorry for so many replies! Just to let you know I have succeeded in duplicating it (watch a video on relationships). I will now add data and test. Thanks again!
- ValMayoJul 25, 2023Copper ContributorCan 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?- XPS35Jul 25, 2023Iron ContributorYou 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?
- ValMayoJul 27, 2023Copper Contributor
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.