Simple Lookups In Table
Dec 4, 2006
Simple questions I hope.
1.
What I'd like to do is do a lookup based on 2 fields in my table.
I'm tracking inventory for the company I am currently working with.
I have a computer table with 3 relevant fields:
compID, areaId, locationId
computer table
compID = PK for this table
areaId = FK from area table
locationId = FK from location table
area table
areaId
location table
locationId
areaId
There is a distinct relationship between area + location.
I want a lookup for the locationID, based on the area they have selected.
Thanks for any help on this one. On to the next question related to this.
2. As mentioned above I have an area table controlling the general departments (for lack of a better word). When an area is selected in the table, they have the ability to do a lookup for the relevant locations (implemented in the combo box).
I'd like the user to be able to add a new location item in the computer table, and have that value be added into the location table with the corresponding areaId as well. Am I looking for cascade update or something of this sort?
Thanks anyone. This is my first post here, I'm quite new to microsoft access as a database tool.
View Replies
ADVERTISEMENT
Oct 25, 2006
Probably an easy one that I just can't think my through it. I've been trying to create some (for lack of a better term) cascading fields using the lookup wizard (to eventually be used in a datasheet view/form).
In the main table, the user needs to select a Team (A, B, C), Sub-Team (A1, A2, A3, B1, B2, etc), and a Family (A1 contains bolts, screws, and washers).
The first lookup of selecting a Team (A, B or C) was easy. However after this point I'm stuck. If the user chooses A, I only want the "A" related sub-teams to show in the next lookup. Then based on the sub-team chosen, I want the Family list restricted again.
Any ideas would be appreciated. Thanks!
View 5 Replies
View Related
Apr 25, 2014
I have one attribute in the table and the table is normalized. But we have the same attribute in two different forms, one with a table lookup and the other with a free form text input. I'm trying to rationalize this for a dashboard output.
Do I keep it as one attribute or do I make 2 - one for the table lookup and 1 for the free form input? What are the pros and cons of each?
View 5 Replies
View Related
Jan 7, 2013
I am trying to link two fields in a table here is an example ....
Field A
Values stored could be....
AAA, BBB, CCC, DDD etc
LIST TO LOOK UP
AAA = UDC2
BBB = UDC1
CCC = UDC4
DDD = UDC3
Field B
Needs to lookup listi n Field A
and get correct UDC1, UDC2 etc...
View 3 Replies
View Related
May 31, 2007
Hi there,
I'm pretty new to Access so I hope this question is not to simple/stupid ;)
Ok, I have an Excel Worksheet that I want to import into an Access Table. The column headers are identical! The only problem is:
- the Access table looks up a couple of the columns from other Access tables. When I try to import the Excel data, Access does (properly) report an error.
- Error: the records from the Excel file were added to the Access table but not all values were imported (no values were imported into the lookup columns...)
Any idea how I should procede so that I can import my data into Access?
Thanks!
Steve
View 3 Replies
View Related
May 19, 2005
Hi All
I am trying to develop a database for recording project risks and issues and need to assign an owner to each one. My design stage has three tables for project resources, issues and risks. The Resource table is basically ResourceID; Surname; Forename, GivenName (forename and surname concatenated) Status; Role and DateAdded. When I allocate a risk or issue I want to be able to select the ResourceID from a drop down list that references the Resource table (I have got this working) and then from that ResourceID value to automatically populate another field with the matching GivenName and that is where I am getting stuck. I have setup relationships between the tables based on ResourceID but can't see where I need to be with a query to do this. Someone a suggetsed a union query but not sure of the syntax for this. I know I could have a separate lookup for the GivenName but that invites data capture error and requires the user to undertake an extra input step that could be avoided with a bit of slick programming.
Hopefully the above provides enough information to identify the problem and possible solution - any help on this appreciated.
Thanks
Andy
View 4 Replies
View Related
May 18, 2006
I am a total newbie and possibly in over my head already.
I have 2 tables:
tblEmployees
..EmployeeID autonumber PK
..FirstName text
..LastName text
..DepartmentID number
..HourlyRate currency
tblDepartments
..DepartmentID autonumber PK
..DepartmentName text
Many-to-one relationship tblEmployees to tblDepartments
I need to create a form to enter employees in tblEmployees.
FirstName, LastName, HourlyRate are all straightforward. I'm stuck (probably a HUGE mental block!) on how to get the DepartmentName into the tblEmployees from the input in the form.
Please help!
View 1 Replies
View Related
Oct 20, 2006
In an attempt to eliminate user input error,I am Looking Up values from a field in a separate table as input for my SEMINAR field. The table containing my Look Up field has only one other field, which is an auto-numbered key field.
When I view my SEMINAR field in datasheet view, the data is as I would expect to see. However, when I use my SEMINAR field in a report or query, it returns the value of the autonumbered key field. The data type for both the SEMINAR field and the Lookup field are text. - Any Thoughts?
Golfer
View 3 Replies
View Related
Aug 18, 2005
I have a subform in continuous mode which has a list of people. I have created an unbound field which has a dlookup into a telephone number table.
What I want to happen is for each person in the list to have their telephone number displayed (not recorded - just displayed) on the form alongside their name. On a single form this works fine, but on a continuous form it displays the same phone number on all the rows (presumably the first person in the lists number).
Can anybody advise how I can get the dlookup working for each record in the continous form.
Thanks in advance,
Adam.
View 14 Replies
View Related
Apr 27, 2006
Hi Guys,
First off, a big thanks to everyone on the site. I have learnt a lot since first discovering this site a few weeks back.
Problem:
Having understood that it is better to create SQL lookups to queries of tables rather than to the tables directly, I am having trouble understanding what value I should store in my main table, a text value or the ID (number) (of the text value.)
I have an asset table with a field Equipment Type. This field looks at a query of the EquipmentType Table.
Would it better to store the text value "Printer" in the main asset table (in which case I can query the table directly but the field will use up more space (i.e. 25 char)?)
or
Store the Equipment_Type_ID "1" relating to the Printer (will use up less space, but mean any queries querying the actual name would have to include the EquipmentType table).
Any advise would be much appreciated.
:confused: My thought was that I should go with the ID as otherwise I will be storing duplicate data. If this is the case, when would a text value be more suitable.
View 1 Replies
View Related
Jan 2, 2015
We are on Win7 with Office 2013 (32-bit). I have had to migrate my application away from a network that is open to the web. I am now on a more isolated network. My problem is that I can no longer use Outlook for e-mail.
I knew that you can use CDO to send SMTP via a Mail Relay server. We've got one of those on this isolated network... but there is a show-stopper here. When setting up the configuration portion of the CDO message, there are these references using
CDO.Configuration.Fields.Item("HTTP:schema.micro soft.com.... etc.) = value
These references are how you define the SMTP server, server port, authentication method, etc. My problem with this is I can't get to that Microsoft site from the isolated network. The SMTP Relay won't do this lookup for me and I have no general internet access.
When I looked into the CDO library, I found that there were definitions for the fields like cdoSMTPAuthenticate and cdoSMTPServer and cdoSMTPServerPort - but when I attempt to use them, Access doesn't seem to like them even though the CDO library is checked in my references list and those values are defined. For what it is worth, the library file says it is version 6.6.7501 (or something close to that), so it is not the older version. The revision date is 6/6/2012, just about right for a file that would have been included in Office 2013's library set.
How to use CDO when you can't do the HTTP lookups? (I know how to use it when the lookups work...)
View 5 Replies
View Related
Jan 23, 2006
Hi,
I'm fairly new to access.
I'm trying to design a database for my work network which will show how nodes are connected. I currently have the following tables:
section - refers to agreggate cable links between buildings/sites
media - refers to individual links
Network_element - refers to switches/routers
section (section_id PKEY,media_type, start_site, start_building, start_cabinet,
end_site, end_building, end_cabinet).
media (section_id PKEY, from_pair, to_pair, from_ip, to_ip)
Network_element( Name PKEY, MAC, IP)
I'd like to be able to make sure access will only allow from_ip and to_ip to be entered if there is a corresponding value in the Network_element table, however I do not know how to enforce this.
Any help much appreciated.
Thanks
Dan
ps - if anyone has done something similar, I'd be keen to see how they gone about it.
View 1 Replies
View Related
Jan 27, 2006
Can someone please tell me if there is a way to perform lookups in Excel from an Access table without first exporting the data into Excel? I have a form that is being exported into Excel from a pricing tool our company uses from an outside vendor. It is important the users have the ability to perform complicated calculations and are not at all experienced in Access. There are several data sources that are well over the 65,000+ rows limited by Excel. I need to either prove or disprove whether it is possible to perform lookups between Office applications. If it is possible to lookup between applications, I would appreciate any sources known to learn how to do this. If not possible, I would like to be able to confirm that fact to my manager.
Thanks for any help that can be offered.
tschultz
View 1 Replies
View Related
Oct 18, 2004
I am trying to create a query for a form which returns a 'calculated' value.
One table contains ProjectNo, SchoolType1 and SchoolType2
Second table contains SchoolTypeID and SchoolTypeName
The SchoolType1 and 2 are number values which reference the SchoolTypeID.
If I query for project number and school type 1 and 2, I get the actual names for the school types (i.e. 33.3333, elementary school, learning center). But, not all project numbers have two school types (i.e. some are just elementary schools). If I create a query for project number and [iif (SchoolType2 is null, SchoolType1, SchoolType1&", "&SchoolType2)], I get the number ID's returned instead of the names (i.e. 33.3333, 3, 6) instead of what I'm looking for which is the names. (I get number ID's returned if I do something as simple as 'SchoolType1 & " " & SchoolType2')
I hope this makes sense. How can I get it to return the school type names when I do any kind of calculation on the school types?
Thanks
View 3 Replies
View Related
Oct 22, 2013
I have a DB for clients that contains all kinds of info. It is getting bigger and harder to keep track of things by their ID number. The problem I am running into is that ComboBoxes and Lookups can only bind 1 column. My DB is based on Categories, then Sub-Categories, then Sub-Sub-Categories. I have done this because there is multiple criteria for each customer.
For Example, I have a Customer = ID; Location = Location_ID; Employee = Employee_ID so everything is tied to the ID, but on my phone list I need to have a list of Employee's that show the name that is tied to ID and Employee_ID.
ID is unique to each customer but I have duplicate Employee_ID for example Customer 1 has Employee 1 and Customer 2 has Employee 1, so when I use the ComboBoxes or Lookups I get Customer 1 - Employee 1 for Customer 2 - Employee 1 if I bind to Employee_ID, if I switch that to ID it is completely messed up. Now I don't care if the table stores a number, but I would like the form to display the name.
I am trying to get my forms to display a name and not an ID number and when you enter new information you can select a name and not a number.
View 14 Replies
View Related
Oct 21, 2012
I have 3 tables: [SurveyVendor], [Surveys] and [SurveyResults]
SurveyVendor has 2 fields:
[VendorID] - PK
[VendorName]
Surveys has 3 fields:
[SurveyID] - PK
[VendorName] - FK
[SurveyName]
SurveyResults has 3 fields:
[SurveyResultID] - PK
[SurveyName] - FK
[SurveyQuantity]
I used the LookUp wizard to establish the relationships, and I chose to include 3 fields when linking [SurveyResults].[SurveyName] to [Surveys].[SurveyID]. I'd like the ID, Survey Name and Vendor Name to be displayed when making a choice in the combo box. And I would like the column to only display the SurveyName.
However, what's happening is the VendorID shows up instead of the VendorName when I click the combo box for [SurveyName]. And the SurveyID shows up in the column results, rather than the Survey Name.
View 3 Replies
View Related
Aug 7, 2013
We have our access database with a bit of a messy structure?
We use our database to record sizes of our product. I am hoping to improve it by adding the items we have in stock to prevent manufacturing more.
Basically our main database called "Make & Model 1" has a list of various makes and model numbers, each model number lists various information needed to manufacture a replacement part.
Customers order the part and these part details are entered into a table called "Order Detail" What I would like to happen is that when the details are entered a calculated field adds the data entered to a text string. I need to match the text string to the same text string in a table called "stock" as there could be one part that matches hundreds of models.
Basically the "profile" "Colour" "height" and "width" make the string and this is what I need to match and tick a box / populate the number of items in stock. Eventually I would like this to reduce by the amount ordered but lets do one step at a time.
I am using Access 2010
View 1 Replies
View Related
Sep 19, 2005
Ive done this before in College but it was along time ago and i cant remember what i did
Bus say I have a table with Businesses and i need each business to be able to have multiple records associated with it.
So i have two tables one named 'businesses' and one named 'orders' how do i make sure that when a business makes an order the data in 'orders' is associated witht the releveant business in the 'businesses' table
View 3 Replies
View Related
Jan 26, 2006
I really suck at table design and coding, and access. I'm a techie that usually stays away from all this development stuff; it makes my head hurt. I'm a lot happier knee deep in an Exchange migration than developing even the most basic of databases as I suck at it (think I mentioned that already) However, i've been roped into doing a tiny little project for a friend. Below is the process I went through for the data / table design. When I started to create the relationships it got messy with the many to many relationships. Am I over complicating it or am I on the wrong track? Any assistance from you super-clever developer dudes would be greatly appreciated and would affirm your superiority over us humble techies yet again. :)
I started with the raw data:
Supplier (e.g. Builder's Supplies Co.)
Item (e.g. Plywood, Sharp Concreting Sand)
SupplierItemCode (e.g. WWE3428X)
Unit (25KG, 9x2440x1220mm)
Cost
Here are the main relationships:
A Supplier can have many Items, SupplierItemCodes and Costs
An Item can have many Suppliers
An Item can have many Units (e.g. Sharp Concreting Sand comes in many different weights (Units))
A Unit can have many Items (e.g. the unit of 25Kg can have several different types of sand or other items associated with it)
So what I did was to create four tables (* - primary key, ( ) foreign keys):
I created the ID fields in each table even though Supplier, Item, Unit and SupplierItemCode are all unique values that could be used as primary keys (or would that be bad practice; I told you I sucked at this?).
Supplier Table
-------------
*SupplierID
Supplier
(ItemID)
Item Table
----------
*ItemID
Item
(SupplierID)
(UnitID)
Unit Table
----------
*UnitID
Unit
(ItemID)
SupplierItemandCost Table
-------------------------
*SupplierItemID
SupplierItemCode
SupplierItemCost
(ItemID)
(SupplierID)
View 2 Replies
View Related
Jul 13, 2006
I haven't built a relational DB in years and I realise I'm pretty damn rusty at it. Any help would be appreciated.
Basically I've compiled a list of software applications our offices use, and I'm compiling which PCs have which software installed. Ideally I'd end up building a form with checkboxes for common software apps and text fields holding serial numbers or login IDs where applicable. But I'm stumped already on just the table layout. I currently have:
tblSoftware:
SoftID (Autonumber) - Primary Key and identifier for individual software apps
Software (Text) - Software title
Description (Text) - Any additional information (Licence, etc)
tblComputers:
NetID (text) - The Network Identification of the PC, also acts as Primary Key
User (text) - Name of user assigned to the machine
and having a One to Many relationship between NetID and SoftID. This should be a simple DB but I've been separated from all my manuals and the net is proving more useful for specific fault finding.
How can I set up the tables and relationships so that a query can be used to update the software for each computer?
View 3 Replies
View Related
Feb 12, 2007
Hi, I was asked to help create a very simple Access DB for a small restaurant to help them keep track of inventory.
The only things that the owner wants to keep track of are:
1.Item Catalog
2.Date and Quantity of Item IN or OUT
3.Current # of Items
My problem here is that there are 5 Item categories, each with a relatively lost list of Items under each one.
1.Drinks
2.Ice Cream
3.Kitchen Supplies
4.Grocery
5.Etc.
Will it be advisable for me to create an Item Catalog TABLE for each of the category instead of piling all the Items into just one Item Catalog TABLE?
The Designs I’m currently considering are:
A.)
TABLE SET 1 : Catalog of Items
Fields (ID, Name)
•TABLE Drinks
•TABLE Ice Cream
•TABLE Kitchen Supplies
•TABLE Grocery
•TABLE ETC
---------------------------------------------------
TABLE SET 2 : Movement IN/OUT of Items
Fields (Movement ID, Name, Date, IN-Amount, OUT-Amount)
•TABLE Drinks
•TABLE Ice Cream
•TABLE Kitchen Supplies
•TABLE Grocery
•TABLE ETC
B.)
TABLE 1: Catalog of Items
Fields (ID, Category, Name)
TABLE 2: Movement IN/OUT of Items
Fields (Movement ID, Name, Category, Date, IN-Amount, OUT-Amount)
I’m really more inclined to choice A because I just re-learned Access last month and will be creating my first serious database just now. I don’t want to choose a more complex table structure (choice B) that might cause problems with the DB later (as it will really be used to keep track of real-life inventory and might screw up their operations if it goes haywire). Is Design A viable? Or flawed?
Another question I have is with the structure of the Movement – IN/OUT table. Is this workable? My thought is just to let the user enter something like:
Drink [date] [IN] [OUT]
Coke [date] [2] [0]
Coke [date] [0] [1]
In the database, then compute current Quantity of the Item using a query (Sum[IN] – Sum[OUT]) . Will this work? I’m a bit apprehensive with this because I’m thinking I should have another TABLE that will store the actual inventory Quantity instead of the Quantity just being computed through query. But doing another TABLE for Quantity does complicate stuff a lot since it will have to determine which Quantity value gets added to or subtracted from based on many criteria. Will it be OK just to use the query to compute Quantity left?
Thank you so much for any help on this!
View 14 Replies
View Related
Jan 5, 2006
I have a very simple database for recording data collected from a questionnaire. The database comprises of
table1: QUESTIONNAIRE
fields: QUESTIONNAIRE ID
QUEST_1 ........
up to QUEST_75
table2: VALUE (1 to 6)
ANSWER (strongly agree, agree etc)
All of the 75 question fields in table1 record the same information, and all look up to a table2 which stores the 6 values: agree (1), strongly agree (2), neither (3), disagree (4), strongly disagree (5) and spoilt (6). As the data is being entered into a from using option groups, the database stores the value rather than the text in each QUEST field.
Pretty straightforward I thought, but when I come to query the database I am getting into such a muddle I feel like my head is going to explode!!
The results I need to produce should look like this:
ANSWER QUEST_1 QUEST_2 ....all the way to QUEST_75
agree 34 3 25
strongly agree 45 5 34
neither 3 2 4
disagree 3 4 32
strongly disagree 3 3 5
spoilt 0 1 0
Basically all I want is a count of the number of answers for each question! Given that the answers for all the questions are the same I thought this would be a piece of cake, but alas no.
I thought I would simply be able to crosstab my results from the questionaire into a new table, which would have the six rows (agree, strongly agree etc) and the questions as fields. The values should be the count of "agree" etc. I can quite happly have one field and the counts of each answer but when I try an add any other fields it just duplicates the counts!! I have sat staring at my query and am at a complete blank as to how I resolve this seemly simplistic problem :confused: .
Please if anyone has any ideas why I am in such a muddle over something which seems so simple I would really appreciate their help!
Many thanks
View 13 Replies
View Related
May 15, 2006
created a main form called "frmPatientDemographics" that contains
txtFirstName (Text Box)
txtLastName (Text Box)
txtDOB (Text Box)
txtCountry (Text Box)
cboGender (Combo Box)
it's record source is a table called "tblPatientDemo" that are populated based
on the information place in the above text boxes
Here is my problem
I have a subform called "frmPatientLanguageSub"
Source Object......... frmPatientLanguge
link Child Fields..... PatientID
link Child Fields..... PatientID
When list box Multi Select is set to "Simple" and I add a new patient and click on
multiple selections in the list box it does not populate the "tblPatientLanguge"
But if I change the Multi Select is set to "None" it populates the "tblPatientLanguge"
for that patient
Any Ideas why access populates the "tblPatientLanguge" if I set the Multi Select property to "None" but not for "Simple"?
If so How do I make access poplulate when the table when muliple selections are selected in the list box?
View 7 Replies
View Related
Aug 20, 2005
Hello All,
At work we have a large and messy Contacts list so I decided to set one up using a database.
At present I have 3 tables:-
Companies (custID,companyname,address,etc)
People (nameID,firstname,middlename,lastname,custID)
Phones (phoneID,phonetype,areacode,number,?????)
My problem is this, Some of the phone nos belong to the individuals
and some belong to the company. If a person is replaced at a company I need to reasign the company phone nos to the new person whilst retaining the individuals and their personal phone nos. If a company is deleted I need to delete only the company phone nos. and if a person moves within the company I want the company nos to reasign to the new replacement but keep the personal nos of the individual. Now I see its going to be more complex than I thought.
Can anyone help me with the table layout and links. (nb this is only a simple database relating a person to a company without using departments etc.)
Its main use is to provide phone nos names and addresses quickly.
Many Thanks
Peter
View 4 Replies
View Related
Jul 5, 2006
Hey guys.
Seems simple enough, I just can't figure out how to do it. How can I display the total number of table entries in a textbox on a form? thanks!
View 1 Replies
View Related
Jul 30, 2014
I need a simple audit capability
I have a table (Called Master Active List)
I have an empty duplicate of it called History of Master Active List. (Identical fields, with the addition of a distinct ID (primary key)
I have a form where the master can be changed (Called customer details)
When any change is made I want to post the whole record to the history table
1 - Am I correct I no longer need a form based one,(office 2013) and can do one on the data table itself?
I want to capture everything, can this be done with a wildcard, or do I need to go line by line?
The master table has a ton of fields.
View 3 Replies
View Related
