Search results

Here is the join query select ID,EventID, Description, Expr2, Address, Date, Neighborhood from [VS - Progress Report (MTD) - A] union all select ID,EventID, Description, Expr2, Address, Date, Neighborhood from [VS - Progress Report (MTD) - B] It sources the data from 2 subqueries (VS -...

Hello, Is it possible to create a make-table query out of a join query? If yes, how do I incorporate the INTO statement in my join query? Whenever I change the type of the join query from SELECT to MAKE-TABLE, all my SQL statement gets deleted. Thanks in advance.

vbaInet, thank you for your time. Everything worked out well.

I hope it does. So the SQL will pull the list of unique names that have records from a new query and then attach concatenated records to those names (by means of the UDF from my initial query)? Thanks.

Sorry, I must have misinterpreted your statement Yes, [DVS - ACTW Persons] produces multiple rows per person (one per record). When I was reading the example provided by you I assumed that the input data to produce the sample table was similar to results of my initial query. The last code...

In my query [DVS - ACTW Persons] I have also rec_id (unique record number) along with name_id (unique person number). Can this cause the results I am getting?

Just cannot wrap my brains around it. :confused::confused::confused: I was following the example for table with company name and order dates in the link provided. Everything seems to be straight forward. When I omit the third argument I get all the records possible concatenated for each...

Third argument of the function in the link has it - "CompanyID = " & [CompanyID] Do I have to omit this?

This was in fact my initial try with the UDF suggested by you. I was using my query. The results of this SQL statement: full name, same number of rows per person (depending on the number of records per person) and concatenated records (concatenation worked fine) in separate field. However, I...

Thanks for your help. It worked but...it gave my the entire list of all names in the dbo_nmmain (334K). Obviously some of them have records, most - do not. I need somehow to filter out all the names (about 332K in my case) without Records.

It did not work and, frankly, I cannot see how it would :confused: As I said, I do not have Records in the Name table. By referring to only the initial Name table (dbo_nmmain in my case) I can only get lname, fname, mname, suffix. There is another table where I have Records (table dbo_wamain)...

vbaInet, you got me totally confused. Sorry about that. I really do not understand how I can use my query in the code and the Persons table in the SQL statement. I thought the code is a function that I am using in the SQL, and I am making references to tables/queries in the SQL code only. If...

Yes, I was getting errors with the previous code, but I used the one you suggested. The [DVS - ACTW Persons] query has a listing of persons and specific information pertaining to those persons (several records per person in most of the cases). The instructions to the code provided by you say...

Thank you very much for such a prompt response. When I used the UDF, I am still getting multiple rows (per same person exactly as I have in the feeding query [DVS - ACTW Persons]), the records are concatenated :) but repeated every row :(. The fields of my query are: name_id, Person's Name...

Dear experts, I have multiple records associated with the same person (name_id is a unique key per person). I need to create a list with one row per person and all records concatenated in that row. PersonA Record1 PersonA Record2 PersonB Record3 PersonB Record4 PersonC Record5 into PersonA...

VbaInet, thank you very much! I changed = to Like and everything worked fine. Still cannot understand though why my previous syntax was able to pick up all misspelled city names (with an exception of one instance), if ="PORTS*" calls for exact match only.

That's right. I was hoping my IIF function would pick up everything. It works on all misspelled entries but the one I mentioned before. It is literally just one entry. 1. Can't I combine finding those LIKE "PORTS*" and replacing them with PORTSMOUTH in one IIF function? 2. What is the...

I need the IIF() function in my expression to replace all entries (including misspelled ones) with the correct city name, while keeping the others unchanged. So, it is not just about filtering out entries that do not meet the criteria.

Thanks for the prompt reply. I saw it but thought there is no difference between = "PORTS*" and Like "PORTS*". Should it be than IIf(Trim([dbo_nmmain].[city]) like "PORTS*","Portsmouth",[dbo_nmmain].[city])?

Dear Access experts, I have a query where I want to distinguish between cases happened in my city versus others. There are multiple ways one can misspell that, so I am using wildcard search for this: IIf(Trim([dbo_nmmain].[city])="PORTS*","Portsmouth",[dbo_nmmain].[city])This works fine for all...