Looking for general guidance for how to work this query

[Paprikaspice]

I am completely new to making access databases so I'm not sure if this is even feasible. I want a database that compares 2 excel files with data arranged like this:
Report 1:

ID	Name	Touchdowns
12	Tom Brady	1 4 8

Report 2:

Id	Name	Touchdowns
12	Tom Brady	13

The line breaks in report 1 are within the same cell and Chr(10). I want to make a query that matches the ID from report 1 and 2, and checks if the sum in the Touchdowns column in report 1 matches the number in the Touchdowns column in report 2. I'm also hoping to store this database on a shared drive and make macros so that anyone without any access experience can simply import the excel files and run it. My understanding is that VBA modules would not play well with that. I'd rather not have to use excel to clean the data up first or split/sum the cells in report 1 beforehand since that would make the process more difficult for colleagues.

Is this something that could work? I've found myself stuck at the step of somehow splitting or summing the cells in Report 1 in access.



Any general advice or specific ideas would be appreciated!

Thank you!

[ranman256]

the tables in access would want the data parsed into 2 tables as:

tPersons:
--------
ID, Name
12 Tom Brady

tTDs
---------
ID,TD
12, 1
12, 4
12, 8


then the query would sum them.

link the xl file as an external table,
then run queries to import the data parts to the correct tables.

[Paprikaspice]

Wow thanks! Linking as an external table is great.

Is there a way to split the the cells that way in access? Something similar to the split column function in excel? I can split the table in excel using split column but I was hoping to make it simpler.

[ssanfu]

Is there a way to split the the cells that way in access? Something similar to the split column function in excel?

No. But you can write a UDF (user defined function) that will parse the Excel cell and insert the values into an Access table.

[Bullschmidt]

Here is a calculated field in a query that takes data such as 1,4,8 in a field and uses the Replace() function to convert it to 1+4+8 and then uses the Eval() function to convert that into the number 13...

TotalTouchdownsCalc: Eval(Replace([Touchdowns],",","+"))

And here is some more info about the Eval() function...

Eval Function
Access for Office 365 Access 2019 Access 2016 Access 2013 Access 2010 Access 2007
https://support.office.com/en-us/art...8-5290da4d4166