How to do this in Access

[accessmatt]

Hello, could someone please look over this idea and comment as required. I'd like to setup an import query tied to specific Excel files which will be updated each month. Then, as those files come in, I'd like to, in an automated way, combine these fields in the following way:

• Two Text Fields with an Output of a Third Text Field (a Concatenation in Excel)
• Sum Three Fields with an Output of a Fourth Number Field (a Sum In Excel)

Does this all sound feasible and if so, how would I go about executing this plan? I know how to use the Saved Imports feature but is it possible to add two new calculated columns (fields) in the database without breaking the automation? Also, how can I combine two text fields (like Excel's Concatenation feature).

Thanks!

[June7]

I don't think the Excel structure can be modified and those mods be reflected in table link or Saved Import Specification.

Why would new columns be created? Why not do calcs in Access? Why involve Excel at all?

[accessmatt]

Hi June7,

In fact I mean to say how can I do this soley in Access. I don't want to involve Excel. I would have to create new fields because I have 2 tables of data (2 files) which, in order to join them, I need to make a common connection. The only way I can do that is to combine some of the fields in each. This is what I'm asking how to do in Access... Thanks!

[June7]

You said you'd like to do a monthly import from Excel. I still wonder why Excel is involved. How does this data get into Excel to begin with?

Sounds like you have compound unique identifier - multiple fields uniquely identify each record. Query can join records on multiple fields.

[accessmatt]

June7, that is correct: multiple fields uniquely identify each record. The data comes to us in 2 Excels each month. My job is to q/a it for reconciliation each month and I think this could be automated easily through Access provided I can solve the challenges of creating those two new fields mentioned in my original query. That's where I'm hoping members of this forum could help out

[June7]

Need some sample data. As I said, queries can join on multiple fields.

Queries can be structured to analyze what data is in one table but not in another.

Queries can be joined to queries.

Queries can do calculations.

Access can link to or import from Excel sheets. Links can be used like tables (except can't edit data).

[accessmatt]

June7, attached are the two data files basically I want to compare impressions, clicks, cost from one file against the other. This would be done per month and per brand. Let me know if you have any thoughts about how to structure the db.

[June7]

What do you want to q/a in each file? Do you want to make sure there is 1-to-1 relationship - that all records each table match another record in other table?

Set links to each sheet. Build 2 Find Unmatched queries - there is a wizard for that. The queries will join on the 3 fields common to both sheets. Doesn't matter that the date field name is different, the data is common to both.

[accessmatt]

Hi June,

I want to q/a impressions clicks and cost per month and ad type. I like where you're going with the Find Unmatched queries but I tried a simple test on just the website field and I don't think it worked because it returned blanks. See attached.Database71.accdb

[June7]

If the query returned nothing that indicates every record in Paid has a match in SEM.

I reversed the query and set the website field in Paid to Is Null and 277 records are returned.

Why are there records with no data? Have to disregard those records.

[accessmatt]

June7,

Thanks, I deleted the null rows to keep this example clean however the query isn't working as expected. The Paid Media Output has "0s" for Impressions, Clicks, Cost while the SEM Report has data for those values. I would expect them to come up as unmatched.

[June7]

The query is matching on only the website fields. If you want to check for match on other fields as well, they must be included in the query.

[accessmatt]

Thank you June7, I did get it to work however Access limited me to only matching on one field when I went through the wizard. Do I have to modify the SQL directly to add more fields?

[June7]

Actually, the wizard does allow multiple pairs of fields. Select field in each table, click the <=> button. Repeat for next pair. However, you can manually build the query. Once you understand the structure, not too difficult.

[accessmatt]

Thank You June7 you've been a huge help. However, I've tried the wizard a few times and it won't let me add multiple pairs through the wizard. Could you help with the custom SQL?

SELECT [Paid Media Output].Website, [Paid Media Output].Month, [Paid Media Output].Impressions, [Paid Media Output].Clicks, [Paid Media Output].CostFROM [Paid Media Output] LEFT JOIN [SEM Portfolio Report] ON [Paid Media Output].[Clicks] = [SEM Portfolio Report].[Clicks]
WHERE ((([SEM Portfolio Report].Clicks) Is Null));