Hi,
I have been sent a file of customer contacts. They are held in a single field and have the format firstname + surname. So that I can load this data into a enterprise database system, I need to be able to extract surname and first name into to separate fields.
Any ideas on how I achieve this through an Access query?
Many thanks
Clay
I have a query with the following sql:
SELECT QY_ALL.TB_STAFF_STAFF_ID, QY_ALL.LNAME, QY_ALL.FNAME, QY_ALL.JOB_TITLE, QY_ALL.BASE, QY_ALL.DEPT_WARD, QY_ALL.STATUS, QY_ALL.COURSES_DONE_ID, QY_ALL.COURSE, QY_ALL.START_DATE, QY_ALL.END_DATE
FROM QY_ALL
WHERE (((QY_ALL.DEPT_WARD)=[Enter Dept/Ward]) AND ((QY_ALL.STATUS)="CURRENT") AND ((QY_ALL.COURSES_DONE_ID)=[SELECT COURSE ID]) AND ((QY_ALL.START_DATE) Between [Enter start date] And [Enter end date]))
ORDER BY QY_ALL.LNAME, QY_ALL.FNAME;When I run a report off it it returns correct data except it treats same surnames as one. E.g. if I have Jon, Jo and Fred Smith I get Jon listed but Jo and Fred's records attributed to him.
Is the sql wrong or do I fix it in the report?
Thanks.
I need to validate a surname field which obviously can only consist of letters and occasionally spaces and/or hyphens. (i.e. van Driel or Johnson-Crooks)I managed to create a validation rule which would allow me to have space in the surname field:Is Null Or Not Like "*[!((a-z) or ( ))]*"but when I tried to allow hyphens into the field - I used these codes - but none of them worked:Is Null Or Not Like "*[!((a-z) or ( ) or (-))]*"Is Null Or Not Like "*[!(a-z)]*" Or Not Like (" ") Or Not Like ("-")Is Null Or Not Like "*[!(a-z)]*" Or Not Like "*[!( )]*" Or Not Like "*[!(-)]*"Is Null Or Not Like "*[!(a-z)]*" Or Like "*[!( )]*" Or Like "*[!(-)]*"Is Null Or Not Like "*[!(a-z)]*" Or Not Like "*[( )]*" Or Not Like "*[(-)]*"Is Null or Not Like "*[!(a-z)]*" or (in ("-"," "))Is Null Or Not Like "*[!(a-z)]*" And (Not In ("*[0-9]*"))Is Null Or Not Like "*[!(a-z)]*" And (Not In ("*[0-9]*")) And (In ("-"," "))Is Null Or Not Like "*[!(a-z)]*" Or Like ( ) Or (-)Please can smeone help me.James
View 3 Replies View Relatedhi ,
see below for the current code to find a duplicate surname (this works Fine)
now that ive properly thought it through what i need to do is insert a surename and if (by Chance) when i insert the Date of Birth if this is the same highlight it as a duplicate record. However if these people are twins then i need the option to insert the duplicate record.
any ideas on how to go about this, see code below.
Private Sub Text24_BeforeUpdate(Cancel As Integer)
Dim SID As String
Dim stLinkCriteria As String
Dim rsc As DAO.Recordset
Set rsc = Me.RecordsetClone
SID = Me.Surename.Value
stLinkCriteria = "[Surename]=" & "'" & SID & "'"
'Check StudentDetails table for duplicate StudentNumber
If DCount("Surename", "tblData", stLinkCriteria) > 0 Then
'Undo duplicate entry
Me.Undo
'Message box warning of duplication
MsgBox "Duplicate Name " _
& SID & " has already been entered." _
& vbCr & vbCr & "You will now been taken to the record.", vbInformation _
, "Duplicate Information"
'Go to record of original Student Number
rsc.FindFirst stLinkCriteria
Me.Bookmark = rsc.Bookmark
End If
Set rsc = Nothing
End Sub
I have a button that when clicked is used to switch to a form displaying a record details based on a search by surname using a combo box. The record chosen by using the combo box is also displayed lower down on the initial form.
My problem is with multiple surname entries, it keeps switching to the first surname record; even when another surname record is selected.
Can I combine the use of surname and first name in my criteria? or Is there a better way to switch to the correct record once it has been selected. I am a bit of a novice with coding. My code is below:
Private Sub Open_Form_Click()
On Error GoTo Err_Open_Form_Click
Dim stDocName As String
Dim stLinkCriteria As String
stDocName = "Patient Details/Visit History"
stLinkCriteria = "[Surname]=" & "'" & Me![Surname] & "'"
DoCmd.OpenForm stDocName, , , stLinkCriteria
Exit_Open_Form_Click:
Exit Sub
Err_Open_Form_Click:
MsgBox Err.Description
Resume Exit_Open_Form_Click
End Sub
Is it possible to set up an input mask for a form for a surname field so that names will show first letter of name as a capital (easy), as in Smith - but for names like O'Connor, show the first and second letters as capitals? (and, of course, allow the ' to be entered).
Thanks.
Hi everyone,
Can someone please help me out here?
I am finishing a project at my client, and they said that I could take a copy of the database I created home (i.e. so that I will support them ;) )
I have a table tblPeople, however, the only condition is that I have to change the First- and LastName columns in this table, since it contains personal information. I need to change the names for about 600 people (400 men, 200 women)
I was thinking of adding the First- and Last Names of movie stars, to keep the flair in my DB :)
Does anyone maybe have a sample database with (movie star) names that I can use to populate my DB?
Thanks in advance,
Jean
Hi i have to design a member database in access,
As the membership number i need to use the first letter of the surname,
then have an auto number.
e.g Smith, if the last member was S47 then Smith would need to be S48,
This will be used on a form for a video hire shop, so, the customer details
will be entered, is it possible for this auto number to work just by putting the name in
the surname field?
I only know basic Access so please help.
Thanks a lot
Nick
I just need a simple method to prevent a member being imput twice on a form.
I have two fields firstname and surname ( this is necessary).
I want to stop duplication of the same person being entered when staff use the form.
How do I stop this?
I have a textbox called "name", what i am trying to do is have lowercase on first/second names in the textbox except the last word (surname)this i want in uppercase.
View 1 Replies View RelatedHi
im trying to combine the
Forename Column and Surname column so i can produce another column which is initial surname eg
Matthew | Williamson | M Williams
Kevin | smith | K Smith
how do i do this ive completely gone blank!!!!!! :(((
I am currently using Access 2007, where I used to be able to concentrate First Name and Surname Fields by simply using:
= [First Name] &" "& [Surname]
However I have downloaded Access Run Time 2010 on the other employees computers so they will be able to access the Database, however since i have done this the First Name and Surname do not appear, it simply shows:
#Name?
I am guessing this is because it is a newer version, however even when I open the Database with Access 2007 it still does not work, even if I delete it and add it again.
I have a report with the fields
Firstname_
Surname_
[field1]
[field2]
etc etc (they're not called field1/2 but they're irreverent at present.
On the database people can be entered multiple times so I may have
Bob Smith
Jimmy Carter
Bob Smith
Jackson Smith
Tim Jones
Jackson Smith
Bob Holmes
Now I now with the group function I could group by the firstname_ field but the results would group like the following
Bob Smith
Bob Smith
Bob Holmes
Jackson Smith
Jackson Smith
Jimmy Carter
Tim Jones
And if I do it by surname, well I am sure you get the drift.
So how can I sort so I would get the following
Bob Smith
Bob Smith
Bob Holmes
etc etc.....
is it possible to extract the first letter of a surname in an Access Query? Below is my sample data, and below that is what I want to acheive.
Player Name
Hart, Joe
Cech, Petr
de Gea, David
Szczesny, Wojciech
Mignolet, Simon
Reina, Pepe
Lloris, Hugo
Howard, Tim
Begovic, Asmir
What I want the query to return:
Player Name
Hart, J
Cech, P
de Gea, D
Szczesny, W
Mignolet, S
Reina, P
Lloris, H
Howard, T
Begovic, A
I have a Table with 6 fields, 2 are named Initial and Surname. Is it at all possible to have a combo box on a form with these to fields "joined" with the alphabetical order on the Surname. I understand that using a query will not work as the result is based on an expression
View 11 Replies View RelatedI've got a field in a table called GenTime. It is in a Date/Time format, like 5/16/2005 7:00:00 PM.
What I need to do is extract from this field the time, like 7:01:00 PM to 7:59:00 PM. I just can't seem to make it work?? :(
Help!
Hi Everybody,
I have an access database and in a certain table I have a string field which has two values. I want to make a query that will show all the fields which contain a substring.
I'd better give an example:
Table: Kid
Fields:
Name: Ben
Activities: Soccer, Tennis
Name: Dan
Activities: Soccer
Name: John
Activities: Football, Origami
.
.
.
I want to show in a query all the Names which have a "Soccer" substring in their Activities string field
Need your help ASAP !!!
Hi all,
In my query I can query for ODD or EVEN Street Numbers using the syntax below:
OE: [PROPHOUSENUMERIC] Mod 2
This works fine returning just odd or even numbers.
Now the problem I am facing now is that I wan the same query to return both Odd and Even Street numbers if I did not ask for either Odd or Even Street Numbers.
Is this possible. I have search hi and low in the forum but no answer. I have attached the file I am working with this post.
Thanks in advance.
matomo :confused:
Can someone help me out with writing the expression to pull out the IDs that are duplicates?
I know it is something like count >1. thanks!
Hi all: MS Access 2003. I have two text fields family_name and given_name I need to extract 1st, 2nd & 5th letter of family_name along with 2nd & 3rd letter of given_name to form a link_key field. If either name has insufficient letters the missing letter is replaced by the numeral 2. Does anybody know how to help me acheive this please!
View 2 Replies View RelatedHi,
I have a date field in one of my queries (01DEC2004). I have a pivot table linked to the query. I would like the users of the pivot table to be able to group the data by month. Currently the group feature will not work. Does anyone have a suggestion on the best way to extract the month from the date field?
Could I create a new column in the query called month and use an expression to extract the month?
If so can someone tell me how to key the expression to do this?
Thanks for any help.
Was just wondering how i go about pulling out a range of records. I do not want to use my Primary Key autonumber value as some records have been deleted and selecting a range of 10 to 50 for example will not guarentee 40 records. Is there some way i can use an SQL command in the SQL View window to achieve this?
Cheers
I found a thread from last year that is close to solving this question but not 100% so I'll post this new thread.
I have a field called ITEM_NUM in a database with the following structure:
XX-YYYYYYYYY
XX-YYYYY
XX-YYYYYYYYYYYY
I need to extract the Y portion of the data and a Query format would be the best.
FYI, the X portion consists of 2 Letters/numbers then the Hyphen and the Y portion is variable in length.
thanks for your help!
You guys have been so helpful in the past. Perhaps you can help me with this as well. I believe it’s a bit out of my scope. I have a list that reads similar to this:
Field 1
DMA: ALBANY
CAR 1
CAR 2
CAR 3
DMA: BOSTON
CAR 1
CAR 3
CAR 4
CAR 5
CAR 6
DMA: HOUSTON
CAR 1
CAR 3
CAR 4
CAR 5
I need it to read like this:
Field 1 Field 2
DMA: ALBANY CAR 1
DMA: ALBANY CAR 2
DMA: ALBANY CAR 3
DMA: BOSTON CAR 1
DMA: BOSTON CAR 3
DMA: BOSTON CAR 4
DMA: BOSTON CAR 5
DMA: BOSTON CAR 6
DMA: HOUSTON CAR 1
DMA: HOUSTON CAR 3
DMA: HOUSTON CAR 4
DMA: HOUSTON CAR 5
If my list was this short, I would do it all manually, but my record list is in the 10,000s. Basically I want field 1 to read: DMA:ALBANY and Field 2 to read:CAR 1, and so on. I’ve tried linking the table to itself in a query, however, that hasn’t proven to be helpful. I've also searched the FAQ's and newsgroups. Any insight would be appreciated.
Thanks,
Amanda
Hi,
I have a long list of customers / suppliers and their VAT numbers. I would like to create a query that shows only the customer/supplier number and the first 2 characters (countrycode) from their VAT number. for example
Supplier 3 VAT number NL80980808
endresult should show:
3 NL
Is there anyone that can help me out??
I probably have an easy question for one of you out there, but I just cannot get it myself.
I need to extract the data between two characters in a string.
The data will be similar to this:
T4454: Text Text-Text: $296.07: Text Text
I need to get the dollar amount between the dollar sign and the colon on the right of it.
So far, I have this for my query:
Amount1: Mid$([subject],InStr([subject],"$"))
Which gets the amount to the left side, but also has the $ in it.
In the example above, I would like to just end up with 296.07 as the result from the query. I should also add that the dollar amount may vary from 1.00 to #,###.00
Thanks in advance for any help you can provide