My Sales! table has fields [ItemType] and [Price] and VAT! table has [ItemType] and [VATRate].
Both tables has a common field which is [ItemType].
I am trying to make a query which will calculate the VAT figure by multiplying the Sales table [Price] with the corresponding [VATRate] in the VAT table by matching the [ItemType] in both tables.
I tried DLookup but couldn’t find a solution. How do I accomplish this?
how to do a particular thing in Access 2010 (I don't even know if it is possible).
I have a table named PRODUCTS:
ID_PRODUCT (primary key, autonumber long integer)
ALLOWED_OPTIONS (multi value text lookup field: "Option 1";"Option 2";...;"Option 9")
So I can store, for each different product, none, one, or more options to let the customers choose from.
I have a table named ORDERS:
ID_ORDER (primary key, autonumber long integer)
FK_CUSTOMER (foreign key, linked to the primary key of a CUSTOMERS table; represents the customer that places the order.)
FK_PRODUCT (foreign key, linked to PRODUCTS.ID_PRODUCT; represents the product that the customer has choosen)
CHOOSEN_OPTION (lookup text field; the customer must choose ONE option among those allowed for the product he has ordered)
The problem is that I would like the CHOOSEN_OPTION field to show as a combobox, listing the values stored into PRODUCTS.ALLOWED_OPTIONS, so that when a customer buys a product, he can choose only among the options allowed by that particular product.How can I manage a multi value field to populate a combobox, in which every item stays on its line? If I use, as a query to populate the combobox:
select [PRODUCTS].[ALLOWED_OPTIONS]
from PRODUCTS
where [PRODUCTS].[ID_PRODUCT]=[FK_PRODUCT]
I obtain an empty combobox.If I refer to the last field as [ORDERS].[FK_PRODUCT], Access asks me to type a value for "[ORDERS].[FK_PRODUCT]", treating it as an unknown parameter.I think that the problem is that when the combobox expands, the record is not committed yet, so FK_PRODUCT is unknown (NULL?). But this happens even if I commit the record typing something in FK_PRODUCT and then I re-enter the record and I expand the CHOOSEN_OPTION combobox, that is still empy although FK_PRODUCT exists, now.Is there a particular syntax to refer to a field in a record not committed yet (something like "THIS." or "ME.")?
I have a database that has 2 tables. Table A and Table B. Table A is my primary table. On this table I have 2 fields. The first field is a LOOKUP Field that looks up information from Table B and displays my selection in the field on Table A. Then using DLOOKUP I automatically input the information in the Second Field on Table A based upon the selection from the First Field.
This is working mostly correctly. However, the problem is, when I click on the next record in the table, it automatically changes the Second Field on that record to the same value as the record before it and continues this trend each time I click on another record. This occurs without me making a selection in the first field. If I make a selection in the first field it does change the Second Field to the Correct Value, but then the next Record has the same issue.
How do I go about fixing this so it doesn't change the value with the change of the record. Only change if I change that particular field within that 1 record?is there a way to restrict the Value's in my lookup field to only include the Values from Table B that aren't already in Table A?
I am very new to Access and any help would be appreciated. I have been through the entire Access Bible and many forums but can't find the answer to my specific question.
The project:
I'm setting up a database for tutors and students. Tutors will, via the web, enter data about a specific tutoring session (studentName, subject, sessionLength, etc.) Then at the end of the month a report will be generated detailing hours tutored and total compensation.
The problem:
Students pay varying rates based on subject (math or verbal). So in the Session table each record for each tutoring session has an empty field called payRate. This is the rate the student will pay based on subject. So I need this field to "fill in" automatically based on the 'student' and the 'subject' from the same record. Then I can multiply the field by sessionLength and get a totalPay field for the session. Add these at month's end and pay the tutors.
I've set up a lookup table called 'subject' with three fields (student,subject, payRate) and I have the student and subject combined as the primary key. Am I on the right track here? If so, how do I link the tables to get that Session 'payRate' field to fill in automatically. This is where is breaks down for me. I've tried the lookup wizard but all I seem to get when I open the Session table is an empty PayRate field with a drop down box containing the entire subject lookup table.
Sorry for being so long winded but I need help from someone better at his than me. Thanks in advance.
hi, plz i need a fast help
i have 3 tables in access:
Country Table: fields:country Id, country name
City Table: fields: City ID,Country Name(lookup field to Country Table), City Name
Street Table: fields: Street Id, country name(lookup field to Country Table), City Name(must be lookup to City Table where City.Country name = Street. Country name), street name
my question is: how can i make the lookup of the City Name in the Street Table, in a way that only cities of the specific country of the record active are displayed ???
example:
Country table:
1 Lebanon
2 KSA
3 Kuwait
City Table:
1 KSA jeddah
2 KSA Riyadh
3 Lebanon Beirut
4 Lebanon Tripoli
STreet Table:
1 KSA (???) Sary (the ?? must be a combo Box that show only the cities of KSA: jeddah, Riyadh..which are in the City Table)
2 KSA (???) sondos (same as above)
3 Lebanon (???) hamra (the ??? must be a combo box that show only the cities of lebanon: Beirut and tripoli...which are in the city Table)
i have created all the fields in these tables...and when i put the field City Name as lookup, i am failing to mak eit read correctly from the city table according the the country of my active record..
plz anybody knows how to do this query???
I'm having a big struggle with adding a multi-value lookup field to a table. I need to provide a long pick list of items (from a source table) but these items must not be output (in forms or reports) in alphabetical order, they need to remain in the order that they are in the source table.
Access wants to alphabetize the output even if a I add a number field to the source table and sort by that.
Is there any way I can persuade access to just give the selected items back in the same order as in the source table ?
Hi,
Is there anyway to have a single field in a table which is populated via the use of a lookup onto another table, but allowing multiple value selection out of the lookup table and populating those into the field...
For example
Table 1 is customer details
Table 1 field 3 = areas of interest
Table 1 field 3 is populated via a lookup into Table 2 interests
Table 2 has 4 records
Sport
Household
Motoring
Family
I want to be able to select 1 or more of the Table 2 values and populate them into Table 1 Field 3....
Help my head hurts....
I have created a lookup in a field in a table:
select id, bike from tblbikes.
Column count 2
Width 0,3
When I try to sort the table by bike:
I receive the following error: Type mismatch in expression.
Is there some way to sort a field with a lookup.
I have a table holding a list of post codes, and their servicing depot EG -
ID Postcode Depot
1 AB10 Edinburgh
2 AB11 Edinburgh
Then a form, which has a field for depot ( Fld_Depot )
What i would like is, when the user clicks on the field, a msg box prompts, asking for them to input a postcode
Once the postcode is entered, it populates that field with the relevant depot from the table....
In Access, when using Look Up function, Table/Query, how do I make more than 1 field from the query appear in the drop down list?I will be doing the same thing for students and some other tables. But for this example I am only wanting to show instructors first and last name and their ID number in the drop down.
I am using
Code:
SELECT instructors.first, instructors.last, instructors.ID FROM instructors;
in my Classes table.
What am I doing wrong?
I would like for it to show their instructor first and last name but actually put the ID in the field as I will be using that key for various relationship based functions. Show the first and last name to the person who will be using the database, but actually put the ID in the field so I can use it as a common field across other tables.
I have two tables, "Membership" and "Class." The Class table has a class ID, class name, and cost of the class.
In the Membership table, I have a field to accept a class ID that is entered by a user. I also have a column called Tuition. When the user enters a classID in the Membership table, I would like the dollar amount associated with that classID to automatically appear in the field called Tuition in the Membership table.
I tried the LOOKUP data type and calculated fields, but no luck. I know that DLOOKUP can be used on forms, but I don't see how it can be used directly with tables.
Is this even possible?
I have a table called Locations that lists Countries and Cities:
USA, Detroit
USA, Chicago
USA, New York
UK, London
UK, Liverpool
UK, Birmingham
France, Paris
France, Le Mans
I then have another table for inputing details on people I know. This table would have columns called Country and City. I've figured out how to make the Country column a "lookup column" so that I can only enter USA, UK or France. But how do I make the City column show only the appropriate list of cities relevant to the country that I've selected (e.g. Paris or Le Mans if France is the selected country).
i have a user permission table.that consists of PermissionPK, UserFK, CompanyFK. I also want the username to be automatically filled in?So when a user ID is filled in on the table, it also fills in what that UserID's Username should be?As i need both the UserId and Username text for code that looks at the Environ username.
View 2 Replies View RelatedI am currently working on an instrument datebase, I have a mainquery that takes care of user inputs from a form. The main fields that have been queried on are Type, System, and Manufacturer and they are all look-up fields that contain some null values.
On the same criteria row for these fields, I have
Like IIf([forms]![User Interface].[qtype2]="","*",[forms]![User Interface].[qtype2])
Like IIf([forms]![User Interface].[qsys2]="","*",[forms]![User Interface].[qsys2])
Like IIf([forms]![User Interface].[qman1]="","*",[forms]![User Interface].[qman1])
qtype, qsys and qman are the user inputs from the user interface that returns look-up table values.
This works fine when all 3 of these fields are all filled out for a certain instrument. The problem arise when some fields of the instrument are left blank or is null. The instrument won't show up in a query at all. What I wanted it to do is to show everything including the ones with null fields when the user input are null or "". When the user specifies certain requirement I only want to show the ones that are not null. I understand that putting them on the same row means AND, I have tried to OR them and did not have the result i wanted.
I have a split database and need a field (Combo type) in the table to lookup values from a query in the front end. How do I do this as it doesn't see the querys because the front and back end are split?
View 3 Replies View RelatedVBA Code to go in the on double_click event of a name field in a Subform bound to a table. The subform is just a copy of a data table and within the subform view, When the field "employee name" which contains e.g. John is double clicked, I would like access to Lookup and get John's email in the employee table under field "Email" and launch outlook application and insert it into the To field.
I assume hyperlinking the field can also achieve this similar to what excel does but I am fast learning that what is standard functionality in excel is a whole another story in Access..
KTYPE=text; EXPIRATION_DATE=date/time; OVERAGE=date/time
im doing a report based on a query (which has a few fields, including KTYPE, EXPIRATION_DATE, OVERAGE). im trying to determine the OVERAGE based on the following conditions:
here are the conditions:
if KTYPE="PO", then OVERAGE:=(EXPIRATION_DATE + 6 months)
ie.. KTYPE=PO, OVERAGE:=(01/04/2004 + 6 months) = 07/04/2004
if KTYPE="IAA", then OVERAGE:=(EXPIRATION_DATE + 20 months)
if KTYPE="FA", then OVERAGE:=(EXPIRATION_DATE + 12 months)
and so on and so forth.
I'm very new to Access. What formulas do I use and do I put the formula in the query or the report.
Please help. thanks in advance
I want to create a different rowsource-query for a lookup field (field1) in each record in a subform. The rowsource changes dependent on the value in another field (field2) in the same record. How can this be done?
- I tried to change the rowsource-query in an eventmacro when the focus is set to field1, but this ofcourse changes the rowsource for all field1's and makes the allready selected values unvisible.
- I think I have to include the value of record 'field2' in the rowsource query, but i cannot find a way to include that value in the query.
Something like:
Lookup field1 in the subform contains this rowsource
- SELECT CUSTOMER.Id, CUSTOMER.AGE, CUSTOMER.NAME
FROM CUSTOMERS
WHERE (CUSTOMER.AGE= me![field2]);
me![field2] however does not function
Is it possible to look up 2 field to auto fill another field?
Field 1 is "Exposure" this autofill with "Probability" is user input.
I need to lookup Exposure and Probability and autofill a score in to "Risk Rating". This to stop incorrect data being inputted.
I have a table with all the results combination in it.
Would it be possible to use a Dlookup to look at Exposure and Probability to give me the score
I tried a Combox with autofill. But the power to be would like it done without user input.
Hi,
I have a cross tab query that has field names 6, 7, 8, 9, 10 representing months and a relevance date field with a date.
I have a form and from that form i need to get the value of the last three months from a relevance date field.
I.e relevance may be 01/10/2005 so i would need 3 labels, (labels are already made, but there source needs to be) 10, 9, 8. But the next row may have a relevance date of 01/08/2005 in which case it would be 8,7,6.
Any ideas on how i can do this.
Thanks Adam
Having some problems with a calculated field on a form. The field came from my table but I typed in the calculation (=[cost_price]+[sale_price]) and it works fine but it wont save to my table so when I run a repot off it doesnt have any of the calculated values because they arent saved in my table??? Can anyone help?
Thanks
Tom
How do I get a field to calculate this formula?
I want a script to find the precise current date and from another field find a number and add it to the current date day to make current date + number = date in the future.
For example
Field: Total Nights Hired = 2
Field: Date due back = 23/02/07 + 2 = 25/02/07
I am sorry if I did not explain this well enough. I am totally new to databases but need help in my corsework. I am designing a video rental system.
Any help will be greatly appreciated.
Andy
So here is my problem i am semi access savy but i have having trouble with one thing. At work when we make a schedule there are 5 possibilites for how you want to mark your day. I have created a field using text as my parameters since my possibilities include both letters and numbers. At the end of each week I need to create a calcuation only utilizing the number which is 1 to add up the total shifts in the week.
I have tried some IIF statements with no luck. The 5 possiblities are 1, c,r,a,l. Each of the letters stand for something. When the scheduler runs the query he needs to see the letters or the number for each day. But i can't create a properly working calculated field for adding all of the 1's together only. The letters don't need to be added. Any ideas?
Also I have to run a sum for the total of each day at the bottom so I am running into the same problem there if there is any letters in the boxes. Going to a number field is not an option
thanks for the help
Below is the code I have written to calculate the arrival and departure times based on the users input on time in the air (tripETE) and on the ground (tripGT). The arrival (txtArrTimeZulu) and departure (txtDepTimeZulu) times are unbound boxes since there is no need to save the information. My problem is when the next leg's ground or air times are entered, it updates the arrival and departure times for all the records to that value; overwritting the previous legs times. What am i missing here? Do i need to open a recordset when opening the form or when entering "TripETE" and "TripGT"?....
View 1 Replies View RelatedI have a report which is a statement. I want to be able to have a field that calculates rent, and minus payments. There are a few rent types, Pro Rated rent, Rent brought forward and rent changed, then there is 1 payment type. I am trying to write the sum a few ways, but have not had success. If rent type i= 1 or 2 or 3 I want it to add, if rent total = 4 I want it to minus.
=Sum(IIf([RentType]=1 Or 2 Or 3,[RentAmount],0))-Sum(IIf([RentType]=4,[RentAmount]))
I have a table "Product" and in this table I have two fields "StoragePlaceID1" and "StoragePlaceID2". Both these fields link to the look-up table "StoragePlace"
*Attached Image "Product_StoragePlace" from the Access Relationship Window"*
When I want to add a new Product from my inter face i get the error you can see in the Attached Image "Save_Error".I think the problem is that the Relationship is defined as One-to-Many,there a way to define the relationship as Zero-to-Many.