Help In Creating Lookup Tables For Geographical Locations
Oct 30, 2004
Hi, I am fairly new to ACCESS. I understand the basics, thus i am a novice user.
I am trying to create a new database for managing booking and storing info on various artists.
In my main table where bands' info will be stored I am trying to make data input as efficient as possible and to ensure consistancy.
So, it leads me to ask this question... regarding inputing info of bands' location.
I want to use a lookup table to enter data for:
1 Country (Canada/US),
2 Province/State
3 Cities?
Questions:
1 Whats the best way to organize this?
2 Do i have to create seperate tables for each country and province? Or any kind of suggestion would greatly appreciated.
Furthermore, when entering data on the form, for instance if i select:
Canada --> then I'd want only Canadian Provinces to show in the following field ---> then i select Ontario --> then i'd like only ontario cities to show in the next field....
3 What would require for me to create this kind of thing... Not sure if there are 'if' functions in ACCESS.
Could someone please guide me towards the light?
thanks.
View Replies
ADVERTISEMENT
Mar 20, 2008
Hey all.
I'm having a few issues with creating a Lookup on an Access form. I'll recreate the scenario below as best i can to help with my explanation.
I have 2 data entries. The first is a Post Code (Zip code), and the second is an Area Rating that applies to the Post code. For example:
Post code AB1 8 is awarded a rating of A
Post code SO1 3 is awarded a rating of F
I have already created a table in Excel with all 2,922 Post Codes (AB1 1 to ZE3 9) and the corresponding Area Rates (A to F)
What i'm making is a form that holds customer details. I'd like a combo box or text box that when you type in the Post Code, another text box automatically displays it's corresponding Area Rating.
Code:Post Code: Area Rating:------------- --------------| CM2 7 | | A |------------- --------------
I apologise for my really very poor diagram :P
So the Post Code box is either typed into or a combo box with the list of Post Codes, and the Area Rate box cannot be typed in to, but it automatically displays the record dependant on what is in the Post Code box.
Ideally, these will also be recorded onto the main table (IE not the table with the list of post codes or area ratings, but a 3rd table holding the rest of the details that will be used to create statistics and graphs)
Can anyone help me with how to do this? I've been trying to work it out all afternoon!
Any help is really appreciated.
View 3 Replies
View Related
Jan 27, 2014
I'm trying to create a field with the lookup wizard, however it's a bit more complicated. I need the field to have multiple choices(3) and when a specific option is selected I need some additional choices to appear. For example: Question - have you ever used a specific product. Options: a-yes, b -no, c-other. If option b is selected then thats it, but if option a is selected I need a few other options to appear: was the brand Option1 or Option2? Also did you use it before(option1) or after(option2) smth.
View 2 Replies
View Related
Feb 21, 2014
I am trying to make a database that will keep track of invoice backlogs for different locations within a company.
Each day a report file is imported into an Access database (2010), and a query will display all locations with the backlog count of invoices for each of them. Something like:
Code:
Location Backlog
00001 84
00002 53
00003 26
Now I would like to be able to store historical data in a new table on a daily basis so that it will be possible to see how the backlogs have fluctuated over time for a given location, but I am not sure how I could do this. I was thinking I could make a table with one row for every date and one column for every location, but then I am not sure how I would populate this table based on the query that shows the results based on the most recently imported report file.
View 6 Replies
View Related
Jul 24, 2007
I'm wrestling with the issues; in other threads, it became apparent that because I could not know ahead of time what I will need to know about a given entity, I will use a table to enumerate attributes that is applicable for a given entity.
However, the stumper is that what if an attribute should conform to a set list of values? Since they are dynamic, I would have problem predicting what I will need to be able to lookup, and am even don't know whether I will need a one-many lookup or many-many lookup.
I thought that generic lookup table with a table listing "classes" of lookup would allow me to have one big generic lookup table while using "classes" to act like virtual tables so I can then set the query to appropriate "class" to return just right set of values.
But as I thought about it, I ran into some issues which is pulling me toward the crazy idea that I should have freestanding tables, and use a field in tblAttribute to give me the table's name so I'd know which free-standing table it points to, and have the necessary key to lookup the values within that table.
Even though my gut instincts tell me that I shouldn't be going against the conventions of database design (who the frick goes around creating free-standing lookups?!?), I'm simply not sure how I can use a generic lookup table to hold all information.
For example, suppose I was given a list of values that has its own categories. Since the former design allows only for two level (lookup and lookupclass), where am I to insert that extra level?
Furthermore, I found myself needing a set of virtual keys to reference a certain "class" of lookups for report purposes. That means I need an extra field in my lookup table than I originally anticipates. What if I find myself needing one more field that just won't fit the generic lookup table?
So does anyone have suggestions on how we would create a placeholder for a lookup table that will be made just in time?
View 4 Replies
View Related
Jan 7, 2007
Hello.
I am a newbie in Access. I am making a Boostore database. Here is the
relationship in problem:
http://moryasha.narod.ru/acc.gif
I set the Data Type for BookISBN field in my OrderDetails table to a
LookupWizard. However, I dont want it to look up all the ISBN's in the Books
table, but only those whose Publisher is the same as the publisher of the
relative order.
unfortunately, I am very puzzled and haven't been able to do it by myself.
Can anyone please kindly explain me as detailed as possible? Grateful in
advance.
View 1 Replies
View Related
May 20, 2005
Hi, I'll give you a little background info before I ask my question:
I have a table called "Company" with the following feilds:
Company_ID (PK), Ticker (Indexed, Unique), Company Name
I have (or at least want) a single record access form, and now for the problem:
I would like someone to be able to type a ticker into the ticker feild, and have it search for an existing feild with that ticker (it would automatically populate company name with the correct value). Now, if the ticker doesn't exist, I would like it to bring up a new record form so the user can type in that information. I know I've seen something like this before, but its been a while since I've used access, so I'm still kinda brushing up on this stuff. Can anyone help me out here? Thanks!
View 8 Replies
View Related
Aug 3, 2014
I am designing a database for a quarry for maintenance of their machines. I have a table that has a full list of all the parts on the machines, and a multiple value lookup field that says what machines said parts are used on. I am trying to have these values translate to another table, so I can then insert a field to say how many of each part are used on the respective machine, to display on an information form for each machine.
I would also like this table to update if the relevant information is updated, for example if a new machine is input, then have the table update to reflect what parts are used on it (an After Update function?) I have made a query that gives me the read out I want (attached) but just cannot figure out how to get that into a table so I can add the extra information.
View 1 Replies
View Related
Nov 23, 2012
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.")?
View 5 Replies
View Related
Mar 31, 2015
I want to create a text box within a form that automatically populates a contact number based on a selection from a combo box, also in the same form.
For example, I have a Bidders Table (tblTenders), this form includes information regarding the Tendor like the company name and a main contact within that company and a phone number for that contact.
I've created a separate table for all the contacts called tblContacts. This table holds all the contact information for each contact. I have a simple form called frmTenders that asks the user to input the Customer (which is the company who are bidding) and the Main Contact, which is a combo box to select the main contact from tblContacts. Below that combo box is a text box called 'Contact Number' - I want this box to display the contact number for the main contact automatically when a main contact is selected from the combo box.
The contact number text box isn't storing that information in any tables etc. It's just for viewing purposes when we need to make a call to that specific tender.
View 5 Replies
View Related
Feb 18, 2013
I have an Access Table with about 28,000 Automobile dealerships across the country shown. I've joined a new/small phone contact table to this to keep up with our phone contacts with the dealership and followup efforts. When I search/filter on the dealership table all is fine. However when I search/filter on the phone contact table with a few test entries, I get nothing at all. I supposed that after joining the tables, I'd be able to do a search on the field named follow up date and find/filter today's date or other dates and locate which dealerships to contact when the correct date arrives. But nothing.
View 14 Replies
View Related
Jun 11, 2014
I have a database with various tables, customers, jobs_table, black_book. I am wanting to have a field that looks up a latest date.
Both the [jobs_table] and [black_book] have a date field, there can be multiple entries per customer in both fields. What I want to do is have 2 fields in the [customer] table that looks up the customer name in [jobs_table] and [black_book] and brings back the latest date.
View 1 Replies
View Related
Mar 13, 2006
I have a look-up box in a form that saves the description selected into a field in a query. I then use that query to produce a report, but on the report, the query has stored the ID field so the report doesn't show the description the user selected - just a number. How do I get the field in the query to save the description rather than the ID field so I can show it on a report?
View 5 Replies
View Related
Nov 7, 2004
Hi ... Question #4 (question #3 is beyond me)
Should all the lookup field be in one table or in several? Either way, how do you normalise them?
View 14 Replies
View Related
Feb 20, 2005
I have seen a lot of database applications utilizing lookup tables with only one field. That field being the list of objects that is being used to "lookup" in a drop down or list box. (example: State lookup.. "South Carolina")
On the other hand, I see a log of database apps using a lookup table with at least 2 fields. One field representing an object code, the other being the object description. Typically in this setup, the object description is what is displayed in the drop down or list box of a form for the user's selection; whereas, the object code actually gets inserted into the database main table. (example: State lookup.. code: SC; Desc: "South Carolina")
I'm sure using either of these methods depends on preference; however, which is the most acceptable practice? What advantage does one method have over another. Please let me know if the question is not understood so I can clarify.
View 3 Replies
View Related
Aug 2, 2006
Hey all
I have always in the past created separate lookup tables but i am considering using one big lookup table for all lookups. For example having the states, cities, departments etc all in one table.
Can anyone give me the good and bad of doing it this way.
Thanks Hooks
View 2 Replies
View Related
Dec 11, 2006
Hi,
I was reading an article about look up tables, and it recommends that lookup fields shouldn't be done at table level but rather as combos at form level. Is this recommendation a valid one, and since I have some of my tables set up like this, if i change the field from combo box to text box in the table design, would that get rid of lookup table, or are there any further actions which i need to address?
Thank You very much for your help.
View 2 Replies
View Related
Mar 5, 2012
I have two tables; 1, a Customer Table that a customer supplies an ID and some personal information; 2, an Order Table that opens once the Create Order button is pushed on the Customer Table.
What I need to do is get the Customer ID in the Order Table to look up and populate from the Customer Table. I thought the best way to dothis is to use a LookUp Wizard, but every time I try to do that a window pops up and says that I need to save the table before any relationships can be saved. I save it and try again and get the same response. I tried to test it and it does not work because none of the information I populated in the LookUp Wizard was saved.
View 1 Replies
View Related
Aug 5, 2006
I would like to create a lookup field based on a table. However, I want the data displayed in a particular way. That is, I want 'dummy' fields for headings, and the selectable items are tabbed from these headings.
It is probably best to demonstrate what I mean, so here goes!
Sony
-PSX
-PS2
-PS3
-PSP
Microsoft
-XBox
-XBox 360
In my example above, the bold manufacturer names should be non-selectable. The console names are the selectable values.
A good example is the genre search field at:
http://www.pegi.info/pegi/search.do
However, as mine is for data entry rather than a search, the headings cannot be selectable.
Is this possible?
Any assistance would be greatly appreciated! Thanks in advance.
View 4 Replies
View Related
Aug 22, 2006
I have a database that has been developed by a consultant. Unfortunately the database contains look-up tables. Obviously when I query it returns and references the codes for the lookup field rather than the lookup field value. Unfortunately I am unable to change the database to remove lookup fields/tables. How can I get around this to produce tables with the lookup field value or perhaps reproduce the old tables (without just cut and paste) to then conduct my queries. Am I missing something simple as I am fairly new to Access development.
Regards,
Deesy.
View 2 Replies
View Related
Feb 15, 2007
Hello,
I have two tables in my access database.
A. Lets call one table "Map" and Map table has the following fields:
1) ZipCode_From
2) ZipCode_To
3) Salesperson
ZipCode_From : ZipCode_To : Salesperson
00001 : 10000 : Smith
10001 : 19999 : Jones
20000 : 29999 : Johnson
B. Second table is called "SaleMap" and SalesMap table has the following fields:
1) ZipCode
ZipCode :
00252 :
12568 :
22563 :
C. I would like my end query to look like this:
ZipCode : Salesperson
00252 : Smith
12568 : Jones
22563 : Johnson
Any help would be much appreciated.
View 1 Replies
View Related
May 1, 2012
I have a table which contains information about personnel. There are several fields which I want to have consistent values inputted. For example, somebody might populate the "State" field with: California, CA, Ca, or C.A. To avoid this, I created a seperate lookup table with a list of all states, fully spelled out, and the digraph abreviation associated with each state. The digraph is the primary key for this new table. In all, I have 12 such lookup tables.
Having all these extra tables, while nice for clarity sake, seems excessive. In most cases, the lookup tables really only require one column, though I've always used a minimum of two (one is an acronym set as the primary key, and one is a fully spelled out description). I'm finding the primary key is often not useful to somebody reading the data; the full description is much better.Could I have one catch all lookup table which combines all of the standardized fields which I want to use, and have no primary key for said table?
View 2 Replies
View Related
Nov 21, 2006
We have a stock situation, where the balance on hand is calculated by taking into account receipts and issues/sales from a historic balance.
how do stock systems like this normally deal with stock that can be located in multiple locations.
sounds very hard to me!
Thanks in advance
View 10 Replies
View Related
Sep 9, 2005
I have a table of 'things' and two related tables, type and subtype. Each thing has a type and a subtype. The types table contains simply Index (autonumber) and Type (text). The Subtype table Index (autonumber), type (number) and subtype (text), where index and type form the primary key and type is a foreign key to the types table. Thus each type has its own sub-set of subtypes.
In the design of the Things table I have set the lookup for Type and Subtype to be a dropdown, but of course the subtype drop-down shows all subtypes, not just valid ones. If I select a subtype that is incorrect for the selected type then of course I get an error, but it would be nice to set the query in the subtype lookup's Row Source to just display the valid subtypes. At the moment it just says "SELECT index, type FROM subtype" How do I put in a WHERE clause that references the Type field for the current row: "SELECT index, type FROM subtype WHERE type=<type selected in current row>"?
View 3 Replies
View Related
Dec 8, 2005
Hi ppl,
Anyone who thinks that they may be able to help me, there is a copy of the database at :
http://members.iinet.net.au/~a.beardsley/database/StaffProLearning.mdb
Here is my problem, as you will see. In the from called frmStaff, I have created 2 lookup tables that work fine in the main form. In the subform under activites though, I have attempted to create a lookup form for Name and Location of activities. It does not appear to be storing the information from previous entries though. My aim is so that when someone goes to enter a new activity name or location, the data will be saved and be in the list for the lookup next time with a different entry, however, it does not appear to be storing the information in the appropriate table. Any ideas on how to fix this?
Regards,
sugar05
View 3 Replies
View Related
Jul 12, 2007
Hi, all! I'm once again needing your sage, wise advice.
I was reading on MVPS.org, and found a page that denounces the use of lookup fields in tables, because of the way it skews relationships, weighing down the database.
http://www.mvps.org/access/tencommandments.htm
(#2)
Do you all agree with this?
What do you recommend I do, in lieu of lookup fields, when I need to maintain integrity of the data.
Thanks SO much. You guys are my heroes!
KellyJo
View 2 Replies
View Related
