Getting Data From Two Fields!!!
Apr 19, 2006
I have this table filled with patient information. I imported the
excel file into textPad, then to Access and I have a little problem.
There are 2 fields that contain Patients name because the names are too
long. How I know where each patient name is because in another field
their is the word Patient, then the next two fields are there name.
Example:
Fields: ServiceDate, PayerID, ServType
Data: Patient, (Lahrmer,), Frances L
03/12/06, WEEK, OT Yaha
The Service Date field is filled with dates, until you reach the next
patient in which it has "Patient" in the field. Same for the PayerID &
ServType!
*Some of the Patients name are in the PayerID field, but others that
are longer are in both!
Question!
How do I get the Patient Name from the PayerID & ServType fields and
put them into a field in the table called PatientName? There are 16,000
records, so typing is not an option!
Thanks in advance!! :D
Any Ideas!!!!!!!!!!!!!!!
View Replies
ADVERTISEMENT
Apr 13, 2008
I created a form in Access that retrieves data from a table. Inside the form, I am able to access/populate data fields with data from the table. I also have data fields, inside the form, that requires key-in data. I have some how lost the ability send all data field information to a second table and clear existing data fields for new entry.
Questions: What settings, code or buttons can I use to send/store data field information to a new table? What settings, code or buttons can I use to automatically clear all data fields from my form once data has been sent to the new table?
View 7 Replies
View Related
Jun 28, 2015
I have a query that creates counts of fields based on the data in other fields, basically it tells me that in a table there are two entries with value ABC????? and three of DEF????? , the query works perfectly.
When I create a form to display this data and base the form on the Query I keep getting a message box asking for the ID (key field) from the base table.
If I type * in the box (to denote all values) and press enter I get the results expected.
View 4 Replies
View Related
Nov 18, 2013
Basically in my order details table i have the following fields
Product
Unit
Size
At the moment i have the Product field with a dropdown that gives me all the products from my ProductT. But once i choose the correct product in the unit field it gives me all the possibilities of every product not just the units associated with that product. ie
ProductT
Grasshopper Box1000 Adult
Grasshopper Box1000 Subadult
Worm 10pz Big
When I select the grasshopper product and move on to the unit field i also get "10pz" option but this is not a product available.
How do i set up validation of the fields Unit and size based on another fields data?
View 4 Replies
View Related
Aug 27, 2013
I have a database that will register the emails coming in and what time, also the time, date out and person.
I have a form with the fields to be filled in and a submit button. There are some fields that are automatically filled in and others need manually fill in.
below that part is a sheet (subform in the form of sheet) that should be filled in with the above data. Once it is there, it should empty the fields so they are ready to be filled in again. If I fill new data and press the submit button, it should go to the next available row.
View 2 Replies
View Related
Dec 19, 2006
Hello Tech gurus,
I have a database with existing data, that is not normalized, :eek: and all the data is in one field... This what I am dealing with:
As you notice the first row of data in field1 C10A CHOLEST&TRIGLY has 3 spaces to the right the next row ALTORVASTIN has five spaces to the right, the next line LIPITOR has 7 spaces to the right, and so forth, hopefully you get at what I am trying to do, I just looked at the data and it is not showing up in view of the leading spaces, but I am trying to move the data based ONLY on the position of leading spaces, example all data that has 3 leading spaces would go into its own separate column, and all data that has 7 leading spaces would go into a separate column, I have tried to use the left, mid, len functions but I cannot figure out how to move the text and keep it whole only by data position of spaces.
Field1:
C10A CHOLEST&TRIGLY
ATORVASTATIN
LIPITOR
PFIZER
SIMVASTATIN
SIMVASTATIN TEVA
TEVA
M1A ANTIRHEUMATIC N-STEROID
ETODOLAC
ETOPAN
TARO PHARMA
Thanks so much for your help... :confused:
Nana :D
View 10 Replies
View Related
Dec 19, 2006
Hello Tech gurus,
I have a database with existing data, that is not normalized, and all the data is in one field...:eek: This what I am dealing with:
As you notice the first row of data in field1 C10A CHOLEST&TRIGLY has 3 spaces to the right the next row ALTORVASTIN has five spaces to the right, the next line LIPITOR has 7 spaces to the right, and so forth, hopefully you get at what I am trying to do, I just looked at the data and it is not showing up in view of the leading spaces, but I am trying to move the data based ONLY one the position of leading spaces, example all data that has 3 leading spaces would go into its own separate column, and all data that has 7 leading spaces would go into a separate column, I have tried to use the left, mid, len functions but I cannot figure out how to move the text and keep it whole only by data position of spaces.
Field1:
C10A CHOLEST&TRIGLY
ATORVASTATIN
LIPITOR
PFIZER
SIMVASTATIN
SIMVASTATIN TEVA
TEVA
M1A ANTIRHEUMATIC N-STEROID
ETODOLAC
ETOPAN
TARO PHARMA
Thanks so much for your help... :confused:
Nana :D
View 12 Replies
View Related
Apr 14, 2007
Hello there, I'm having a problem very much like the one described by this user (http://www.access-programmers.co.uk/forums/showthread.php?t=113742&highlight=SQL+blank+spaces) where an SQL statement I am using to read data from one table and copy it to another is filling the remaining field space with blanks.
I didn't notice until a VBA module I worked on later on which was to read data from the new table and copy the selected record into a form said it could not find the records. The code:
Do Until blnFound = True Or rs.EOF
'DEBUG: answer = MsgBox("ok", vbYesNo)
'DEBUG: MsgBox rs("Account Name")
If rs("Account Name") = strAName Then
MsgBox "DEBUG: FOUND " & rs("Account Name")
blnFound = True
End If
rs.MoveNext
Loop
The record selector just goes straight to EOF because rs("Account Name") never matches strAName (as strAname has all those damned spaces after the useful data).
I wondered if perhaps there was some way to use the code in the other thread to read the length of useful data and then chop off the garbage spaces afterwards, but I'm worried it would be incredibly slow when running through the thousands of records. Also, the field has legitimate spaces in between words (most of the account names are two or three words in length).
The SQL statement responsible is as follows:
strASQL(i) = "INSERT INTO " & strTableName & "([CAccount No], [Short Name], " & _
"[Account Name], [Telephone], [Fax], [Contact Name], " & _
"[Mail address1], [Mail address2], [Mail address3], [Mail address4], " & _
"[Analysis1], [Analysis2], [Analysis3])" & _
" SELECT [CAccount No], [Short Name], " & _
"[Account Name], [Telephone], [Fax], [Contact Name], " & _
"[Mail address1], [Mail address2], [Mail address3], [Mail address4], " & _
"[Analysis1], [Analysis2], [Analysis3] " & _
"FROM tblCustomer WHERE [" & strField(i) & "] LIKE '" & strValue(i) & "';"
strASQL, strField and strValue are arrays and this SQL statement runs in a loop, as this is part of a search form for multiple possible entries. It all works fine except it always adds those damned spaces :D
Any suggestions?? Cheers.
View 12 Replies
View Related
May 15, 2007
His is itpossible to make abutton in access and then assign a equation to it to add the dat in two of the fields together, for example adding the "stock level" and the "re-order level together? ifso could some please tell me how greatly appreciated cheers
View 1 Replies
View Related
Jan 19, 2006
I have already asked this question before, but only one person responded, and it was not what I was looking for.
I would like to create a code that takes(copies) each record's data from a field and paste it into another existing field within the same table. I have about 2000 records... Your help will be highly appreciated.
It will have to be a VBA code or macro because I have to alter the strings that are copied before they are pasted into the other field.
View 2 Replies
View Related
Feb 14, 2008
Hi, I am new to access. My father likes to gamble and has lists of horses he likes to check to see if they are running. He is currently using Microsoft Word and has to check each one by one. I thought if I could get the lists in to Access then it would be a lot easier. I have a field called 'Name' where I am going to put in the horses. The problem is many of the horses in his list have the country next to their name in brackets. I want to remove the brackets and their contents automatically. So if I enter 'horse (GBR)' then I want only 'horse' to be entered. How would I go about doing this. I have brackets in other fields so want this limited to the 'Name' field only. Is this possible?
Thanks :)
View 12 Replies
View Related
Jan 3, 2007
Good Afternoon All,
I have a table devoted to time spent on certain projects. I am trying to make a query to show the total time spent on each project (the data is not in time format) and then turn that into a percent for a report. I have tried doing a summary query, but that isn't giving me the data i am looking for...is there an easier way:confused:
View 13 Replies
View Related
Aug 1, 2007
I have a database with a field LNAME with last names preceded by maiden names in parentheses. I would like a query that would update fields (LAST and MAIDEN) with the corresponding data:
Example –
LNAME = (Jones) SMITH
LAST = SMITH
MAIDEN = Jones
Any ideas would be greatly appreciated.
Thanks,
SKK
View 2 Replies
View Related
Jun 24, 2005
Hi,
Just a short introduction. I am rather new to this forum and also a beginner to Access development. I am currently working on developing a database for CRM purposes for an organisation.
I have created a form for ease of viewing and inputting data. However, the problem I am encountering is that some of the fields does not allow me to add new entries. All the data seems to be drawn from the right sources and any changes made to the existing data is reflected in the underlying tables but it just doesnt allow me to add new data for some fields.
I have attached a compressed version of the database and I would very much appreciate if anyone could offer any assistance on this issue.
Go to Forms (tabbed)
Fields where data cannot be inputted :
Relationship Info - Service, Relationship Info
Meeting Info - All fields
Client Info - RelationshipNum
Fields where you can input data
All the rest are working fine
Thank you very much for your help!
View 1 Replies
View Related
Jun 27, 2006
i am creating a report in access, but it shows a few titles without data in front, cause i left the fields blank in the table, is there anyway of making these titles visible only when some data is inserted and remain hidden when the fields are empty..
View 2 Replies
View Related
Oct 15, 2007
I know I am being particularly stupid, but I can't work out how to do the following:
I have a table in which is recorded information about orders for customers. These fields are for orders of meat and poultry. As customers may wish to order more than one Turkey, I have created three fields called Turkey, Turkey 2, and Turkey 3. The data that is input is the weight of the Turkey e.g. 22 (lbs), 16 (lbs).
When it comes to ordering the turkeys, I need to produce a report which shows, by weight, the number of turkeys needed. This will need to add the number of turkeys in each of the 3 fields and order them by weight.
I've just got lost, trying to work out whether I base my report on the table or on a query based on the table. Help!!!
View 2 Replies
View Related
Apr 8, 2014
In Access 2010, in a table or form, are you able to use data from a lookup table like you can in a vlookup in Excel?
I am creating a Student database starting with the MS Students template. I will have a Parameter Table that contains weighting factors for each test for a given class. When scores for a particular test are entered for each student in a Test Results table, I would like to use the weighting factors for that test from the Parameter Table. These would be used in formulas/expressions to calculate a weighted score for the particular test as well as class-to-date average for all the tests to that point.
It seems this can be done in a query, but I am wondering if it can also be done in form so that when the instructor inputs the test scores the calculations are performed and the results of the calculations displayed. Or, if not, can the query be "built in" to the form to accomplish the same effect?
View 2 Replies
View Related
Nov 9, 2005
I only have one table and I read so often that its better to have multiple tables. For what I have, it seems to work fine.
Will this cause problems down the road?
Thank you once again
John D
View 14 Replies
View Related
Feb 13, 2006
Hi,
How can I get a field to update automatically after data has been imported into the record.
Manually it works using the DLOOKUP in the "After Update" properties but this does not work when importing data.
TIA
Mike C
View 4 Replies
View Related
Dec 6, 2007
Hello people.
Im just after a bit of advice please??
I have created an audit trail for data held on a form so that when a field is changed it adds a line of text to a memo field with the date of the change and the old and new field values. The problem I have is that each time something is changed the line of text is added beneath the existing text in the memo field. This means that for users to view the most recent change they have to scroll all the way down to the bottom of the memo field. Is there a "Quick" way of me having the most recent entry first.
I am using the vbnewline command when adding the text. A sample is shown below:
Dim Response As Integer
Dim AnotherItem As Integer
Dim sqlstr As String
Set Myform = Forms![DataDetail]
Producer = Forms![ListOfProducers(Existing Data Item)]![LstProducers].Column(1)
Response = MsgBox("Are you sure you wish to add a Producer?", vbYesNo, "Data Dictionary v1.0")
sqlstr = "INSERT INTO DataProducers ( Data_Id, Producer_Id ) SELECT Data.Data_Id, Producers.Producer_Id FROM Data, Producers WHERE (((Data.Data_Id)=[Forms]![DataDetail]![Data_ID]) AND ((Producers.Producer_Id)=[Forms]![ListOfProducers(Existing Data Item)]![LstProducers]));"
If Response = 6 Then
DoCmd.RunSQL sqlstr
Myform!LstHistory = Myform!LstHistory & vbNewLine & "Changes made on " & Now & ""
Myform!LstHistory = Myform!LstHistory & vbNewLine & "The Producer '" & Producer & "' was added to this data item"""
Myform!LstHistory = Myform!LstHistory & vbNewLine & "-----------------------------------------------------"
MsgBox "Producer has been successfully added.", vbInformation, "Data Dictionary v1.0"
Thanks folks...
View 2 Replies
View Related
May 23, 2005
I have a table in my Database that uses the feilds Location and partnum. I use a barcode scanner to generate each of these fields. The problem I am having is that when our part number is scanned it contains data such as "des--. 123456 --aa". I have been doing a find/replace on this data to remove the "des--." and "--aa" but I was wondering if access has a function to do this for me so that I can get a macro to check automatically for this type of problem. Any ideas?
View 1 Replies
View Related
Jun 21, 2005
WorkID is a look-up field, so its Data Type is Number.
In the same table, DailyGrind is a field with the Data Type of text.
I would like to update WorkID with the jobs in DailyGrind.
Surely there must be a way to do it, gurus?
View 4 Replies
View Related
Aug 10, 2006
I am not sure that I am in the Correct Section for this question but I am sure I can be forgiven for that :
I am attempting to build a db for a friend who has all his data in one table. During normalisation I have seperated the various threads of data into various tables of usable information. My problem is that I need to seperate his clients fullname into First & Surname i.e. Fullname: Mick Burke to FirstName: Mick and Surname: Burke - in other words make two fields from one.
Any help in doing this would be well appreciated.
View 2 Replies
View Related
Jun 17, 2007
Hello
I have a table with 3 fields:
- ID no (primany key), Time1 and Time2
I have 3 records with ID no 1 to 3.
So my table look like this
ID no: Time1: Time2:
1 [emty] [emty]
2 [emty] [emty]
3 [emty] [emty]
I have an Excel document with the data for field Time1 and another Excel dokument with the data for field Time2. Excel doc1 have 2 colums named ID no and Time1. And Excel doc2 have 2 colums named ID no and Time2.
How do I import/update these data into my table?
I have tried wiht a adding-quiry but it only works if the table is complety emty and only with one Excel doc. Trying the second Excel doc afterwards only makes an error and no fields are updated.:confused:
View 4 Replies
View Related
Sep 17, 2007
Ok,
So after finishing my database with mail merges I found out that I identified a few fields as number fields but these fields may contain numbers that start with 0 and I need to preserve that.
I believe the right step is to make those fields text fields. This preserves my 0 for me but gives me other problems. My question is if that is the only way to retain the lead 0 in the data?
If it is then my problem then becomes a mail merge problem.
I am mail merging to a specific form and need the data to fit into specific boxes but I am unable to format the text with a switch. I can format the data when it is a number by using /# ### ## ####. This works just fine for me
but I am unable to format it when it is text with a switch. I basically need to put spaces in between the text fields so i can make it fit so instead of 012345678, I need it look like 0 1 2 3 4 5 6 7 8 9 in the word document.
I cant seem to fins anything on formatting text switches for spacing.
Thanks!
View 1 Replies
View Related
Jan 3, 2008
I have two one-many related tables: Transactions and Site Details. Most of the Transactions information can be filled in first, and then the Site Details filled in when they are ready.
How do I get a query based on both tables to show those transactions with missing site details? I have tried IsNull(SiteName) in the SiteName field in the Design View, but I don't get any results at all (for records with no SiteName). The answers I've seen in Google seem to be variations on the theme, but I am probably missing something simple (as usual).
I orignally posted this with a question about tracking revision history. I thought it might be a similar solution, since I want to add information to the blank fields when the information becomes available. But I need to find those transactions WITH blank fields first.
View 3 Replies
View Related
