Search results

Good Morning, I have a data entry form feeding a table named [Group Members] using Access 2010. It has 4 fields; [Group ID] & [Group Name] derived from tbl.[Groups] and [Member ID] & [Member Name] derived from tbl.[Mail List]. The 'ID' fields are foreign keys from the respective tables. The 2...

I have a form entitled 'Group Mmebers Entry Form' feeding a 'Group Members' table, containing only 4 fields. The data sheet of the table contains 566 records, yet the entry form contains 618, including 52 Null Records. I can delete all of the Null records in the Entry Form and save it, but when...

Paul, Have you had the opportunity to look at my cut down db yet? Regards, jcbhydro

Eureka! I thought I had caught all of the 'Address 1' bracketss but missed the final one. Thank you so much for your persistence in achieving a successful outcome. I have learned a great deal about coding from your patient efforts. Kind regards jcbhydro

We still have a syntax error, I'm afraid, with everything after the 4th line leading bracket highlighted. I am inserting the code into a totally blank SQL pane with all earlier code deleted. jcbhydro

I feel rather embarrassed about the trouble I am putting you to, but I am exremely grateful for your efforts. Here is your code as I copies and pasted it into my Query SQL pane. I still get the 'syntax error' woth the first " &_" highlighted. Select a.[Member ID], a.[Address 1] " & _" From...

It still doesn't run, I'm afraid. I did a copy and paste of your most recent coding with no success. I noted that the trailing speech marks were missing from most of the " &_" expressions,so I added those, but I still had a syntax error 'missing operator in query expression 'a.(Address 1) "...

No, the spaces are there as pasted from your coding. Select [Member ID], [Address1] From [Mail List] AS a " & _ "On cStr(a.[Member ID]) + a.[Address 1] = " & _ "(Select Min(cStr(b.[Member ID]) + b.[Address 1]) " & _ "From [Mail List] AS b " & _ "Where b.[Member...

Brian, I am definitely inserting the code in the SQL pane of my query. This time I did a copy & paste directly from within the quote marks of your amended code and received the same syntax error in FROM Clause with "&_" highlighted. What can be the problem? jcbhydro

Many thanks for your further efforts. The PK is [Member ID] and I have incorporated this in your code as follows; Select [Member ID], [Address1] From [Mail List] AS a On cStr(a.[Member ID]) + a.[Address 1] = (Select Min(cStr(b.[Member ID]) + b.[Address 1])...

Paul, I thought that I had uploaded a 'cut down' version of my database to this thread, but it hasn't appeared. I will try again. Although I have followed the recommendations for a [cut down' version it is still pretty large. jcbhydro

Dear MSAccessRookie, I would love to try your suggested coding, but I'm much more of a 'rookie' than you and can't see how to translate it to my needs. My membership table is named [Mail List] and contains records of members contact details. The first field of the postal address is named...

Thank you Brian, Your last piece of coding works beautifully. I can now sum 3 or 4 alphanumeric post code localities as required. Regards, jcbhydro

Yes Brian, I do NOT want to Group all post codes. You will know that the first part of a UK Post Code indicates the locality and the 2nd part a selection of residences, which can be anywhere between 1 & 30 residences. I am wanting to determine the total number of members in particular...

Good Morning, I have a query to count the number of society members in a particular UK Postcode area. It works well for a single Postcode reference but adding further references to the Query Design View generates an 'or' or 'and' statement, rather than the additional counts that I need. The...

Nigel, I have been away from home for a few weeks but I am still keen to achieve the object previously outlined. I am confident that your proposed coding will achieve the object but in my inexperience I have failed to translate your sample coding to work with my database structure. If I define...

Paul, I have doctored the file to remove all irrelevant forms, queries and reports, leaving only the main table 'Mail List' plus associated 'Group' tables and a single 'Member Entry' Form. This form is where I would expect to automatically increment the Member ID number. I have removed all but...

I regret that in my novice Access status I don't understand the solution proposed by MSAccessRookie. I have a members table named 'Mail List', holding details of Society members including address details. The 1st address field is named 'Address1'. I have a label printing query which selects...

Paul, I have tried writing criteria in the Design View of my Query using the 'Group By' or the 'Distinct' expressions but without success. I have tried; SELECT DISTINCT [Address1] (The relevant Field) FROM [Mail List] (The relevant Table) alternatively, I have tried; SELECT [Address1] FROM...

Good Morning, I have written a simple query for address label printing purposes, but I do not want to print labels to identical postal addresses. This sounds such a simple requirement but my efforts to write a simple 'criteria' in the Address1 Field have been unsuccessful. Any suggestions for...