I have 2 tables:
1) Data Pull-Table
- AcctDate
- Location
- Property
2) Property-Table
- LOC
- BeginDate
- EndDate
- OSShare (which is the percentage)
I need access to find the Percentage based on:
- Location must match LOC
- AcctDate must be between BeginDate and EndDate
Example: Table 1: the Acct Date = 8/2017
Table 2: has 3 entries for Location/LOC 0124
- BeginDate: 1/2015 & EndDate: 3/2015 at 33.687%
- BeginDate: 4/2015 & EndDate: 5/2015 at 36%
- BeginDate: 6/2015 & EndDate: 1/9999 at 38.5973%
Correct Answer is 38.5973% (If it can't find the LOC then the % = 0)
I created a join between Location and LOC as "includes all records from Table 1 and only those records for Table 2 where there is a match".
I created and IF formula but the results are creating records record in Table 2.
Example Results:
- BeginDate: 1/2015 & EndDate: 3/2015 is 0%
- BeginDate: 4/2015 & EndDate: 5/2015 at 0%
- BeginDate: 6/2015 & EndDate: 1/9999 at 38.5973%
Here's what the SQL looks like:
SELECT [Copy Of 02-1_Acct Recon 232010-Data Pull].Location, [Copy Of 02-1_Acct Recon 232010-Data Pull].AcctDate, [Copy Of 02-1_Acct Recon 232010-Data Pull].Location, [Property Listing with Percentages].BeginDate, [Property Listing with Percentages].EndDate, IIf([Location]=[LOC] And [AcctDate]>=[BeginDate] And [AcctDate]<=[EndDate],[OSShare],0) AS OSSharePercent
FROM [Copy Of 02-1_Acct Recon 232010-Data Pull] LEFT JOIN [Property Listing with Percentages] ON [Copy Of 02-1_Acct Recon 232010-Data Pull].Location = [Property Listing with Percentages].LOC;
How do I fix this?