Lookup Wizard Or Separate Table

Jun 8, 2005

I've been doing a lot of research and reading on databases and normalization and things of that sort because I need to create database from scratch. I've been maintaining a db that someone else made, but it turns out I will have to recreat the whole thing because it's not useful anymore and the users need a more user friendly db. I have a couple of days some are:

tblTO
------
TONumber
Contract
Product Directorate <- combo box 20 items
Document Type <- combo box 15 items
TOManager <- combo box 10 items

tblTOItem
----------
Product <- combo box 13 items
JobNo
BasicDate
ChangeDate
ChangeLevel
TotalBillablePgs
TotalTextPgs
TotalArtPgs
Standard <- combo box 22 items
TemplateVersion <- combo box 20 items
SourceFormat <- combo box 15 items
SourceLocation <- combo box 10 items

The ones I have the arrows for will be a combo box on my form.
My question is should I make each of these field a table of its own?
For instance make the Product field a table and list all the 13 products there?
Or keep the Product field in the tblTOItems and in design view of tblTOItems use the Lookup Wizard and type in my values there to make a lookup column.

I have seen it done both ways, and I was just wondering which way is more efficient.

View Replies


ADVERTISEMENT

Lookup Or Separate Table?

Aug 19, 2004

While trying to "normalize" a developing database, a question has come up. I have two sets of information that will probably never change. Hair color and Eye color. Should these be put into a separate table or can I include those two fields in the table containing information about the person and then use the lookup wizard function in the table design and "type in the values I want"? I am using '97. Thanks.

View 1 Replies View Related

Tables :: Lookup Wizard In Table Design

Aug 28, 2013

I've designed a database at work to collate information about locations around the world that are contaminated by conflict and military activities. I'm struggling with the use of the lookup wizard to populate some fields in one table from another table.The database is ultimately meant to be used to identify contaminated sites in various countries and also to be a source of data for an online interactive map. As such, it needs to hold a fair amount of information. I figured that it would be normal that some fields and tables would be connected to one another. So, for example:

Country table is looked up by the conflict table to provide the names of countries participating in a conflict. To do so I used the lookup wizard. Similarly, the Site information table is looked up by the Site contamination event table to provide the names of contaminated sites. The Site contamination event table. Then the Site assessment table looks up the Site contamination event table to provide the name of contaminated sites. The relationship between these three table is intended so that at any given site multiple instances of contamination and their subsequent assessment can be recorded. This is where I started noticing problems. When I tried to input some fields into the Site assessment table, specifically the 'Site name', it would only display the primary and foreign keys in the drop down menu but not the 'Site name'.

- Have I messed up by relying on the lookup wizard in my table design? This seems to be the consensus in this and most forums (I checked another thread in the 'Tables' forum here). That said, I've seen some people making a distinction between using 'Lookup tables' and 'Lookup fields within a table', but given my relative newbyness I'm struggling to see the difference!

- If the answer is yes then what approach should I take to achieve the same aim (having multiple tables that feed information to one another). Initial research seems to suggest putting lookup/combo boxes in my forms. I'm dabbling with that at the minute but so far the results haven't been as desired.

I've attached a zip file with a screenshot of my relationships diagram to give an extra idea. Note that the relationship between the 'Site assessment' table and 'Site contamination event' table isn't showing up as I removed while trying to troubleshoot, but it is supposed to be one-to-many from 'Site contamination event' to 'Site assessment'.

View 14 Replies View Related

Forms :: Using A Lookup Wizard To Select Multiple Entries From A Table

Jul 14, 2015

I am trying to create a lookup wizard to record the number of people who attend a regular event. I have a table recording the names, surnames and DOB of registered members and a number of other tables for regular events we hold. Previously people have been manually adding the names of attendees and of course there have been inconsistencies in the data. I want them to look up the names from the table of registered members. I have been using the lookup wizard, but it is only displaying the first name in a string. I need it to display id, first name, surname and DOB in a table. I also want to be able to type in the first letter and then pick from the list.

View 5 Replies View Related

Lookup Fields Using Lookup Wizard

Mar 2, 2005

Hello,

I wonder if anybody can help me.

I have a table called ITEM, within ITEM I have three fields ITEM NUMBER (Key Field), Item, Cost,

I have another table called INVOICE ITEMS, Within INVOICE ITEMS I have six Fields, INVOICE NUMBER, ITEM NUMBER, ITEM, UNIT COST, Amount, Total Amount.

I want to use Lookup wizard to complete the fields ITEM NUMBER, ITEM, UNIT COST from the ITEM table.

Is this possible?

Regards
Nathan

View 1 Replies View Related

Lookup Wizard

Mar 10, 2005

