If A AND B Then C Lookup Table Needed

Oct 30, 2006

I am looking to set up tables in such a way that will allow for the concept:
If Variable 1=A and Variable 2=B then Output of 3=C

For Example:
If the House is "Blue"
And the Man drinks "Beer"
Then his pet is a "Dog"

If the House is "Green"
And the Man drinks "Vodka"
Then his pet is a "Bird"

NOTE:
There is no equational relationship between the first two variables to get the 3rd output. It is simply a lookup table. In excel you would have the color house down the left, the drink across the top and in the cells would be the type of pet. But I am struggling with how this can be made to function within Access since I will want the user to obviously be able to pick the first two variables and have the "answer" spit out.

View Replies

One Lookup Table With Two Needed Values - How To Use Both

Apr 24, 2015

The Lookup table has two fields containing values that are needed: Description and Amount ($). In the table that uses the Lookup, I'd like to have both values shown, but have a dropdown just once. In other words, when the user selects a description (the dropdown shows both the description and amount), can the amount be inserted into an Amount field as the description is done currently.

View 4 Replies View Related

Modules & VBA :: Restrict Value In Lookup Field To Only Include Values From Table B Not In Table A?

Feb 2, 2014

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?

View 5 Replies View Related

Help Needed :If Table Exists, Then Delete Table

Jun 6, 2006

I need urgent help, I am required at my job to come up with a command that will check to see if a table exists, and if so, delete the table. The whole process goes like this:

There's a form, and a listbox. 2 buttons...1 is import a file and 2nd is generate report. In the listbox theres 3 excel files. You select one excel file then click on Import, followed by clicking on Generate Report button.

The whole technical process is this:
1. A csv file is imported into a temp table
2. A temp table is created and named "_ImportedSKUS".
3. An append query is exectued to add the data from the "_ImportedSKUS" temp table to the final table, "Imported SKUs".
4. Then the temp table, "_ImportedSKUs" is closed and deleted.

I have a delete command at the end of the sub, which goes like this:

DoCmd.DeleteObject acTable, "_ImportedSKUS".

But I'm also need to generate an If statement before the loop that will check to see if the "_ImportedSKUs" table exists, if so, delete it (using same code listed above) and end if.

Can anyone help me out here???

View 4 Replies View Related

Tables :: Two Fields In A Table / Link To Same Lookup Table

Mar 14, 2013

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.

View 5 Replies View Related

Table Lookup - Row Source From Another Table - Only Showing Numbers

Feb 11, 2012

When I have a 3rd table looking at the row source of the 2nd table, which is looking at the row source of the 1st table. I only get numbers.

Everything I have read so far points to using a query as a solution. However, the query I made is not updating the information from the 2nd table.

I have attached a few examples.

Attachment 6247

this below shows. I changed "Bakersfield-test1" in the original table. However, the query I made does not update, still showing "Bakersfield-test". When you click on the drop down, it shows the updated "Bakersfield-test1", but it does not repopulate my column with the updated info from the table.

Attachment 6248

View 4 Replies View Related

Need 2 Fields From Table 1 / Using Lookup Info From Table 2 And Put Into Table 3

Jun 28, 2013

I need to get x and y coordinates for each device, but the data has to get looked up from 2 other tables.

I have a table (called InstReclosers) that has device names. Each device is on a Section. I can go to another table (called InstSections) and look up what Node that particular section is tied to. Then I need to go to another table (called Nodes) to get the X and Y location for that particular node.

How I can go about getting this X and Y data into the InstReclosers table?

...in summary, InstReclosers has device name and section name. InstSections has section name and node name. Nodes has node name and XY coords. Need XY coords for each device in InstReclosers.

View 3 Replies View Related

Duplicates In A Table-Help Needed

May 19, 2005

