best way to accomplish this (aggregate tables)

[TheShabz]

Hi everyone.

Here's my current situation. I have tables consisting of customer information for this year's media campaigns. They all have the same fields. I need to aggregate these into one table. The trick is that the same customer may have been campaigned in multiple times and I need to capture the earliest campaign and omit the later campaign.

For example:

tblCampaignJan1
CustID, EnrollmentID, CampaignDate
1, 123, 1/1/2011

tblCampaignFeb1
CustID, EnrollmentID, CampaignDate


1, 123, 2/1/2011

My aggregate table will need to only contain the record from the earliest campaign date. My current ideas involve multiple queries. is it possible to do this in one shot?

[pbaldy]

Well, it was likely a normalization mistake to have a table for each campaign. My guess is that you first need to use a UNION query to pull all the data together, and then this type of thing to get the earliest record for each customer:

http://www.baldyweb.com/LastValue.htm

If the data is really that simple, a basic totals query will let you pull out one record for each customer with the Min() date (based on the UNION query).

[TheShabz]

The table for each campaign is due to not using access to create the campaigns to begin with. each table is an excel sheet that was imported. I dont aggregate them together at the beginning because the totals for the campaign is over a million. the query to find the enrollments hit a table of 5mil+ joined with another table of 5mil+. The result of which balloons Access to over 2GB and kills the query.

The method in your link was what I was considering, just wanted to see if there was a way to combine the union with the Min() and have it go in one shot. Thanks

[pbaldy]

I'd probably still import the data into one table, though with that many records I'd use SQL Server for the data (there's a free version). That way you can use a stored procedure or pass through query and let the server do all the work.

[TheShabz]

I have the full version of SQL Server through MSDNAA. It's not "company IT approved" for my purposes, so I cant use it. Hell, if I had it may way, all these reports would be sitting on VB projects to be run on the fly and on demand by whoever wants them.

[pbaldy]

I like being both developer and IT Manager. All my requests get approved.

[TheShabz]

I'm still trying to get INSERT rights to a table so i can avoid a 5k record per upload limit through the web UI when I have campaigns of over 60k each. Got any openings? =P

[pbaldy]

I'm pretty much a department of 1, so an opening would mean that I fired myself. Sorry, no openings!

I sympathize with having to work around stupid restrictions though. I've had to do it in the past, and it's frustrating.