2

I've done my research, spent hours on tutorials but couldn't find an easy to understand way to create what I need.

I'm new to Access but so eager to learn. I do tons of reports on Excel but tired of how slow it can be when too much data on my data sheet. So I shifted to Access.

I have 3 Excel files with one table in each.

1° 1st excel table has a list of campaign name and tracking codes that goes with it.
"Campaign-TrackCode"(exported from a software)
ex:
Country          Campaign name                     Track Code
US                  Campaign name 1                  us-ad-1-1
US                  Campaign name 1                  us-ad-2-1
US                  Campaign name 2                  us-ad-1-2
US                  Campaign name 2                  us-ad-2-2
US                  Campaign name 2                  us-ad-3-2
etc...

2° 2nd excel table has dates; registration ID and from which tracking code "Registrations"(exported from a software)
ex:
Date                Reg ID                                Track Code
01.01.2013     1021                                     us-ad-1-1
01.01.2013     1022                                     us-ad-1-1
01.01.2013     1023                                     us-ad-1-2
01.02.2013     1024                                     us-ad-4-2
01.02.2013     1025                                     us-ad-2-1
etc...

3° 3rd excel table is a report with dates, number of clicks, campaign name. "ggle Report"(exported from a ggle ads)
ex:
Date                            Campaign name                   Clicks
01.01.2013                  Campaign name 1                  12
01.01.2013                  Campaign name 1                  46
01.01.2013                  Campaign name 2                  16
01.02.2013                  Campaign name 2                  35
01.02.2013                  Campaign name 2                  23
etc...

And I'm trying to merge my 2nd and 3rd table together via the 1st one to get a table like this in Access:
Date                    Country              Campaign name           Count RegID          Clicks
01.01.2013          US                      Campaign name 1              21                         12
01.01.2013          US                      Campaign name 2        23                         35
01.02.2013          US                      Campaign name 1        22                         46
etc...

I'm not sure what to do. I've tried the table analyzer to split my 1st table into 3 tables (countries, campaign name and track code) then connect it to my second table via track code, then the 1st table connected to the 3rd table via campaigns and then finaly use the 1st table to connect the 2nd and the 3rd.

doesn't work. Since I'm a newbee, it would be great to have a step by step to create that final table. I'm a bit lost with primary keys, and unique values. (I'm starting to get one to many relationship so I think I'm on the right path to learn). I would like not to change my excel tables since the import in access each day is almost fully automated. I download the 3 reports and then in access I append my already imported tables according to the already saved rules to import those excel files.

Would someone know how to do this?

Here is a link with examples of the tables and the output needed:

example of tables

Brian Tompsett - 汤莱恩
  • 5,753
  • 72
  • 57
  • 129
JD76
  • 25
  • 5

1 Answers1

1

Can you try running this query (changing table/field names as appropriate) and see if that's it? I'll edit this answer later when we establish this is what you wanted to achieve. ;)

SELECT 
  Table2.Date, 
  Table1.Country, 
  Table1.[Campaign name], 
  Table2.[Track Code], 
  Table2.[Reg ID], 
  Table3.Clicks
FROM 
  Table3 INNER JOIN 
  (Table2 INNER JOIN Table1 ON Table2.[Track Code] = Table1.[Track Code]) ON 
  Table3.[Campaign name] = Table1.[Campaign name]
ORDER BY Table2.Date, Table1.Country, Table1.[Campaign name];
  • Copy this example SQL query above,
  • open your Access database,
  • click on Queries tab,
  • click on New icon,
  • select Design View,
  • close the Add tables dialog,
  • click on the SQL button (top left),
  • paste this code,
  • change names as needed,
  • click on the red exclamation point (Run) icon,
  • come back with your findings...

;)

EDIT: I've added sorting at the end of the query. You can of course change the sorting order by swapping fields listed in the ORDER BY clause, add some more, omit some, and if you want them to sort in descending order, add DESC at the end of the field name, separated by a space (e.g.: ORDER BY Table2.Date DESC, Table1.Country to have records sorted first by Date in descending order, and then by Country in ascending order ASC that is a default sorting order).