Hi Everyone, I need help in selection of primary key and designing of a tables. I am having a table called Current trailers with Trailer number as a primary key. I have similar table to current trailers called history table in which the trailers that left the yard are stored. As a trailer can enter and exit the yard couple of times in a week, its not letting me to have duplicates in the history table.So can anyone help in selection of primary key for the history table. Or can anyone tell me how I can have duplicates in a table.

Thanks in Advance,
Naveen

View 2 Replies View Related

Junction Table Needed??

Apr 24, 2006

I�m having trouble defining Relationships I�m thinking I need a �Junction� Table and I have tried looking at the Orders.mdb but it hasn�t helped (I�m sure I�m just missing something) I just don�t see how it works. If at all possible please don�t just give the info try to help me understand so I can get the answer myself.
Here is what I have:
Far table:
FarNumID (PK) > autonumber
FarNumber > Text �224-10C�
FarTitle> Text

FarParagraph table:
FarParaID (PK) > autonumber
FarNumID > Number
FarParaTitle > Text
FarParaText > Text

AC table:
ACNumID (PK) > autonumber
ACNumber > Text
ACTitle> Text

ACParagraph table:
ACParaID (PK) > autonumber
ACNumID > Number
ACParaTitle > Text
ACParaText > Text

1. Each FarNumber can have only 1 FarTitle 1:1
Each FarNumber can have many FarParaTitles 1:Many
Each FarNumber can have many FarParaText 1:Many
2. Each FarTitle can have many FarParaTitle 1:Many
Each FarTitle can have many FarParaText 1:Many
3. Each FarParaTitle and have only 1 FarparaText 1:1

Thanks so much.

View 4 Replies View Related

Help Needed With Table Formation

Mar 14, 2007

Hi

I will give you an overview of what I have.
I have a database that records sites in Blackburn and each site has a unique ID (RTP_ID). Each site has 10 objectives with 25 Measures in which the sites are scored against.
Objective 1 has 2 measures
Objective 2 has 8 measures
Objective 3 has 1 measure
Objective 4 has 1 measure
Objective 5 has 1 measure
Objective 6 has 3 measures
Objective 7 has 2 measures
Objective 8 has 1 measure
Objective 9 has 2 measures
Objective 10 has 3 measures

Each site has the same objectives and the same measures, but the scores for each are individual.

Table: SiteDetails
RTP_ID, SiteName, Postcode

Table: Objectives
OBJ_ID, ObjName, ObjScore, RTP_ID

Table: Measures
OBJ_ID, MSR_ID, MsrName, MsrWeight, MsrScore

I don't know how to create the relationship between them so that each site can have 10 objectives and 25 measures (with scores) individually. At the moment, I have them connecting as SiteDetails.RTP_ID > Objectives.RTP_ID and Objectives.OBJ_ID > Measures.OBJ_ID but it doesn't work, it thinks that the scores apply to every site.

Please help me, I know that this may not make sense without seeing the database, but unfortunately it is too large to attach.

View 4 Replies View Related

