How to LookUp from table within Fact(Summary)Table?

[jaryszek]

Hi,



this is the continuity from here:

https://www.accessforums.net/showthread.php?t=67142

there I am trying to create the best efficient relational database model.

Here i would like to find out the solution for lookup within the fact table.

I have summary table with IDs(not Uniques!) and a lot of attributes like: name and surname, sending date, employeeID, DocumentID,Client_Nr_FK etc.

and additionally i have second table filled manually by administrator - this is a lookup table or dictionary table you can call it like that.

ID (the same as in Fact Table), director, area and e-mail. ID is a field which should match with ID field in Summary Table.

And know i need a macro or query or something like that which will be checking all rows in my Fact table which are macthing with my lookup table values.

Example:

Fact Table:
UniqueID , ID , Name_And_Surname , Area , Sending Date, EmployeeID, DocumentID, Client_Nr_FK
1, 1001, Pawel X , Wroclaw, 2017-07-25 , 5 , 2 , 1
2, 1001, Pawel X , Wroclaw, EMPTY HERE, 5 , 2 , 1

LookUp table:
UniqueID, Director, Area, E-mail
1001, Michal Z, Poznań, a@interia.pl
1001, Michal Z, Warsaw , b@interia.pl
1001, Michal Y, Wroclaw, c@interia.pl

Area is a text value unfortunately, i could not force business to change it.
But it doesnt matter for now, i have to have clear idea in my mind how to solve this difficult technical problem.

So my macro should:
1. Check all rows if Sendind Date is empty (so only UniqueID = 2 in example above because Sending date is empty here)
2. If yes should be sending email (reminder) for specific criteria:
- find in lookup table exact machting ID
- check the area for found ID -if it is the same as in Fact Table --> send for given email

So here e-mail should be send for E-mail c@interia.pl because Area and ID is the same as in Fact table...

Every hint from you guys here will be most welcome,
Best Wishes,
Jacek Antek

[pbaldy]

First step, create a query and add both tables. Join them on those two fields. Add the fields you want returned, including the date field. Put this in the criteria of the date field:

Is Null

and see if that returns what you want. If so, open a recordset on it and send emails within a loop of that recordset.

[jaryszek]

Wow pbaldy your tip makes my day.

Sorry for late answer - i will write here if i do appropriate model and query.

Best Wishes,
Jacek Antek

[pbaldy]

No problem.

[jaryszek]

Okey,

first problem here.

I had join the tables as you wrote and problem is that my ID field is not only one with i should treat as criteria (or filter if you want to say that).

So i have one additionally field here - Area which is criterion.

So after combining these tables i have result table like below:

UniqueID , ID , Name_And_Surname , Area , Sending Date, EmployeeID, DocumentID, Client_Nr_FK
1, 1001, Pawel X , Wroclaw, 2017-07-25 , 5 , 2 , 1
2, 1001, Paweł X, Warszawa, 2017-07-25 , 5 , 2 , 2

With your solution i got rid of unnecessary IDs here.
Problem is that ID is connected with 2 different Areas.

I have to send email only for record with UniqueID = 1 because in my lookup table there is criteria (Client_Nr_FK) which corresponds to Client Name.
So in lookup table i have Client_Nr_FK = 1 and Area = Wroclaw so e-mail shoud be sent exactly to e-mail attached to it.

other words:
From fact table we know that Pawel X has Area for specific client : Wroclaw - so i have to somehow join our tables in order to get only record with Wroclaw criteria.
With inner join i can get only IDs which i need but i have to somehow do it additionally for Area criterion field.

In qlikview i would simple create the new unique key containing 2 fields: ID & Area and your solution will work here.
In Access i do not know it is good idea.

I have found the solution here:

https://stackoverflow.com/questions/...join-in-access

but i have an error joining when I am trying add new relationship between Area field in fact table and in lookup table.

How can i solve this additionally?
Create composite key within my query? How?

Best wishes,
Jacek Antek

[pbaldy]

You haven't shown your attempt or said what the error was, so I'm shooting in the dark here. Are the field in the join of compatible data types?

[jaryszek]

Thank you pbaldy for your help.

Ok i compacted database and this is working like a charm now.

My steps in order to do it:
1. I created Query "QryRemindersFirst" with all required fields:



Here I am taking all data from my Fact Table which i need.

2. I created Query "QryReminder" where I am taking all fields from lookup table and adding the name of company with relationship:



3. The last step I created with these 2 queries the last queries which is combining only these records which i want to show (with WHERE criteria):



4. This relationship give us result table like here (i had to change data in order to data protection):



This is awesome, thank you for you tip!

And now my VBA code can loop through all rows and send e-mail to each of user.

First new problem:
Now it is the hard part: i have to write code which will be looping and sending only one e-mail --> for example Adach Anna.
So Adach Anna has the same company, the same SAP_nr, the same Email and EmailDW. Only difference is document name.

How to show it, maybe delete repating records and leave only Documents?

Second new problem:
How to insert for each document name the reminder_date? Also use code and SQL commend insert into ?

Thank you for your help !
Best Wishes,
Jacek Antek

[pbaldy]

Have to run out for a while, but check out these two:

http://allenbrowne.com/func-concat.html

http://www.granite.ab.ca/access/email/recordsetloop.htm

The second has some extra stuff in it, but hopefully gets you started.

[jaryszek]

Hello pbaldy,

thank you very much !

Your links are very useful,
I have already handled it, i will describe my full database with example in another topic:

https://www.accessforums.net/showthr...758#post365758

Warm regards,
Jacek Antek

[pbaldy]

Happy to help!