I also suggest you have a look at the built-in query builder (Design View) after you're done editing the SQL syntax. You'll find it's rather useful to build your own queries fast. Copied SQL clause will give you a nice example in how tables are related one to another in the WYSIWYG query builder. You can of course also change all the relations, sorting orders, display orders,... by simply moving things around. ;)

TildalWave
  • 1,677
  • 1
  • 14
  • 20
  • Hi @TildalWave, thanks for the answer.
    At first, it looked like what I needed.
    The SQL query did the right connections :
    Table 2 Table 1 Table 3
    – JD76 Jan 27 '13 at 17:24
  • OK, no problem, but can you please share with us what about my 'possible' solution is not the way you expect it to be? It will help us solve your problem. Your problem is at the fundamental level and you can expect thousands of people here willing and able to help, possibly with ease, but you will have to be a bit more precise in your question and/or subsequent comments. – TildalWave Jan 27 '13 at 17:29
  • yes thanks @TildalWave, I was about to give more info. just new on this forum and not sure how comment format works. So messed up my first answer.
    here it goes:
    The SQL query did the connections
    table 2 was connected to table 1 via the column Track Code and Table 3 was connected to table 1 via Campaign name.
    I had the right columns in the table but the dates, Campaign names, track codes, Reg ID and Clicks were not matching. But values seemed almost random.
     – JD76 Jan 27 '13 at 17:47
  • @JD76 - about formatting comments, questions: I suggest you don't format them initially at all, and just add formatting later as needed. You can find formatting rules and some examples even when editing your post by clicking on the `help` link bottom right to the input field. And no, it doesn't take HTML formatting for obvious reasons. ;) – TildalWave Jan 27 '13 at 18:09
  • thanks again @TildalWave, I could pull columns from table 2 and 3 to get my end table. But dates, Campaign names, track codes, Reg ID and Clicks were not matching. The values seemed almost random and duplicate. (still ramdom after setting in properties unique values yes)
     – JD76 Jan 27 '13 at 18:16
  • The end goal is to calculate the Cost per Aquisition(CPA) of a campaign.
    In my table from ggle ads (Table 3), I have dates, campaign names, Clicks and how much was spent.
    In my table from our system (Table 2), I have Dates, Registration IDs and the tracking code of the link they used to register which belong to a campaign.
    In my table 1, I have the tracking codes of each campaign. it's a list of campaign and 2 to 5 tracking codes for each campaign.
     – JD76 Jan 27 '13 at 18:16
  • So I was trying to have in my ggle ads table (table 3), a column at the end with the number of registrations per campaign per day (which i get from table 2 from our system).
    That way I know how much I've spent on a campaign and how many registration I got that day from that campaign. Which allows me to calcuate the CPA.
    That's why I was trying to connect my ggle ads table (table 3) to my system table (Table 2) via the table 1 which is the list of campaign and their tracking codes.
     – JD76 Jan 27 '13 at 18:18
  • The table 1 (the list) is the bridge between table 2 and table 3. The end table I'm trying to get is like this: From the ggle ads table: (Dates/Country/Campaign name/Clicks of the day/spent of the day/Number of registrations (pulled from table 2- our system). Hope it's clear enough. if not let me know, I'll try to be clearer. Thanks in advance. PS: I was expecting an answer with click on this and then click on that etc... but the SQL query was much better. I have few notions on SQL queries. so loved it. cheers – JD76 Jan 27 '13 at 18:19
  • Could you possibly open this query in `Design View` (top-left icon in the toolbar toggles the views) and have a go at the relations on your own? You can double-click on each relation and then simply select the value that matches your desired effect (they have descriptions). You can also change (add/remove/edit) any relations by simply dragging a field from one table to a field from another in `Design View`. I think you'll do it better on your own than us hunting for the proper solution to your problem by guessing who knows what and if description given was clear enough.;) – TildalWave Jan 27 '13 at 18:31
  • @JD76 - to delete a relation, simply select it with your mouse in `Design View` and hit DEL on your keyboard (or by using contextual menu with the right mouse button). – TildalWave Jan 27 '13 at 18:33
  • 1
    Thank you so much @TildalWave. It's already a great help. The connections are what I needed. Now I'll play around with the data, see if I can make it make sense. Cheers. – JD76 Jan 27 '13 at 18:47