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!!!!!! :(((
Hi
I have a confusing situation. I have a need of creating a combobox which displays records from different tables.
Eg.
I have a table called "house parts" and filled with records like
room, hall, garage
Secondly i have a table called "Parts" and filled with
floor, ceiling, lamp, window etc.
As u see, "house parts" could consist of "Parts" like "room" could have floor, ceiling etc.
I need to create a query that shows me all the records from "house parts" and also "Parts" in the same list.
Can anyone help me please
NEVER MIND--- I FIGURED IT OUT:o
I have a table with two columns.
FIRST LAST
James Jones
R Kevin Foster
When I use this:
Left(FIRST,1) & " " & LAST AS Expr1
I get: James Jones
R Foster
Is there a way to correct this so R Foster comes back as R Kevin Foster?
I created a form and want to ascend two columns by date order. Example 1st column has dates from Sept. 05 and Nov 05 and the second column has dates Aug. 05 and Oct 05. How do I get the form to merge these two columns so that the date order is Aug, Sept, Oct and November.
View 1 Replies View RelatedThe problem I'm having is that i need to combine 12 tables with multiple columns and not all columns have the same headers. So if a column is present it should be updated, if not it should be added.
The end result should be one column with all records and all columns that exists in all 12 tables.
The tables have an identifier that is the same for all columns (supplier reference).
Table 1 has 56 columns
Table 2 has 42 columns (of which some have the same header)
....
(--doing every column manually is something i would like to avoid if possible --)
So i have a access database with a main field that we can call vendor # (LIFNR) and another called Company code (BUKRS). There are multiple company codes under a single vendor #. Example:
LIFNR
BUKRS
0000010535
1010
0000010535
5060
0000010535
5610
0000010536
1010
0000010536
5060
0000010536
5610
What I am trying to do is create a 3rd column where i can have a unique row for each of these fields without it repeating. There is no unique identifier in this table and that is what i am trying to achieve.
hi, im a bit stuck. i am creating a mail merge and i want access to retrieve only the first character of the forename to input into the mail merge.
e.g. Dear Mr. R Price.
I want the 'R' to be retrieved by access from the full forename, so 'R' would be retrieved from the name Robert in the forename field.
Thx
I have a form that contains a combobox. I want the default value for this combobox to be the Staff_ID of the first forename to appear in a table when sorted alphabetically.
The table I'm querying is called 'Staff'. Fields within are 'Staff_ID', 'Forename', 'Surname'. The table is sorted by 'Forename' in ascending order.
From the example tables below I would expect to return a value of '3' for example 1 and '9' for example 2.
Example 1
Staff_ID Forename Surname
3 Andrew Banks
7 John Jacobs
2 Mark Jones
8 David Smith
Example 2
Staff_ID Forename Surname
9 Alice Jones
3 Andrew Banks
7 John Jacobs
2 Mark Jones
8 David Smith
I'm using Access 2007
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,
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
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
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 RelatedI 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
Hi
I have a table which keeps a record of days off sick.
The table has the field [First Day] = the first day they were off sick, in a dd/mm/yy format
and a [Last Day] = last day they were off sick, again in the same date format.
I need to have a query that will produce the actual day, and all of the days in between; so - if the First Day is 25/10/06, it will produce "Wednesday"
and then the last day is 27/10/06, it will produce "Friday"
But, I also need it to produce "Thursday" (which would be the 26/10/06)
Any ideas??
Thanks
Maria
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 have 2 comboboxes to select a product no, and a unit of measurement
I have a materialprices table with unique records, keyed on productno and unitno, so i should get either one or no records returned from the following SQL
However, if i compare the values of the returned records with the search values and the tests, it shows a difference, although the difference is then showing as zero when I evaluate it.
(I found this because initially I had written the code using seek, and was trying to ensure I had located the correct record)
I have actually got round this by testing the abs difference as being greater than a minuscule value.
ie
If abs(rs!pmpprodno - cboProd)>0.01 Or abs(rs!pmpunit - cboUnit)>0.01 which does not produce an error
I could understand this if I was testing real numbers, but these are all integers - any ideas anyone?
so ignoring the dims - ignore any typos - the code compiles and executes properly - its just the equality test thats the problem
function lookupmat as boolean
strsql = "select * from tblmatprices where [pmpprodno] = " & cboProd & _
" and [pmpunit] = " & cboUnit
Set rs = CurrentDb.OpenRecordset(strsql)
If rs.eof Then
lookupmat = False
rs.Close
GoTo exitproc
End If
'having used the above where clause to find an item, this test now seems to produce a difference, even though the figures are the same!
If rs!pmpprodno <> cboProd Or rs!pmpunit <> cboUnit Then MsgBox ("Unexpected - There is a difference " & vbCrLf & _
"cboProd = " & Format(cboProd, "###.0000000000") & _
" Lookup = " & Format(rs!pmpprodno, "###.0000000000") & vbCrLf & _
"cboUnit = " & Format(cboUnit, "###.0000000000") & _
" Lookup = " & Format(rs!pmpunit, "###.0000000000") & vbCrLf)
'and now both of these tests are showing a difference! of zero
If cboProd <> rs!pmpprodno Then MsgBox ("Prod Was different " & cboProd - rs!pmpprodno)
If cboUnit <> rs!pmpunit Then MsgBox ("Unit Was different " & cboUnit - rs!pmpunit)
lookupmat = False
rs.Close
GoTo exitproc
End If
Hi everyone,
I really need help with transforming this table/data:
Project Date(dd/mm/yy) Value
----------------------------------
A...........1/1/2007...............100
B...........2/2/2007...............200
C...........3/2/2007...............300
D...........4/5/2007...............400
E...........5/5/2007...............500
F...........6/5/2007...............600
G...........7/6/2007...............700
into the following data format (13 columns in total, with 7 rows):
Project Jan Feb Mar Apr May Jun Jul ... Dec
-------------------------------------------------------------------
A............100.................................. ..........................................
B.....................200......................... ..........................................
C.....................300......................... ...........................................
D...............................................40 0.........................................
E...............................................50 0.........................................
F...............................................60 0.........................................
G................................................. .........700...............................
Assuming today is January 1st 2007, the 2nd column must starts with current month.
If someone can post me the SQL, or give me some tips or direct me to an article on how to achieve the results above..it'll be most appreciated.
Thanks in advance..
Hi, I am looking for help with a database I am trying to set up for a dog
club. I have created databases before although not for more than 6 years
and am now probably at the bottom end of Intermediate in terms of skill and
have no experience of using code builder as yet.
I need to create a database of dogs, their parents, owners and breeders and a few other bits of information such that I can print a catalogue of dogs entered at dog shows (see below) and am finding it very difficult.
Owner(s), address,
name of dog, sex (Dog or Bitch), Date of Birth, breeder, Sire - Dam
I've created various tables: Dogs, Owners, breeders, address, Dogs/Owners
(junction table) and Query to do the pedigree of the Dog (ie the parents)
which is a self join.
An owner can have more than one dog and a dog can have between 1 and 5
owners at the same time. Where there is more than one owner then the
address is that of the first named.
I also need to be able to add and subtract both dogs and owners to/from
their respective tables although subtraction will occur less frequently. I
then need to be able to produce catalogues with the details laid out as
above. Not all of the dogs or owners will be in each catalogue. I
anticipate doing this using a query and then using mailmerge in Word.
I appreciate that this may be too big a task to help me with but do not like
to give up until I have explored all avenues. If you can help then
I can let you know the field and joins that I have and other important
details.
Thanks (hopefully)
Pete