First Name Surname Table To Table
Mar 4, 2007
Hello - new user here. I need help in setting up my database to be used in a non profit sports club.
I have one table containing FirstName and LastName (this will build into my membership information file with lots more detail eventually) and another table which is to be used as a class register.
I want to extract information from the Membership table and use it to produce Class Register table.
I wish to do this by entering a first name or first letter of a name and have all the available portential matching records come up in a drop down box to be selected from.
I have tried every way I can using relationships, codes etc and cannot make this work. I cannot move forward to build the database until this is solved.
Can you help??
Many thanks
Brian
View Replies
ADVERTISEMENT
Aug 22, 2006
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.
View 1 Replies
View Related
Apr 2, 2008
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 Related
Jan 12, 2006
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
View 2 Replies
View Related
Jul 5, 2005
hi ,
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
View 1 Replies
View Related
May 26, 2005
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
View 2 Replies
View Related
Aug 24, 2005
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.
View 1 Replies
View Related
Sep 29, 2013
How can I update (some columns) in a table from the same table based on a Criteria column in the same table.
View 2 Replies
View Related
May 3, 2005
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
View 3 Replies
View Related
Oct 16, 2004
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
View 2 Replies
View Related
Apr 25, 2013
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?
View 2 Replies
View Related
Sep 11, 2013
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 Related
Nov 27, 2006
Hi
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!!!!!! :(((
View 3 Replies
View Related
Aug 8, 2013
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.
View 2 Replies
View Related
Jan 21, 2015
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.....
View 2 Replies
View Related
Jun 11, 2015
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
View 6 Replies
View Related
Aug 1, 2015
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 Related
Jun 28, 2013
I need to get x and y coordinates for each device, but the data has to get looked up from 2 other tables.
I have a table (called InstReclosers) that has device names. Each device is on a Section. I can go to another table (called InstSections) and look up what Node that particular section is tied to. Then I need to go to another table (called Nodes) to get the X and Y location for that particular node.
How I can go about getting this X and Y data into the InstReclosers table?
...in summary, InstReclosers has device name and section name. InstSections has section name and node name. Nodes has node name and XY coords. Need XY coords for each device in InstReclosers.
View 3 Replies
View Related
Jan 29, 2015
When I try to transfer (ctrl c + v) data from a table in Excel to a table in Access it loses format.
Exemple: $ 1.000,00 (Excel) become 1000 (Access).
I need to keep the format to make sure that the code works properly.
View 9 Replies
View Related
Nov 4, 2014
I have a make-table query that pulls all the fields from 1 table (MainTable), and creates a new table with a date stamp based apon a form value entered (New Table = MainTableWithDate).
Currently, I setup the query to pull info from the form field like this:
DateField: [Forms]![frmmain]![DateField]
However, when the make-table query is done - all date fields are blank (all other fields are correctly created), and when I look at the new created table (mainTableWIthDate), the typeassigned to the date field is "Binary" (in the form, I've specified LongDate).
View 6 Replies
View Related
Aug 5, 2014
I have a table products with a field "id_product" and "total" (Total items in stock)
I have a query with the fields "id_product" and also the field "total in stock"
I want an update query to update the field 'total' in table 'products' with infos from that query
For each id-product in table products, replace the field total with the field 'total in stock' from the query
So I want to update a filed in a specific table with infos form another table.
View 2 Replies
View Related
Apr 19, 2013
Here's a query that the bottom listview in the attached form i.e. a listview representing a table of calls(many) to fims (1 top listview)
Code:
SELECT calls.id, calls.firm_id, calls.called, calls.said, calls.spoke_to, calls.next
FROM calls
WHERE (((calls.firm_id)=[firms].[id]))
ORDER BY calls.called DESC , calls.next DESC;
When I run the thing...I get a dialog asking me for firm id.
I want to change this so when I move up and down the firms LV (top)... the bottom LV updates taking firm id from the top LV with focus.
Access 2003.
View 2 Replies
View Related
Jan 13, 2014
I have an MS Access accdb with linked SQL Server 2012 ODBC tables. I am working on a procedure to copy data from local tables to these linked tables (identical schema). I did a simple
Code:
DoCmd.RunSQL "INSERT INTO linkedTable SELECT * FROM localTable"
This works, but is very slow. Way too slow. (INSERT copies the data one record at a time).
I would like to copy the data in a bulk operation, or operations that I can execute programmatically.
View 1 Replies
View Related
May 28, 2015
I have a table for a multiple parents linked to a child table. I need to figure out a way to only allow 1 parent to be coded as primary, 1 as secondary, and then the rest as other... I thought about making Primary/Secondary/Other a primary key. But then I can only have 1 other. I would have to make a finite number of parents that could be entered and I want an infinite number.... My end goal is to have a report that only has a primary and second parent on it, but the rest of the parents still exist in the table...
*Child_ID
Parent_First_Name
*Primary/Secondary/Other
View 8 Replies
View Related
Sep 12, 2014
How to make a form open with data from a table based on a date and time in the/a table?
View 7 Replies
View Related
Aug 29, 2006
Hi,
I am extracting data from linked db2 table using access make table query. First I create a select query and can view the linked db2 data, but when I change to a make table query I get an error message, "invalid argument", when I run the make table query. There is no selection critera specified. Has anyone had this happen? and Do you know a solution?
Thanks for your support
GinnyP
View 1 Replies
View Related
