Join and Loop thru second table & generate new table for each row

[Nilou]

Hi experts,

I'm very new to access and VBA, I am Looking to find a way to join two tables.
To join these two table, I need to loop through my second table and for each row exist in second table I create and insert the matching result from first table into new table.

is there an easy way to do it?

Thank you

[twinnyfo]

Are you talking about creating a "third" table, which only contains values that are contained in both tables?

It is difficult to know exactly what you want, since you don't describe anything that is in your two tables. I can guess that there is a way to do this in VBA, or even with a simple query, but since you provide very little information, it is impossible to provide further advice.

[Nilou]

Hi thank you very much,
I have two tables that need to join base on some criteria.

table 1 loop thru table 2 record set and create a new report for each row and export into excel sheet.

I get Run-time error 3085 undefined function 'rstSKSF' in expression and point out this line:

[twinnyfo]

You are referring to your recordset improperly, as you are using it in your query def as a function, not as a recordset. At a minimum, you should refer to your recordset values like this:

Report.[Asset Title] = '" & rstSKSF("Course Name") & "'"

However, I still don't think it is possible to use a recordset in this way. A recordset is a bit different from a table. I'm not sure it can be joined in the same way. Any other experts know how this can be done? I may have just not ever come across this before.

[Seth Schrock]

You can't join a query to a recordset. A recordset is only a virtual table in VBA. You would just have pass the values from the recordset into the query string as twinnyfo has demonstrated. I don't have any sample data to test this on, but I think that it will work.

With rstSKSF
      Do Until .EOF
 
      'join report table with SKSF_request table's Rows
      'Create newworksheet for each report joint with SKSF rows
            Set qdf = dbsReport.CreateQueryDef("Training_Reportsheet", _
            "SELECT Name, [Employee Role], [Employee Location], [Retails Region], " & _
	        "[Asset Title], [Completion Date], [Completion Stat] " & _
	        "FROM Report " & _ 
	        "WHERE [Asset Title] = " & ![Course Name] " & _
	        "And " & !Role & " Like ' * ' & [Report].[Employee Role] & ' * ' "
 
            DoCmd.TransferSpreadsheet acExport, acSpreadsheetTypeExcel12Xml, "Training_Reportsheet", xlsxPath, True
            DoCmd.DeleteObject acQuery, "Training_Reportsheet"
 
 
         .Edit
          rstSKSF![Flag] = "Y" 'Set Flag
         .Update
         .MoveNext
 
      Loop
   End With

Your query had a GROUP BY clause, but you didn't have any aggregate functions (Count, Sum, etc.) so that doesn't do anything for you.

In your original code, you had referenced rstSKSF(SKSF_Re q.[Course Name], rstSKSF(SKSF_Re q.Role), and rstSKSF([SKSF_RequestFor m].[Course Name]). What are the SKSF_Req and SKSF_RequestFor m prefixes for? I'm assuming that Course Name and Role are the field names and I see that SKSF_Req is the name of the table that your recordset is based on, but I can't figure out where the SKSF_RequestFor m comes from.

[Nilou]

My Sample File

Hi I have attached my file, Please see if you can figure it out ?

Thank you

[NeoPa]

Seth Schrock:
Your query had a GROUP BY clause, but you didn't have any aggregate functions (Count, Sum, etc.) so that doesn't do anything for you.

Not exactly true Seth :-( It can be used as a clumsy way of specifying that only unique records are required. If all fields used are in the GROUP BY clause then it essentially ignores duplicates. Not nice - but does have that effect.

Nilou:
Hi I have attached my file, Please see if you can figure it out ?

This is not a good move.

1. When we want an attachment from you we will ask for it. We are unlikely to be prepared to download anything from you unless we've asked for it first.
2. It's not our responsibility to figure out the question for you. That's down to you. I can see that expressing the problem clearly in English is not easy for you, so we can let it slide for now, but if I explain that such a comment is understood to be disrespectful then I'm sure you appreciate why it's not a good idea.

I can't speak for others here, but I don't understand your question very well at all. Before I spend time interpreting what the code is doing and what the question should be, I need to know that you've covered all the points in Before Posting (VBA or SQL) Code. Let me know when you have and the posted code matches that level and I'll look more closely (Always assuming you want me to of course).

[Nilou]

thanks

Okay and thank you.....I figured it out.
DAO doesn't take `"' Like ' * ' & [Report].[Employee Role] & ' * '` and I was reading from query and had to change it to real table in s`trSQL = "SELECT * FROM SKSF_Req WHERE Flag IS NULL"` Statement.