Relationships and Junction Table to create - Nice Challenge!

[ValMayo]

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 relationship with Enforce Integrity + Cascade updates… THANK YOU!

[#2 · Accepted solution]

@ValMayo What about

[#3]

WOW!!!!! Thank you! Will try it out right now! And let you know.

[#4]

I 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.

[#5]

@ValMayo Here it is.

[#6]

Sorry 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!

[#7]

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?

[#8]

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?

[#9]

@XPS35 

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.

[#10]

@ValMayo If rates depend on Proces and Location, you should create a rate table with foreign keys to both Proces and Location, and store the rate there. 

[#11]

Sounds great! Can you assist me in doing that? It is ONE field in location but THREE fields in rate... really unsure how to do this...

[#12]

@ValMayo 

[#13]

Thank you! I will work with this and add data...