Help - SQL Lookups (Text Or Integer)
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 Replies
ADVERTISEMENT
Nov 23, 2014
I am having a query which having a category field like Electrical, Sports, House hold etc.
What I want that if i select Electrical then it should return 15, if Sports then 10 and so on i think this could be done through this below mentioned VBA but it need change from integer to text...
Option Compare Database
Public Function fncGrade(intNum%) As String
Select Case intNum
Case 0 To 1: fncGrade = "Same as Previous"
Case 2 To 32: fncGrade = "C-3"
Case 33 To 40: fncGrade = "C-2"
Case 41 To 50: fncGrade = "C-1"
Case 51 To 60: fncGrade = "B-3"
Case 61 To 70: fncGrade = "B-2"
Case 71 To 80: fncGrade = "B-1"
Case 81 To 90: fncGrade = "A-2"
Case 91 To 100: fncGrade = "A-1"
Case Else:: fncGrade = "X-X"
End Select
End Function
View 5 Replies
View Related
Mar 18, 2013
I have a text field like, 11242010, and I need to be able to convert it into 3 int fields, day, month, year. I am trying to do this in a query and have create the following three;
DateD: IIf([DATE] Is Not Null,(CInt(Left(Right([DATE],6),2))))
DateM: IIf([DATE] Is Not Null,(CInt(Left([DATE],2))))
DateY: IIf([DATE] Is Not Null,(CInt(Right([DATE],4))))
Time: IIf([TIME] Is Not Null,[TIME])
When I have a value of Null, i keep getting #Error, I think when it's null.
View 7 Replies
View Related
Mar 12, 2013
I have a database that was built 5 years ago that has an auto field with an integer. There are relationships attached to this. I an rewriting it to simplify the database and I need to keep the relationships somehow. I want to make the auto field a text fields. How to work this out...
View 3 Replies
View Related
Apr 24, 2013
I would like to convert a text string to integer.
Lots of posts say to use val, but it is not listed in access 2010
So I am trying,
Creating a field that is numeric then just referring to the string field.
This works good except where it finds actual text. It puts the value "#Type!" in there.
Would there be some kind of function to check for an error or check if the value is text.
View 1 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
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
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 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
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 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
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
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
Oct 11, 2005
What the easiest way to turn text to an integer - ir have vaiable defined as long and an inputbox - want to keep asking for an input until I get an integer.
Have IsNumeric - can this be applied to int.
Or could I use localised error handling?
Thanks
View 9 Replies
View Related
Jul 13, 2005
There's a lot of info that I need to keep track of by just the year. If I enter it as a normal date, I would need to extract the year every time I need to query and then do what ever. Would it be easier just to extract it once, convert to an integer and use it like that through out the system when I need to query by year?
Thanks,
scratch
View 5 Replies
View Related
Aug 30, 2006
I have a field, integer, that needs to be exactly 6 characters long, no more, no less. How would I set this?:p
View 1 Replies
View Related
Aug 11, 2005
I have an autonumber field set up as long integer. The field just reached the value of 32670 and I get the overflow message. I thought a long integer
could be much bigger than that before running into that problem.
I got around it by re-creating the field and starting from 1, but would rather
know why it's doing it so I don't have users without their system.
Thanks in advance for any help.
View 4 Replies
View Related
Oct 18, 2006
I have a form that asks what month you completed a file (04, is april ect.). From this form a report is opened, in my report, I have it read this number and I want to display the month for the number thats entered (if they enter 04 I want april displayed on the report). There is already a ton of data that has the MonthCompleted as 04, so changing that is out of the question.
Thanks, JOe K.
View 3 Replies
View Related
