Query: Combining 2 Tables with Table1.FieldA = Table2.FieldA

[warhead92100]

Hi All,

I have 2 tables,
Table1:

Name
Account

Name1
Account1

Name1
Account2

Name2
Account1

Name2
Account2

Name3
Account1

Name3
Account2

Table2:

User

User1

User2

User3

I want to create Table3:

  
Name
Account
User

Name1
Account1
User1

Name1
Account2
User1

Name2
Account1
User2

Name2
Account2
User2

Name3
Account1
User3

Name3
Account2
User3

I tried to use:

SELECT 
Name,Account,Dlookup("[User]","Table2","User = '" & table1.[Name] & "'")
FROM table1,Table2

**Name matches User - both are text string


Seems to work but my 60 rows data (based on table1) becomes 200,000+

How can I a make a query that will return the specific row count as table1?

Also if there is a better way to do this as the 60 rows table1 takes 4 to 5 mins to complete and when applied to the actual data which has over 5000 rows, I had to crash access as it is taking forever.

[pbaldy]

You need a join between the two tables. In design view, click/drag to create a join between the two fields.

[warhead92100]

hi pblady,

i tried:

SELECT
table1.name,
table1.account,
table2.user
FROM table2 INNER JOIN table1
ON table1.name = table2.user;

however, this returns as empty table.

any guide on the correcting this join?

Thanks!

[June7]

The INNER JOIN should work. If you want to provide db for analysis, follow instructions at bottom of my post.

[pbaldy]

I'll get out of the way.

[warhead92100]

I am trying to recreate the database with dummy data and the join seems to work fine, but reapplying it to the actual data doesn't produce the same result.

do you have any idea what could cause this? all my data type are text.

Thanks!

****

Edit: the dummy data is now having problem as well, will post it now

[warhead92100]

here's the replication of the data.
DB_Analysis.accdb

[June7]

Data doesn't make sense. Both tables have duplicate names in the linking fields. How should Access know which records are to be associated?

[warhead92100]

the names would be identical, I want to match the names in table2 to names in table1 to get to the accounts they match. is that attainable in access?

[June7]

Certainly can join the tables but will the output make sense? For instance:

There are 5 records in table1 for Manager02, there are 2 records in table2 for Manager02. Joining these tables on the manager name fields results in 10 records for Manager02. Is that what you want?

In table1 is the ManagerID the ID for ReportingManager?

[warhead92100]

Yes the ManagerID are for the ReportingManager. I think I get where the increase in result are coming from, as you noted access returns all possible matches, am I correct to assume this?

so I filtered each account into queries and tried to use dlookup but it is returning increased results still. I googled around using the assumption above and found a code and tweaked it a bit:

Function IndexMatch(ByVal LookUpField As String, ByVal LookUpTable As String, ByVal ToMatchField As String, ByVal ToMatchTable As String) As String
Dim t As Long
With New CTimer
.StartCounter
    Dim dboCurrent As Database
    Dim rstQuery As Recordset
    Dim strSQLsyntax As String
    Dim recordsetcount As Recordset
    Dim strFind As String
    Dim strfound As String
    
    Set dboCurrent = CurrentDb()
    strSQLsyntax = "SELECT * FROM " & LookUpTable
    
    Set recordsetcount = dboCurrent.OpenRecordset(ToMatchTable)
    Set rstQuery = dboCurrent.OpenRecordset(strSQLsyntax)
    
    'recordsetcount.MoveFirst'
    'Do While Not (recordsetcount.EOF)
        strFind = recordsetcount.Fields(ToMatchField).Value
            rstQuery.MoveFirst
            Do While Not (rstQuery.EOF)
                If rstQuery.Fields(LookUpField).Value = strFind Then '
                    strfound = rstQuery.Fields("Account").Value
                    Exit Do
                Else
                    rstQuery.MoveNext
                End If
            Loop
            Debug.Print ' strFind & " " & strfound
        'recordsetcount.MoveNext
    'Loop
               
    IndexMatch = strfound
    Set recordsetcount = Nothing
    Set rstQuery = Nothing
    Set dboCurrent = Nothing
t = .TimeElapsed
End With
Debug.Print t
Debug.Print (t / 1000) / 60
End Function

The code seems to be faster than the dlookup, however it is stuck on the first record. Would you be able to advise how to reference the records so the code would move along the record set.

[warhead92100]

figured the udf above.

is anyone familiar with "sharepoint people or group", the accounts from the access table is the same as the domain\account of the managers but I am getting null violation upon linking the table to sharepoint. any idea?

Thanks!

[June7]

Sorry, no experience with SharePoint.

Don't know what data is in the queries/recordsets. Don't know what ToMatchField and LookupField fields are for. Don't see anything wrong with code logic.

Step debug. Refer to link at bottom of my post for debugging guidelines.