Creating Relationship Based On Part Of A Text Field
Jan 27, 2014
Each each record in table1 has a unique four character (alpha-numeric) code to identify it. The first two numbers of this code represent the group it is in. (Ie. 15AB and 1502 are both grouped together) The second table stores values that apply to the entire group. I need to create a relationship between these two tables based on the first two characters in the ID field.
Things I've tried:
* Making a calculated field with left$() formula - Access doesn't allow relationships on calculated fields
* Create a new field for just the first two characters and create a data macro for after update and after insert to update that field with the expression - cannot edit the field the user is on
View Replies
ADVERTISEMENT
Oct 12, 2006
I'm working on a table which has a country field, but this field may contain a text string consisting of more than one country, eg "France, Belgium, Spain"
I want to run a query against the table to select records for any one country, but not sure how to do this.
Any suggestions please?
View 4 Replies
View Related
Mar 26, 2014
We have a field that we track paperwork with that is 9 characters. The first 4 characters are a 'julian date', the 5th character is a dash and the last 4 characters are sequential.
Example:
For today, we would use 4085-0001, 4085-0002, 4085-0003, etc.
For yesterday: 4084-0001, 4084-0002, etc.
I have a query set up that will pull records with a julian date of today-1 and today-7 that works. So a result I may get:
4084-0001
4084-0002
4081-0000
4078-0000
4078-0001
4078-0002
I tried formatting the source field with Left -4, but it only shows the first four characters in the result:
4084
4084
4081
4078
4078
4078
How can I count the number of records each day based on only the first four characters? Example:
4084 = 2
4081 = 1
4078 = 3
View 6 Replies
View Related
Aug 2, 2013
Table name: Items
Field: ItemName
Form name: Frmpayments
when I open the above form, it should create texts boxes on "frmpayments" based on how many data available in "Items" table under "Itemname" field. It'll be even better, if it can put those item's name in each text box it creates.
View 1 Replies
View Related
Dec 22, 2004
In my checkbox named Taxable I have the following in the afterupdate
Private Sub Taxable_AfterUpdate()
If Me.[Taxable] = True Then
Me.[Tax] = 0.06
Else
Me.[Tax] = 0
End If
End Sub
This will only insert whole numbers in the Tax field as I have tried several
combinations of numbers. How can I get Tax = 0.06?
View 4 Replies
View Related
May 10, 2013
I'm creating a database for a school. it contains the following tables: 'Students' of which the primary key is StudentID, 'Subjects' of which SubjectID is the key and 'Exams' - ExamID.
Then, I have a table called 'Results' but with no primary key of its own - just 3 foreign keys from the other tables and another field which can be left blank let's call it 'Grade'. So the Results table would have the following fields:
StudentID*, SubjectID*, ExamID*, Grade
Now, I make a form which creates a new lets say ExamID and I would like to create records with all the combinations of all of the subjects and students, ready for the 'Grade' to be typed in.This is assuming the Exam isn't based on the Subject directly.
View 1 Replies
View Related
May 10, 2013
I have a table of the following structure:
Key 1 Key 2 Key 3 Optional
data1 data2 data3 data4
The 'key' fields are a composite key of primary key fields from other separate tables. I have a form that generates a new 'Key 1' record and I would like to make a query that creates all possible 'Key 2' and 'Key 3' results (new combinations) while key 1 stays the same. How would I go about doing this? I know I can be done.
I cannot really give any real data as I haven't received it yet.
View 4 Replies
View Related
Aug 20, 2004
Is there a way to make just part of the text in a text box bold, or to use different font sizes in the same text box?
Something like this:
---------------------------------
Heading in bold: description in regular (not bold)
(a smaller height line used as a line space)
Another "normal" line
a larger height line
a normal line....all in one text box!
---------------------------------
One thing I'd like to be able to do is to specify the line height of a blank line in a text box. I'm using carriage returns created with: Chr(13) & Chr(10). I could see defining the font size of a hidden character, but I'd need to know how to assign a font size to a piece of the text in a font box.
View 2 Replies
View Related
Jul 2, 2007
I have a table with all the job information for each of our jobs. I want to create another table with a job notes field that is linked to the job info table above. My main goal is to be able to make a query or report for the job notes on all our jobs on a specific date.
the query will look like this:
job # field, job name field, job notes (july 3) field.
Q1. Is there a way to link the job info field with the job notes field where the job # and job name information will automatically populate into the query?
Q2. How do I build a relationship between the job info field and the job notes field.
I tried to link them, but when I do the query the job # and job name fields aren't being populated, it only shows a single blank row for the whole query.
Thanks!
View 1 Replies
View Related
Dec 8, 2007
Hi,
Can someone please help me take advantage of this handy piece of work I found on the forum?
I need to implement a picture management app for a database I am developing and this (Attached) app looks to be exactly what I am looking for.
Here is what I have been trying to do.
Use the attached tables, and forms to create a one to many relationship in my exisiting database.
I have a PK of OrderID in my existing database.
I have the table, form and mod in my existing database but I cannot seem to get the relationship down..
Any assistance is greatly apprecaited.
Fen How
View 3 Replies
View Related
Nov 1, 2014
I have a database assignment where I need to link some tables. One is a table with a composite primary key and the other is a table with a regular primary key. The composite key is already linked in a one-to-many relationship with the first of its two fields; the problem is the second field also needs to be in its own one-to-many relationship with a different table. Access keeps creating a one-to-one relationship. I need it to be a one-to-many relationship.why this might be happening?
View 1 Replies
View Related
Nov 18, 2014
I have started a new project where I have tables of people, organization, and roles, as well as one that is supposed to contain the relationships among them. In all four tables there is an AutoNumber ID field as the primary key. The fourth table, the relationship table, has as foreign keys, the primary keys from the first three.
I have created a form with a combo box for each of the three main tables. The intent is for the user to select a person, an organization, and a role, and then click on a button control that will create the relationship in the relationship table. What I am trying to do in the code is, using an AfterUpdate event procedure for each of the combo boxes, open the appropriate table as a recordset, find the selected record, and set a form-local variable to the value of the ID field for that record. So far, I can't get past the first combo box. Here is the code I have written, including both the form variable declarations and the AfterUpdate code:
Option Compare Database
Dim personID As Integer
Dim orgID As Integer
Dim roleID As Integer
Dim rsPerson As Recordset
Dim rsOrg As Recordset
Dim rsRole As Recordset
Dim rsRel As Recordset
[code]...
The error message I get is Compile error: Object required, with the personID reference in the last statement highlighted. (qryPerson is a query based on tblPeople that combines first and last names into LNFN.)
View 1 Replies
View Related
Apr 17, 2014
I have about 600,000 records in an excel sheet and tried using the table analyzer but I run out of memory. Instead i have broken up my giant table into a few other tables with their own primary keys and have populated from a maketable query. I related the tables together in a 1 to many and many to many table and one of the subforms is working great. The problem i am having now is with the many to many table (WorkedJunctionTbl). I don't want to have to manually input thousands of lines of data, so is there a way i can populate the data from the original data correctly using the new autonumber primary keys?I can't seem to get an append query to work correctly.
View 8 Replies
View Related
Jul 26, 2005
Hi, I’m having a problem creating a form for easy data entry. Let me give you a little background. I have a database for a tutor program, students request a tutor for a class, and then I fill out a follow up report to find out if they are being tutored or changed their minds. Next I enter the contact information indicating the date and time of their tutoring session.
Students can be tutored in more than one class but they can only have one tutor for each class.
I have a form where I enter the tutor request info and sub forms for their classes, follow ups, and sessions. Ideally I would also like a form just to enter the session info. I want a combo box to select the tutor and then a sub form to select the tutee with a combo box which will auto fill the Subject, and Course fields, and field to enter the date and contact hrs.
I think the problem is with my relationships. I’m just kind of stuck. I think I need to create a form based on a query but I haven't done that before. Any help would be appreciated. Attached is a screen shot of my relationships and of my current form for your reference.
THANKS!!!!
View 1 Replies
View Related
Apr 28, 2005
I've been searching all the forums and unable to find my answer. What I want to do is show on a form the average quote for the specific model number.
On the form there is customer info, product info, etc. I want to see the average of what we have quoted every other time we have seen this product (we do repairs). The identifer for the product is the model number, Model#, the quoted cost is PRICE. I've used this function before, DAvg("[Price]","Repairs","[Model#]='013003020'") - which of course gives me the average for one specific model number (013003020). How would I use a similar function but have it do the average for the model number that I am viewing on the form?
If I am able to do that - I also need to find a way to not include nulls and zeros from the Price field.
View 3 Replies
View Related
Dec 12, 2005
Hi.
My name is Amanda, and recently I’ve started using Access again for work. I haven’t touched the program in about five years, so I am rusty. In addition, it turns out that I am the only one in my department who’s ever even opened Access. I’ve tried hunting down a manual around here, and it seems to be an ever ending scavenger hunt. I also have not been able to find the answer to my question via web, FAQ, and experimentation, although, I feel it’s a fairly basic question.
So here it goes. I want to return all fields that contain part of a text. For example, I want all fields that contain the word “lux”, regardless of what else the field contains to be returned. So if a field contains “lux. cars” it will be returned in my query.
I’ve tried goggling “Microsoft Access” with “text formulas,” however, all I receive are excel formulas.
Thank you.
View 2 Replies
View Related
Aug 24, 2006
Hello,
I have a table with a field named SSN. This field contains data with the following format:
123-45-6789L
As you can see my field has a text format and I need to create a make table query that will extract all the character that are places in position 12 and 13 (simply the letters after the ssn).
I have tried with Mid([SSN],12,13) but nothing happens.
Any help? Thanks.
View 5 Replies
View Related
Feb 13, 2005
Hello, Bit of a newby to all this. Am trying to use a text box as a calculted field. I am doing so using an sql statment in control source The sql stat is built and tested using build query and then copied and pasted from sql view)
I keep getting #name? as an error.
I have managed using dlookup of the same saved sql to return the required value (which works) but find it to be a bit slow and clunky so wanted to give this a go instead. If somebody can have a look at the query below I would much apreciate.
=(SELECT Sum(QryInout!CostExVATGBP) AS TTlCostExVATGBP
FROM QryInout &_
WHERE (((QryInout.StockImportNoJoinToAll)=[Forms]![FrmSPurch]![ImportNoNew])) &_
GROUP BY QryInout.ProdType &_
HAVING (((QryInout.ProdType)="Freight"));)
Note that I have tried with and without the &_ and also with or with out the brackets..
Thanks
View 4 Replies
View Related
Jun 26, 2013
Is there a way to have a text box filled out based on the value of a field. There will be only 2 possible phrases for the text box.
=IF( <Expr> Forms![Workorder]![Disposition])=("Return As Is","DISAPPROVE","APPROVE")
This is what I have tried but I get syntax error.The form is Workorder and the field is Dispostion. When the Disposition is "Return As Is" then I want to have the text box yield "DISAPPROVE" otherwise it should be "APPROVE".
View 14 Replies
View Related
May 16, 2005
Hi, I am new here and need some help with MS Access97. I am looking for a more convenient way to join tables together. The usual way that I know is to drag the field(s) in one table and drop it in the other table (within the relationship window).
Is there any other way that I can do the same without drag and drop (keyboard instead of mouse)? This is real problem to me when I need to do some modification and have lots of tables scatter across few pages and the vertical scroll of the Relationship window is not working properly with drag & drop technique.
Please help. Many thanks! :)
View 2 Replies
View Related
Apr 3, 2013
I have a relationship between two tables; the parent table is "Client Information" with a primary key of "Client_ID", the child table is "Event Information" and the foreign key is "Client_ID".
I know that within the Event Information table I can select which Client_ID to use as the link. I want to have that done within a form, however. I have a form for Client Information with a subform for Event Information. If I go into the table and manually set the Client_ID then everything displays fine.
How can I set the Client_ID in the subform to automatically match the Client_ID in the main form? I can only assume that these solutions require the parent/child relationship to already have been established.
View 4 Replies
View Related
Feb 4, 2014
I have a Client database table in Access. I now need to add a simple order table (related) to the client table. I have a client ID field set to autonumber in the client table. As I start to create the orders table I'm not sure how to link the two so that I'm not entering data twice and have assurance that they are tied together.
View 4 Replies
View Related
Aug 23, 2006
hi everybody, im have a database with table called "project". there are many column in this table. my user want to export this table to Excel, but only some of column, with particular order ( depend on him) to analyze in Excel.
he asked me to build a form with a list box, drop box,somthing like this, so he can choose what column to export in what order.
i try to make a query like this: " Select Forms!UserInput.combobox1.value , Forms!UserInput.combobox2.value,etc, From Project" but it wont work.
Dou you have any idea.
thanks in advance
View 2 Replies
View Related
Aug 27, 2006
I have created a text field on a form that uses the DateSerial function to return the value of last month e.g. Jul-06.
I have a table that contains months (e.g. Jul-06, Jun-06 etc) and a corresponding numeric value (e.g. 1,2,3).
I have another text box on the same form that I want to display the numeric value in based on the value that has been returned by the DateSerialfunction. What properties/control source should I set for this field? Or do I need to write some VBA?
Any help would be appreciated.
View 2 Replies
View Related
Jun 23, 2006
I want to print text based upon the content of the field but need more than the IIF statement. For example, if the field contains an "A" I want to print "Active", "C" I want to print "Closed", "N" I want to print "New", etc.
Thanks in advance for any help. You guys have always had an answer in the past!
View 1 Replies
View Related
Jul 8, 2013
I've been tasked with creating an Access database..We have a main table that includes information found on every lease, however there are 3 leases types. Each which lead to another table or two. Basically, I want to create a form that allows me to link the main table with the corresponding variable tables.
View 1 Replies
View Related