Log Table Solution Needed :(

Nov 4, 2006

I have almost finished my current database but I was asked to create a log table/log file that would list changes made to every record. Now my current database don't allow duplicate records, so any advice pointing me into the right direction will be helpful. I have ran through the search area and found nothing that I can use. Can any one help me out in this specific problem. I picked up a few books and none of them give examples of such things. Thanking you all in advance...

View 14 Replies View Related

Linked Table Relationship Help Needed

Nov 30, 2005

I have a database that has a linked table within it. I need to enforce referential integrity on this relationship but it won't allow me too.

Does anyone know if this can be done?

If so, could you possibly point me in the right direction

Thanks

View 4 Replies View Related

Opinion Needed On Table Structure

Nov 16, 2004

Hi everyone,

Could someone please share their opinion with me on the following:

I have a database with a Table which stores People's details, e.g. ID, Name, Surname, ...., etc (e.g. tblPeople) .

Now I have people filling out a questionnaire, of which the results I want to save in an Access table. Each person will fill out this questionnaire only once. The number of fields I will need to accomodate the answers to questions asked, is around 120. For example, an answer to Question1 will appear in a field called Q1.
I know that the limit for fields in a table are 255, and my total fields in tblPeople so far are about 20.

Should I keep Q1 and all other fields in the same table as tblPeople, or create another table to hold all answers and create a one-to-one relationship between tblPeople and tblQuestions&Answers ?

Can someone give me a tip, in particular if this will cause performance problems?

I appreciate your effort for reading this.

Kind Regards,

Jean

View 2 Replies View Related

Table Design Analysis Needed

Dec 22, 2004

I am designing a database for my company that keeps track of new classes and new courses created. I have a form for each with the create a class form taking the course title from the courses table/created course from the create a course form.

Here's my dilemma. I want to use cascading combo boxes for class location, facility and room but I think my table structures and relationships may be incorrect.

Here is a list of my tables and their fields:

tblClass
ClassID (PK)
CourseID (FK)
InstructorID (FK)
StartDate
EndDate
StartTime
EndTime
ClassMin
ClassMax
Materials (yes/no)
MatNotes
EmpID (this is the ID of the employee who requested a class creation)
LocID
FacID
RoomID
LogID (Logistical Coordinator ID, if applicable)

Course
CourseID (PK)
CourseName
more...

Employee
EmpID (PK)
FName
LName
more...

Instructor
InstructorID (PK)
FName
LName
more...

Logistical
LogID (PK)
FName
LName
more...

Locations
LocID (PK)
Location

Facilities
FacID (PK)
Facility
LocID (FK)

Rooms
RoomID (PK)
Room
FacID (FK)
LocID (FK)

When I run a query with class, locations, facilities, and rooms no information shows up. If I run the query with the relationships between facility/room, facility/location and room/location removed (leaving the only relationships between class and these three) it works. Why is that? Shouldn't there be a 1-M relationship between Location, Facility and Room? Or, should there be a M-M relationship and I should create tables with each PK?

I apologize for the long post but this has become quite frustrating (despite the simple logistics).

Thanks!

View 1 Replies View Related

Table Structure Assistance Needed.

Dec 29, 2004

I manage academic papers whom are written by multiple authors and reviewed by multiple reviewers. Currently, all the data is stored in one big table and I'm not able to get any statistics or real tracking out of it.

I have set up a test DB with 3 tables so far:

Main
Table_ID -auto sequential key
CORP_ID -internal paper id
STATUS -open, overdue, accepted, rejected
REC_DATE -received date
MOD_DATE -record last modified date
TITLE
AUTH1_ID -linked to Author.ID
AUTH2_ID -linked to Author.ID
REVR1_ID -linked to Reviewer.ID
REVR2_ID -linked to Reviewer.ID

Author
ID -key field
NAME
DESIG -designation
EMAIL
PHONE

Reviewer
ID -key field
NAME
DESIG
EMAIL
PHONE
RATING

My goals are these:

Create a module that manages the authors. They are usually the same group of people. Each author should only appear once.

Create a module that manages the reviewers. Also, usually the same people (but different than authors). Each reviewer should only appear once.

Create a main form that allows the end user to add a record (paper), assign author(s), assign reviewer(s).

Create Query/Report that would then be able to:
- track authors and their papers
- track reviewers and their reviews
- as well as track papers and their authors/reviewers

When I create subforms, they appear to be 'backwards' meaning that the Author form tends to be the main form and the Main form tends to be the subform. I think this is b/c the Author.ID field is the key field, whereas the Main.TABLE_ID is the key field, but not relevant for other than keeping things straight, nor is it related.

Can someone point me in the right direction?

Alternatively, has someone created a database like this already that I can buy? :)

Thanks!

View 1 Replies View Related

Tables And Table Structure Needed For New DB

Oct 31, 2005

