I am trying this new project out, and i have two fields set up, Both will be drop down boxes.
One field is Airline, the next Aircraft Type
If the user clicks on the British Airways dropdown link under the "Airline" field, i would the the Aircraft type field to automaticaly pull up only those Aircraft types
that british Airways flys.
Could anyone help me out?
Help needed: Large Yes/no tables!
I have a form with names of employees, and different skills that employee has.
It is listed in a form as columns with the employee name in rows, and columns for the skills. The form is in fact really great, but I see that the solution might bring problems in the future because of maintainance issues, and field limitations. I add fields frequently, and the table is growing without me beeing able to control it.
I have organized this in only one table, and used the name of the skill as a field. There is many other tables in the DB, but none of them covers this area.
exampletable:
employeeID | auto
employeename | text
Hireddate | date/time
Fireddate | date/time
baking | yes/no
cooking | yes/no
driving | yes/no
juggling | yes/no
With this solution I get the skillname as a heading, and the name as a column with lots of yes/no values. I love the filter functions, and the form is quite nice...
exampleform:
Employee baking cooking driving juggling
employeeeID1 yes no no yes
employeeeID2 no yes yes yes
employeeeID3 no no yes yes
employeeeID4 yes yes yes no
and so on.
Is there a way to organize this so that the skill name comes in the top row, the results get listed as shown above when the skill is placed as a field value, not a field itself?
I have looked for a solution with many-to-many relations with 3 tables, but cant seem to make it work right. It is especially the form I have trouble creating with the new design.
I have also tried to make crosstab queries with minor success.
Does anyone have a suggestion on how I can make the relations work.
One employee can possess a great number of different skills, and the skill can be possessed by a great number of employees. This is not a difficult issue by itself, the problem is to make the form as shown over.
Thank you, in advance.
I have a table and would like to merge all the fields into one text field of the table after the insert. I'm using adp (access/sql server).
When using standard sql the null values give a problem and i don't want to use the if clause as it will give too much coding.....
Hello,
I have 2 tables:
tblProviders
ProviderName
ProviderAddress
ProviderType
LocationCode
FacilityType
Etc...
tblFacilityType
ProviderType
Location
FacilityType
In tblFacilityType, the FacilityType field is a combonation of ProviderType and Location. Each of those fields are alphanumeric. What I want to do is have the FacilityType field in the the tblProviders table to look at the Location and the ProviderType fields in the tblFacilityType table to determine what the FacilityType field in the tblProviders table should be.
Hope that is understandable.
Thanks in advance for any assistance.
Hi All,
I am a novice at MS Access but I am learing new things every day.
I am using MS Access 2000 for this DB.
If you need to see the DB let me know and I will strip the sensative data out of it and post or send a copy. The DB is for a small retail sales store.
What I want to do is something like this::
If [Tables]![Sales Entry]![Item Tax] [= $0.00] Then copy [Tables]![Sales Entry]![Item Price] to [Queries]![Taxable and Non]![Non-Taxable] [Else[If [Tables]![Sales Entry]![Item Tax] [> $0.00] Then copy [Tables]![Sales Entry]![Item Price] to [Queries]![Taxable and Non]![Taxable]]] End If
I want to split up the Item Price field by checking to see if Tax was applied or not and copy the Item Price to the proper new fields in the query called "Taxable" and "Non-Taxable". Then in my Report I can sum each of the new fields by date.
Thanks
Ron
Here is what I am trying to do.
I have a table with routes that tell me which carrier to use. This is based on from what state to what state it is traveling as well as the service lever (next day, standard, etc.)
How would I code for the value in the carrier field to populate based on the state and service level values entered.
Should I write a query to get the results? Or should it be code in an event on a particular field?
Whichever way works, any ideas on how to get started?
Thanks.
Gary:confused:
I am trying to get one of my fields to calculate this expression which includes other fields:
If "Financing Type" = 1 or 2 THEN 20% * "Loan Amount" OR
If "Financing Type" = 3 or 4 THEN 100% * "Loan Amount" = 2,000,000.
I thought that this would work, but it only works in queries or reports.
=IIF([FinanceType]<3,[LoanAmount]*.2,[LoanAmount])
I would like this to work in the table.
I would like to have a field data type lookup data from another table but display them on this one field. ie on member information table there is a FirstName & LastName fields separately I would like another table with Member field lookup member information and pull both FirstName and LastName fields on one field. How do I go about this?
View 1 Replies View RelatedHi there,
I've been working on this issue for quite sometime. I've tried different methods, such as the DLookup function, but I think I'm doing something wrong. Let me explain what I'm trying to do:
This is a Work Order, the user will Select a Customer (Table Customer: Contains CustomerID and Customer Name, address, etc) from a drop down box. When they do this, I have another textbox which is designated for the Address. I would like the address to automatically populate. but its not quite working. I've tried it with the DLookup function (I entered it into the control source of the textbox). I entered the following syntax:
=DLookup("Address","Customer","CustID=" & cboCustId)
It keeps coming up with an error. I have no idea why.
Now this is just the first half. The second half I have a subform on the same main form, which lists the equipment they have on their site. The database has a number of customers with many equipment for each of them. How do I make sure that the subform only populates equipment associated with the Customer that was selected on the main form?
I hope someone is able to help me.
Thanks a lot!
I am trying to put in a validation rule so that data in one field cannot be less than that in another.
e.g. i want to make a validation rule so that a phone call end time cannot be before the phone call start time.
Can anyone help???
Hi,
I have an old database for generating service reports which has:
Report No
Date
Customer
Site
In separate fields which is fine
but also
wrk1
wrk2
wrk3
etc.
with a separate field for each printed line on the page
I would like to merge all these into one memo field while retaining the previous data which goes back 15 years (it came from a MSWorks 2.0 DOS db which is the reason for the weird structure I think)
Is there any way to write a macro or code to selectively merge some but not all fields in a table?
I've already copied the db and modified it after clearing all the old data, it works fine but I'd like to have access to the old data in the new format
TIA
Kim
So I have a table containing 2 fields containing the first name and last name of a customer. I do need to add these names in the same table into a new field called Name. Can someone help me out?
View 5 Replies View RelatedHi,
I just started doing something in access and need your help.
I have created a database and need to create a query which will sort all important fields within a certain time period. I have a date field and in the criteria field I wrote an expression:">Forms![Insert_date_form]![starting_date_field] and < Forms![insert_date_form]![ending_date_field]"
Its purpose is to show all fields which date is between this to dates. User will have to enter this dates in a separate form(Insert_date_form) which will store it in a small database containing only these two fields, e.g. generate report of all employees that are started working within a period between starting_date and a ending date. When I try to create and open a report based on this query I've been prompted with a small dialog which says: Enter parameter value.... for starting_date_field and same for ending_date_field. I don't need that. These dates are allready entered by user in a insert_date form and stored in its databese.
Is there a beter way to do this?
Thanks!
P.S. Sorry for my poor english. It's not my native language.
drvRegion either contains "EUROPE", "AMERICA" , "ASIA" or is NULL.
if drvOrderSource="Whatever" THEN update drvRegion with "EUROPE", "AMERICA" , "ASIA"
Right now I'm getting a circular reference error :/
please advise. Thank you.
I am attempting to create a report that breaks down a field of 'ClaimID' numbers into groups of x. In the sample report below x = 12 and the report will apportion the first 12 'ClaimID's to the first page and textboxes with extra large fonts will signify the start and end of 'ClaimID' numbers for that page. These sheets are used for sorting and pulling guides at our local Xmas project and x will vary depending on the size of the facility we're using.
http://i35.photobucket.com/albums/d186/HoodRiverDude/SortReport.gif
I believe I can attain my goal if I were able to create a query which broke down the field 'ClaimID' into multiple fields based on x. The sample below represents this breakdown creating multiple records with x, 4 and 5, amount of fields.
http://i35.photobucket.com/albums/d186/HoodRiverDude/SortReport2.gif
Does anyone know how I can create this query, or perhaps suggest another way to create this report using the existing 'ClaimID' data.
Thanks in advance,
Aldo
I am new to Access and was wondering if there was a way to parse the data from a field into two fields.
I have a field named tName which contains both the first name and last name of a person.
How can I get the corrosponding last name into a new adjacent field called tLastName (this field doesn't exist yet)?
Thanks so much in advance,
Paul
P.S. What if the the person doesn't have a last name? or if they included a middle initial?
I know, it's messed up. This DB has a bad setup.
Hi,
I am trying to combine 2 text fields into a new field (concatenate) so that I can use it as the primary field.
Can anyone pls help.
If I have three fields
Surname - text
Given name - text
Dateofbirth - dd/mm/yyyy
how can I generate a field consisting of
surname(then)first letter of given name (then) year of birth
(ie SMITHK66)
Thanks in advance
I have a table that contains Investigator1,Investigator2 and other fields
And another table that contains PID, Rank_Name_Family and other fields
I am making a query in that query i want to get the names of Investigator1 and Investigator2
Investigator1 and Investigator2 is the PID
for example
Investigator1 = 1
Investigator2 = 2
PID = 1 Rank_Name_Family= Roy Jalbout
PID = 2 Rank_Name_Family= Rony Jalbout
i tried this two solutions but the result is error
Solution 1
Code:
Name1: DLookUp([Rank_Name_Family];"tbl_Personal_Information";[tbl_Personal_Information].[PID]=[tbl_Driving_Expert].[Investigator1])
Name2: DLookUp([Rank_Name_Family];"tbl_Personal_Information";[tbl_Personal_Information].[PID]=[tbl_Driving_Expert].[Investigator2])
Solution 2
Code:
Name1: DLookUp([Rank_Name_Family];"tbl_Personal_Information";[tbl_Personal_Information].[PID]=[Investigator1])
Name2: DLookUp([Rank_Name_Family];"tbl_Personal_Information";[tbl_Personal_Information].[PID]=[Investigator2])
I have a form (frmFeeIncome) based on a table (tblFeeIncome)
On the form I have three fields which are FeesMonth, FeesYear, FeesUK (currency), FeesElsewhere (currency) and FeesTotal (currency). Each new record is entered like this
January 2015 25000 25000
What I need is for the total in the FeesUK field and FeesElsewhere field to be added up and stored in FeesTotal when FeesTotal has GotFocus
The FeesTotal has to be stored (I know it shouldn't be but it has to!)
I have tried the following in the GotFocus Event procedure but it does not work.
=[FeesUK] + [FeesElsewhere]
I've tried many things (compact and repair, Nz(,0), sum()) and I can't get it to work.
I entered =[field1]+[field2] but it behaves like if I had entered =[Field1] & [Field2].
Yes instead of adding the numbers it just puts them together.
For example: Field1=3 and Field2=5 instead of field3=8 it shows Field3=35.
I have a field in an Access 2003 table that has several image names in it separated by "; " (semi colon and space). The thing is, I need to split them up into their own fields..My table name is "ONE BIG TABLE" (will be exporting data from one table for CSV).My starting field name is "ALT_IMG".An example of the contents of a record within "ALT_IMG" is
Code:
/AAG70260G05_2_1.JPG; AAG70260G05_3_1.JPG; AAG70260G05_5_1.JPG; AAG70260G05_6_1.JPG; AAG70260G05_7_1.JPG; AAG70260G05_8_1.JPG; AAG70260G05_4_1.JPG
What I need is to split these up into their own fields. I can create new fields to populate, I just need to get them in the fields and to remove them from the original ALT_IMG field after moved. There can be as many as 0 or 1 to 20 images in the ALT_IMG field.
Ultimately I was thinking about making new fields named "ALT_IMG_2", "ALT_IMG_3" (up tp 20) and then making an update query of sort to anything more than 1 image to the next field. Meaning if there are two images, then the first stays where it is and the second is moved to ALT_IMG_2. If there are three then the first stays where it is, the second goes to ALT_IMG_2 and the third goes to ALT_IMG_3.Now I know that there are benefits of having multiple tables but I need this to end up in the same table.
I've got a form where I want to add an employee and I have three fields (EmpFirstName), (EmpLastName), and (EmpCode).They all have their own control source from a table but what I want is EmpCode to automatically fill in after they type a first name and last name and not be editable. Smith, John and the empcode will fill in with smijo (3 of last and 2 of first). I understand this is probably completed using Expression Builder (not sure on the command) and how do I make it so Empcode still receives the data entered??
View 2 Replies View RelatedI'm an excel user and in using excel I can take two fields and combine their data in one field. I've got about 6 database files with anywhere form 1000 to 3000 records that have already been prepared and imported into access database files .mdb and I realize that the firstname lastname fields need to also be combined into a contact field with both names. In Excel it's easy in access I'm a little lost. I was looking to an update query however I can't find instructions on what I'm trying to do.
View 2 Replies View RelatedI am using Access 2013 and have created a music inventory database.I have a question about formatting some text in a table. One of my tables has the following fields:
tblArtist
Artist ID
DVD Number
Artist Last Name
Artist First Name
Artist Full Name
In my form, I want the artist's name to appear like this:
Mozart, Wolfgang
Dylan, Bob
Beatles
Aerosmith
I know how to format the table to do this, and I know how to construct the form for this. To format the name, in the tblArtist table, I use the following expression in the Artist Full Name field:[Artist Last Name] & ", " & [Artist First Name]
If an artist has only one name, such as Aerosmith, I enter it in the Artist Last Name column. Artists such as The Beatles or The Rolling Stones, I just omit "The."When a one-name artist's full name appears in the form, there is a comma after their name.
Is there a simple way to eliminate the comma if there is no text in the Artist First Name column? If so, what is the simplest, easiest, most practical novice-friendly way to do this?
Hello,
I have a few tables containing different exam data for students - and a unique identifier with each. I need to add another unique identifier (which is already specified from another system).
Now, can I use the existing identifier (UPN) to automatically put in the new identifier into a new field (AdNo).
If that makes any sense at all, I will be surprised...thank you!