I have three fields that I would like to have access the same table using the Lookup Wizard. I am unable to get the query to work.

Example:

The Fruit table has 5 records, let's say: orange, apple, banana, kiwi, strawberry.

The first field -- Fruit1 -- would use the Data Type Lookup Wizard referencing the Fruit table.

The second field -- Fruit2 -- would also use the Data Type Lookup Wizard referencing the Fruit table.

The third field -- Fruit3 -- would also use the Data Type Lookup Wizard referencing the Fruit table.

The bottom line: a person will have a choice of 3 fruits.

Again, the problem is that I cannot get the query to work where I choose =banana for the criteria for all three fields. Banana might be person one's first choice, but it might be person two's second choice, etc. I'm looking for all the bananas no matter which choice it is.

Your assistance is appreciated.

View 1 Replies View Related

Lookup Wizard Won't Look Up!

Sep 26, 2005

I will try and explain this as simply as possible.

I have a lookup table that is purely dates so the field is Clinic_Date, the format is date/time, and the input mask is set to short date.

I have a table called appointments. I have a field called appt_date which I am trying to make a look-up field by using the wizard to look up from the clinic_date table. However I an getting the error message "You have entered an expression that has an invalid reference to the property l." (thought it doesn't look like the letter "l" but more like a long vertical line).

What on earth could be going on?

View 7 Replies View Related

Lookup Wizard Not Working

Nov 3, 2005

I tried to chosee Lookup Wizard as data type in my table so I can have drop-down options from another table. However, I received an error message, "ActiveX component can't create object". What does this mean, please help :confused:

View 1 Replies View Related

How To Undo A Lookup Wizard

Feb 14, 2007

hey,

i have applied a lookup wizard to some fields, just to see what happened, and now i want to undo it... how do i do this pls?

thanks

View 3 Replies View Related

Lookup Wizard Issues

Sep 3, 2007

I'm using the lookup wizard for to allow a user to pick a value from a text field. However, when I use the lookup wizard the field type for the field that displays the selected value is number instead of text. When the selected value is displayed in List Box, instead of the text it displays the ID number for the record the text is picked from - For example, if I choose the name "Alex" from the lookup, the number 1 is displayed, if I choose "Bob" it is 2 and if I don't select a value, it shows 0.

Any idea what exactly is going on?

View 6 Replies View Related

How To Use Lookup Wizard For Dates

Apr 29, 2014

How does one use the lookup wizard for dates?

It says: The lookup wizard doesn't apply to fields of this data type.

I want a combo box for dates.

View 7 Replies View Related

Data Type With Lookup Wizard

Nov 1, 2004

Heres something I dont understand about the lookup wizard:

Ive done a lookup wizard on one of my fields, so that now the user gets a drop down list of options to choose from:

0;1;2;3;4;5;6;7;8;"NTSC";"PAL"

Ive also set "Limit to list" to yes, so that these are the only options the user can enter.

However, it will still let me change and play around with the data type settings under the general tab. I dont understand why it lets me do this. I can set the lookup to the above list, and then specify a date type of type "date", and it will allow me to do it.

Why is this the case? Which datatype should I set it to, if I have a list of values like the one above?

View 1 Replies View Related

Lookup Wizard List Visibility?

Jul 11, 2012

As one is typing in a field with a lookup wizard - dropdown list - is there anyway to see the list without clicking on the down arrow? Perhaps there is a choice somewhere in the program where dropdown menus for the lookup wizard can be made visible?

View 3 Replies View Related

How To Get Around 50 Character Rule Of Lookup Wizard

May 19, 2014

I know this is odd but I need to somehow create a way of making a dropdown list with more than 50 characters. One has 250. There are 3 options to choose from and only one needed to be selected.

I am happy to just type a 1, 2 or 3 in the box IF it can be referenced somehow to one of the 3 longer statements that I need to use but how to do this.

View 8 Replies View Related

Tables :: Creating A Field With Lookup Wizard

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

General :: Lookup Wizard - Two Fields In Drop Down List?

Oct 14, 2014

I've managed to use the lookup wizard to show me two fields in the drop down list, first name and last name, but when i select a record I can only see the first name in the cell, not both.

Is there a way to display them both together?

View 1 Replies View Related

Map Table Wizard??

Feb 21, 2005

Hi

I want to manipulate a few tables and make them into one table, but I don't want all of their fields. I heard that I could do it with "map table wizard" from the top of the menu. Where can I find it? Or, can anyone recommend me what I should do better?

I use Office 2002.

Thanx.
Gaufres

View 2 Replies View Related

Tables :: 2 Table To Generate Separate Matrix Table

Mar 9, 2014

I have 2 tables

- Staff Position(Unique Position Name, Description, Hierarchy)
- Training (ID, Name, Description etc.)

I essentially want a table with Staff Position as the Field, and Training as the Rows. The intersecting entries/matrix will be Yes/No to say whether that staff positions requires that training.

Simple Example

..................... Worker ........ Senior ........ Principal
IT Training ......YES ............. YES .............. YES
Accounts ......... NO ............. YES .............. YES
Management ... NO ............. NO ............... YES

I need the user to be able to add as many training entries and as many staff positions as they want. It doesn't HAVE to be that sort of format...

View 4 Replies View Related

How To Choose Row Heading When Using Create Table Wizard

Sep 23, 2015

I am unsure of how to choose my row heading when I use the create table wizard. It gives me the option to use the first row but my row heading is actually row 17.

View 6 Replies View Related

Separate Table For Dates?

Oct 27, 2005

I am designing a database to enter daily/monthly performance numbers for employees and department totals. However, I don't want to have to enter the month and/or day for every category for each employee entered. In looking through these forums, nobody recommends a separate date table. But it seems time-consuming to have to enter the reporting month for every sales category for every employee. To wit:

TblEmp
EmplID
EmplName
HireDate
TerminationDate

TblCategories
CatID
CatName
CatType

TblDate
MonthYr
DayMonth

TblMonthlyPerformance
AutoID
EmplName
CatName
MonthYr
Amount

TblDailyPerformance
AutoID
EmplName
CatName
DayMonth
Amount


If I don't have a table for dates, then for every category for every employee, I would have to enter the date...right or wrong? Or am I thinking flat.

View 14 Replies View Related

Exluding Records For Separate Table

Apr 29, 2006

I created a report where is lists sales volume by day. I was asked to create a table where data can be excluded from this report. The exclusion table consists of only three fields:

Date Department and a check box

What do I need to do in the query to have it exclude the data listed in this exclusion table. The table only holds data we want excluded, not all dates and departments.

I originally added the table to the source query and made the criteria "False" for the check box but when the query runs it does return any data at all.

Please help!

View 1 Replies View Related

Excluding Records From Separate Table

Apr 29, 2006

I created a report where is lists sales volume by day. I was asked to create a table where data can be excluded from this report. The exclusion table consists of only three fields:

Date Department and a check box

What do I need to do in the query to have it exclude the data listed in this exclusion table. The table only holds data we want excluded, not all dates and departments.

I originally added the table to the source query and made the criteria "False" for the check box but when the query runs it does return any data at all.

Please help!

View 1 Replies View Related

Linking 2 Fields From 2 Separate Table.

May 11, 2005

Hi.. Junior access user here. Need help fast!

I created 2 tables. [General Info] & [Details]

[General Info] has 2 fields. Name(is a primary Key as well) & Organisation

[Details] has 4 fields. Primary Key, Name, No of products & Type

[Details] is a sub form for [General Info]

It is a one to many relationship. With Name from [General Info] linking to Name from [Details].

I can update via the table directly and [Details] links the name fields automatically. I created a form but the form just cannot register the field automatically and requires the user to type in the Name from [General Info].

I have attached the DB file. Pls help... your help is greatly appreciated!

View 3 Replies View Related

General :: How To Hide Parent Table In DLOOKUP Wizard In 2010

Dec 1, 2012

here I have capture what is error actually when i make a DLOOKUP wizard.the box of wizard. there i want to make a relationship with table2. but we could see that table1 still show. yes right, it's reality making error and show message ""you can't modify the structure of table, because it is already in use by another person or process"and warning message ""Microsoft access couldn't launch the lookup wizard, or this wizard has been disabled...blabla...blaa"

on my laptop, ms access 2010 nothing happen the mattter and when i make DLOOKUP, parent table have been not seen.and make DLOOKUP Wizard is successfull.

why in my PC, when i make DLOOKUP wizard, parent table still show?what is this bug for MS access 2010?

View 4 Replies View Related

Forms :: Form Wizard Not Displaying Table Field Names

Apr 25, 2013

Access 2007 Pro.Attempting to use a query and a table to populate a 1:N form/subform using the Form Wizard. The wizard will not display the field names of the query.

View 2 Replies View Related

Create A New Table Or Use Comma Separate Fields

Dec 27, 2007

All,

I have a quick question that could lead into a longer one.

First off, I am trying to associate tests with a course a student is enrolled in. Would it be better to add a column in the courses table and have a comma separated list of all tests associated with each course or would it be better to create a separate table that has a column for the course and another column for the associated test with that course?

If comma separated fields would be better, how would I handle that in forms? How does one parse that field?

Thanks!

View 6 Replies View Related







Copyrights 2005-15 www.BigResource.com, All rights reserved