Firstly, I want to admit that my knowledge about data base construction is fairly basic.
I am trying to build a data base for humanitarian projects that do not at all resemple of the usual examples including customers, invoices, suppliers and what have you. My problem in constructing the DB is that most of the projects will address more than one subject and also include more than one target group and even operate in more than one country.
In my first attempt I filled the relevant fields (subjects, target groups and country) with more than one value using a form with multi select lists. The problem was that it was very difficult to query these multi-value fields and kind people in this forum strongly advised my to reconstruct the DB avoiding such fields.
What I need is to be able to view/print various selections based on precise criteria that include a region (or country), a subject and a target group in all kind of combinations.
The problem is to decide on what tables to establish and their interrelation. I guess that when entering a project record (using a form) I will have to store somewhere what subjects (could be more than five) and target groups the project include. It is, of course, not a problem to establish special tables or value lists for the subjects and target groups and countries, but where/how do I store the basic project information for a project and the multi-values that are related to the individual project?
I hope that this is not too confusing and I would appreciate any suggestion for a simple table structure and the interrelation between the tables.
Many thanks in advance.
Niels

View 1 Replies View Related

Clients Table Help Needed, Filtering

Jan 7, 2006

Hi, based on previous advice I had merged my customers and owners into one table and selected whether a client was a customer, owner or both via a lookup table.

This is working fine, however I must assign a rep to each owner, I'm not quite sure how to do this, I need to be able to assign a rep to an owner in the clients table, I must also make sure than a rep can not be assigned to someone who is just a customer obviously.

