Macros for Matching (1 Viewer)

[khaos09]

Hi,
I am new to the this forum and a beginner in Access. Here is the macro that I am looking for: If Last_Name, First_Name, and Incurred_Date matches, then input corresponding number in field called “Pairing.” Any help would be greatly appreciated! Thanks



Here is an example of the spreadsheet.
Last_Name First_Name Incurred_Date Pairing
Johnson James 1/1/12 1
Smith David 2/1/12 2
Johnson James 1/1/12 1
Johnson James 1/1/12 1
smith David 2/1/12 2

 

[plog]

This sounds like it could be solved with a simple query instead of a macro. If you need help buidling this query, provide the tables' names, relevant field names and some sample data from each table.

 

[khaos09]

Thanks for the response. I will gather the details and post it.

 

[khaos09]

Here are the details.
Table Name = Cost_Data

Sample file is attached.

What I am looking for is: If Last_NM, First_NM, and Incur_DT matches, then add a corresponding number to field "Pairing".

 

[plog]

There's an Excel portion of this forum. If you want to solve this with Excel, then I suggest you post your issue there.

 

[khaos09]

Sorry,
I exported the data into an excel. Here is the sample database in Access.

 

[plog]

If Last_Name, First_Name, and Incurred_Date matches, then input corresponding number in field called “Pairing"

What does 'corresponding' mean? It implies it is somewhere else and needs to be retrieved, however you have only one table in there and the Pairing field is populated.

Could you better explain the methodolgy? Also, include some sample data.

 

[khaos09]

The "Pairing" will be empty until the query or macro is ran. I populated the "Pairing" field to show you what I wanted to end up with. Apologies for not mentioning it. "Corresponding" means input the same number in "pairing" if Last_NM, First_NM, and Incur_DT matches.

 

[Adam Caramon]

I think you need to rethink your table design. It looks the purpose of the Pairing field is a unique identifier that is tied to the individual. However, this table design is far from optimal if that is the case.

Example: tblPeople, which contains all the details of the people, using PeopleID as a primary key. Then your other table, tblCostData, would have as one of its fields, PeopleIDFK as a foreign key.

I would try to address this now before you get too far along in your database design and realize that your whole database needs to be redesigned from the ground up.

 

[khaos09]

Thanks for the input Adam Caramon. This is a sample data that I pulled from my database. My actual database is setup similar to what you suggested. What I am trying to do is use the macro or query to clean my import table before I run an update macro. Currently, what I do is manually looking at the First_NM, Last_NM, and Incurred_DT, if those criteria matches, then I put matching number under the "Pairing" field.

 

[plog]

"Corresponding" means input the same number in "pairing" if Last_NM, First_NM, and Incur_DT matches.

I have no idea what that sentence is suppose to mean. From the looks of things you want a count of records with similar Last_NM, First_NM and Incur_DT field values.

If so, you would neither use a macro, nor store this data. You would create an aggregate query and retreive the data from that query when needed. This is the SQL for that query:

[FONT=Calibri]SELECT Last_NM, First_NM, Incur_DT, Count(ID) AS Pairing [/FONT][FONT=Calibri][SIZE=3]FROM Cost_Data [/SIZE][/FONT][FONT=Calibri][SIZE=3]GROUP BY  Last_NM, First_NM, Incur_DT;[/SIZE][/FONT]

Actually, looking over your data a fifth time, I'm not sure thats what you want. Could you explain exactly how you arrived at the pairing value? Especially for the David Stevens records.

 

[khaos09]

Sorry if its confusing. What I do manually to populate the "Pairing" field is look at the First_NM, Last_NM, and Incurred_DT, if those criteria matches, then I put matching numbers under the "Pairing" field. I am looking for a query or macro to do this automatically for me.

 

[plog]

What I do manually to populate the "Pairing" field is look at the First_NM, Last_NM, and Incurred_DT, if those criteria matches, then I put matching numbers under the "Pairing" field.

First, What criteria? Second, I quit. Hopefully someone else will come along to pull the methodology out of you and assist you.