consolidation query basics (Access newbie)

[miconian]

Hi All,

I'm quite experienced with Excel, but I'm just diving into Access, and despite having read a lot about it, I have no one to really guide me directly, so forgive the silly question.

I have a large database on an Excel worksheet, organized properly in columns. I'm moving it into Access because a) it's already so big that it slows down Excel, and will only get bigger, and b) I want to use Access to consolidate the data, much of which is redundant.

It's data about ad campaigns, and looks sort of like this (excuse my use of the code window for formatting purposes):

CAMPAIGN-NAME          CLIENT-NAME          CLIENT-ADDRESS    YEAR   MONTH             SPEND
JOE'S INITIATIVE           JOE                 123 SMITH ST.      2011    8           $100
JOE'S INITIATIVE           JOE                 123 SMITH ST.      2011    8           $300
JOE'S INITIATIVE           JOE                 123 SMITH ST.      2011    6           $300

As you can see, within each campaign, nothing changes except year, month, and spend.
I need to consolidate the campaigns so that I end up with something like this:

CAMPAIGN-NAME     YEAR     MONTH           SPEND
JOE'S INITIATIVE      2011       8            $100
JOE'S INITIATIVE      2011       6            $200

I understand conceptually that this is possible in Access (and should be easy), but am having trouble putting together the necessary queries. Any guidance would be greatly appreciated. I'm happy to provide more information... I have over-simplified the problem a bit in the interest of getting to the core of what I'm having trouble with.

Thanks!

 

[Norie]

You shouldn't consolidate this data, in fact you should do the opposite (sort of).

What I mean is that you need to split it, but not the records the columns/fields.

From what I see the first thing you need is a table for clients, this would consist of individual, unique records for each client.

Then a table for campaigns, assuming a client can have more than one campaign and finally a table for spend.

The campaign table would be linked to the client table, and the spend table to the campaign table.

With that set up you can do any consolidation, summing counting you need using queries.

 

[miconian]

Thanks, Norie. I am attempting to implement your suggestion now.

Slight complication: There is actually more than one field that needs to go in the client table: There is "client" and "parent company." For each campaign, these two are always one client-parent pair, but between different campaigns, any client may be matched with any parent.

I realize that the thing to do here is to create some sort of unique ID that is tied to the combination of parent and client in the initial table, and then bring that ID into the client table. Right?

So, in the client table, I did a 'find duplicates' query, and then I converted that query to a table, and deleted the duplicates count column. This gave me a table of unique client-parent combinations. I then added an auto-number field, which I can use as a key field in the client table.

However, now I need to somehow get that unique client ID into the original table (as a foreign key?), so that I can use it to link the client table to the campaign table, as you suggested.

I am having trouble figuring out how to do that. Any suggestions on translating my unique ID in the client table into a foreign key in the original table? Or am I approaching this the wrong way?

thanks!

 

[miconian]

Hmm... or maybe I ought to be creating the unique client ID field in the original master table, before breaking it up?

 

[Norie]

I thought there might be some more fields, can you post some 'real' example' data?

I'm not sure why you used a find duplicates query.

You should be able to create a parent table with a make table query with the source being the parent fields in the main table.

That's the table you should add the autonumber to.

You should also add an autonumber field to the client table.

Then what you need is a foreign key field in the client field for the client's parent's ID.

To fill that field you can use a query that joins the client table and the parent table to the main table

I hope you follow at least some of that, if you post some more data I'm sure I can explain better.

 

[Norie]

Don't create any unique IDs in the master table.

Not sure how you could do that anyway.

 

[miconian]

Thanks, Norie. Here are the actual fields (or, at least, equivalents that will make sense to you) that are in the original report. I have grouped them below according to what tables I imagine they should end up in.

The Client Table:
Parent
Client
Client Combo ID based on Parent-Client combination (primary key)

The Campaign Table:
Campaign ID (primary key)
Campaign Name
Date Of Campaign Creation
Corporate Or Brand?
Confidence Level
Sales Rep Who Sold The Campaign
Client Combo ID (foreign key)

The Website-Month Table:
Website
Month
Unique ID based on Website-Month combination (primary key)
Campaign ID (foreign key linking to Campaign Table)

The Money Table:
Year
Month
Net Amount
Website-Month Unique ID (foreign key linking to Website-Month table)

The Website-Month table exists because, at the end of the day, the reports will need to show the money breakout per campaign, per website, per month. But a campaign can run on more than one website in a month.

To clarify further, all this is in the service of creating a series of reports, one for each rep. Each report will have:

Name of rep
List of campaigns
For each campaign: client and parent, confidence, corporate/brand, creation date
For each campaign, broken out: website, month, year, money

Thanks so much for spending time on this!