I have attached the database (http://jonroberts.redirectme.net/database.zip), hopefully that will make things more clear.

Thanks in advance.

View 2 Replies View Related

Linking Table To Forms (Help Needed Please)

Mar 28, 2006

Hi

I am designing a database to store volunteers and what particular function they are volunteering for. I have about 17 areas of volunteering tasks (i.e. Fundraising, Teacher) and at least 3 subcategories for those tasks (i.e. candy fundraisers, nursery school teacher). I want a form that you can enter the volunteer's info and then choose as many areas of interest and as many subcategories. I guess this would be done with checkboxes? Right now I am putting each volunteer task in seperate table (ie. Fundraising table, Teaching Table) and then trying to use combo boxes. But I want the person to be able to choose one or many activities.

Can anyone help me design this or give me a suggestion so that I can query or create a report to list the volunteer and what activities they want to volunteer for. I really appreciate any help I can get at this point. My email is ebbr820@hotmail.com or I will check back on this forum.

Thank You!

View 1 Replies View Related

Urgent Help Needed With Table Create Query.

Aug 6, 2007

Hi all, i need some help constructing a query that will create a table for me that does not include the Type value of 'promotion' if the same ID value exists elsewhere within the query.For instance, in the example below, i would want to delete the first entry containing 'Promotion' because ID2001 exists in two other places. However, if only one instance of ID2001 'Promotion' existed in my query, i would want to keep that entry. I would prefer to write all my data to a new table called "Test", This query has to sort out about 1000 entries when executed, otherwise i'd do it manually.I'm pulling my hair out with this one, so could someone help me?ID Company Name TypeID2001 Disc plc John PromotionID2001 Disc plc Paul AgentID2001 Disc plc George Agent

View 3 Replies View Related

Help Needed In Creating A Table Inside Db UsingVBA

Apr 27, 2005

Hi experts. i wrote this code that supposed to create table inside the access db but when i run it i
get this error:

compile error:
user -defined type not defined for Dim db as Database

Itt is a command button that calls CreateDatabase. I be happy if some one help me fix this. Thanks

My code:
Option Compare Database

Public Sub CreateDatabase()
' Routines to create the tables.
CreateTablePlayers
'CreateTablePenalties
' Routine to create relations for the tables.
CreateRefInt
End Sub

Private Sub CreateTablePlayers()
' Local variables
Dim db As Database
Dim tbl As TableDef
Dim fld As Field
Dim idx As Index
Set db = CurrentDb()
Set tbl = db.CreateTableDef("Players")
' Adding fields
Set fld = tbl.CreateField("playerno", dbInteger, 0)
fld.Required = True
tbl.Fields.Append fld
Set fld = tbl.CreateField("name", dbText, 25)
fld.Required = True
tbl.Fields.Append fld

Set fld = tbl.CreateField("initials", dbText, 3)
fld.Required = True
tbl.Fields.Append fld
Set fld = tbl.CreateField("birth_date", dbDate, 0)
fld.Required = False
tbl.Fields.Append fld
Set fld = tbl.CreateField("sex", dbText, 1)
fld.Required = True
tbl.Fields.Append fld
Set fld = tbl.CreateField("joined", dbInteger, 16)
fld.Required = False
tbl.Fields.Append fld
Set fld = tbl.CreateField("leagueno", dbText, 4)
fld.Required = False
tbl.Fields.Append fld

' Adding primary key
Set idx = tbl.CreateIndex("Players_PK")
idx.Primary = True
idx.Unique = True
Set fld = idx.CreateField("playerno")
idx.Fields.Append fld
tbl.Indexes.Append idx

' Add Table to the database
db.TableDefs.Append tbl
End Sub

' Adding Referential Integrity
Private Sub CreateRefInt()
' Local variables
Dim db As Database
Dim fld As Field
Dim rel As Relation
Set db = CurrentDb()
Set rel = db.CreateRelation("PlayersPenaltiesRel", "Players", "Penalties")
rel.Attributes = dbRelationUpdateCascade
Set fld = rel.CreateField("playerno")
fld.ForeignName = "playerno"
rel.Fields.Append fld
db.Relations.Append rel
End Sub

Private Sub Command1_click()
CreateDatabase
End Sub

View 2 Replies View Related

Lookup Table

Jan 6, 2006

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.

View 6 Replies View Related

How To Use A Lookup Table

Apr 24, 2015

My experience has been with SQL Server using Delphi and other languages where code is written for data integrity. A local service club has asked me to setup a relatively simple Access database where those that will use it have very little experience. I am not sure how to setup the following: The ClientTbl has a field, Ad. There is a table, AdTble with the following fields:

Primary key: AutoIncrementing
Description: text (30)
Amount: integer (dollar)

I would like to have a dropdown that shows the description, and when selected, the link to the Primary Key is inserted in the field, Ad. Can this be done? Will the Amount show?

View 1 Replies View Related

LookUp Tables For Dynamic Set Of Attributes: Set A Pointer Or Use A Generic LookUp?

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

Table Lookup Function

Mar 21, 2005

Hi,

I have a table with the following 3 fields (these are the ones i'm having the issue with but there are others).

ID, Country, City

The ID is an auto number.

The Country Field is a lookup with the following lookup properties

Display Control: Combo Box

Row Source Type: Value List

Row Source: "England";"Spain";"France" etc

:confused: Now the problem...

I also want a lookup in the City field which changes to reflect what was selected in the Country field.

E.G

If in the Country Field Spain is selected then in the City Field I should have the option of selecting Barcelona, Madrid, Valencia etc.

Is this possible in the lookup of a table???

Thanks. :)

View 4 Replies View Related

Field & Lookup Table

Jan 6, 2006

View 2 Replies View Related

Lookup Table - Numbers?

Mar 9, 2006

Hi all,
I Have a question regarding lookup tables. I have a table called tblActivities. In this, I have created a field called ActivityBudget. I would like this to be a lookup field (select entries from a list). In order to do this, I have created a table called tblBudgetLists with all the various budgets. For the AcitvityBudget field in tblActivities, I have set it as Text with the following things:

Display Control: Combo Box
Row Source Type: Table/Query
Row Source: tblBudgetLists
Limit To List: Yes

The function is half working... There are 19 entries in the tblBudgetLists table. When I select a budget from the list in tblActivities.ActivityBudget, the numbers 1 to 19 are displayed (I am assuming because there are 19 entries), rather than the names of the budgets. Any ideas on how to change this?

sugar05

View 1 Replies View Related