Lookup Table Value Must Be Populated
Feb 19, 2008
I have a table called tbl_workorders. One of the fields on this table is optional but because the lookup table is on the one side of a 1-to-many relationship using an auto-number key it is requiring that this field be populated to create a record on the workorder table. This is a problem because the field is optional.
tbl_workorders
WorkOrrderID(PK)
Field1
Field2
Field3
Field4
JobSubtypeID (FK-many) <---- Lookup field
tbl_Subtypes
JobSubtypeID (PK-one)
SubtypeDesc
Should I set the SubtypeDesc field to a default value of "none"? I know this is storing unnecessary data, but is there another option?
View Replies
Aug 6, 2013
I am trying to use a combo box to control which table a data entry form will write data to. I want to create a form that has a combo box to select from a top level table that I will call "Stores". Once a "Store" is selected from the drop down, the next field on the form will be a data entry field. The data entered in that field will be written to the table selected by the preceding combo box.
So, basically I would have say Wal-Mart, Macy's, Sears, K-Mart and etc, listed in my "Stores" table. Once I select one of the stores from the drop down, I would then enter a "department" name in the data entry field and based on which store I selected from the previous combo box, the data would be written to that stores department table (which each store will have its own department table), e.g., WalMartDepts, MacyDepts, SearsDepts, etc...
View 1 Replies
View Related
Feb 4, 2015
I am having a problem with calculated fields that are populated on the main form from the subform not saving to the table the main form is linked to.
I created a Purchase Order table that has information on what emplyee created the record, reason for order, customer information if its ordered for a customer, creation date, eta date and notes while the Purchase Order details table is for information on the supplier, product item number, product name, quantity and cost. they are linked by the Purchase Order number which is the primary key in the Purchase order table. it all looked good so i made the form with the Purchase Order Detail as the subform, i got the form all working great and how i wanted it but then i decided i wanted the supplier name and the subtotal of the cost in the main form populated or calculated by the values in the subform.
I copied the fields i wanted populated in the main form to the subform footer and added the calculation for the subtotal then added the formula to the source code on those fields in the main form so the values would populate. it all worked great on the form and the values populated as they were supposed to so i saved the record and went to look at the tables and i found that while the values in the calculated fields that I populated from the subform showed up in the form they didnt save to the table while the non calculated fields saved fine. i did some experimenting and found that if i delete the code and put the data source back to the table in the form and just type something in the text box it would save to the table but not if i had the code in and let it populate.
an example of one of the codes i used to populate the data in the main form is:
=[frmNewPurchaseOrderDetails subform2].[Form]![txtsubfrmSupplierID]
did i mess up in the code or did i do something to the relationship between the form and the table?
View 3 Replies
View Related
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
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 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 2 Replies
View Related
Jun 13, 2006
BACKGROUND
I am desperate to solve this problem but unfortunately I have not been able to figure it out. Below I will outline a design of a database and the desired results.
I know what I want to do but I don’t know how to do it (or whether it is impossible!)
DATABASE DESIGN
The design below is a simplified version of the real thing but it contains the essential information needed to understand my database.
Staff Data
Contains daily data for several members of staff
Staff ID
Staff Name
Date
Data Field 1
Example records:
600-001, Bob Smith, 01/03/2006, 50
600-001, Bob Smith, 02/03/2006, 50
600-001, Bob Smith, 03/03/2006, 50
600-001, Bob Smith, 04/03/2006, 50
600-001, Bob Smith, 05/03/2006, 50
600-002, Jayne Cole, 01/03/2006, 60
600-002, Jayne Cole, 02/03/2006, 60
600-002, Jayne Cole, 03/03/2006, 60
600-002, Jayne Cole, 04/03/2006, 60
600-002, Jayne Cole, 05/03/2006, 60
600-003, Alex Winter, 01/03/2006, 20
600-003, Alex Winter, 02/03/2006, 20
600-003, Alex Winter, 03/03/2006, 20
600-003, Alex Winter, 04/03/2006, 20
600-003, Alex Winter, 05/03/2006, 20
Team Lookup
Shows what team each staff member belongs to and what date this is effective.
Staff ID
Team
Start Date
Example records:
600-001, Sales, 01/01/06
600-002, Sales, 01/01/06
600-003, Accounts, 01/01/06
600-002, Accounts, 04/03/06
Please note:
The first three records show that at the start of the year Bob (600-001) and Jayne (600-002) worked for Sales and that Alex (600-003) worked for Accounts.
The last record shows that from 04/03/06 Jayne switched teams to Accounts
Query Assign Team
Assigns the correct team to Staff ID for each date
Staff ID
Staff Name
Team
Date
Data Field 1
Desired Results:
600-001, Bob Smith, Sales, 01/03/2006, 50
600-001, Bob Smith, Sales, 02/03/2006, 50
600-001, Bob Smith, Sales, 03/03/2006, 50
600-001, Bob Smith, Sales, 04/03/2006, 50
600-001, Bob Smith, Sales, 05/03/2006, 50
600-002, Jayne Cole, Sales, 01/03/2006, 60
600-002, Jayne Cole, Sales, 02/03/2006, 60
600-002, Jayne Cole, Sales, 03/03/2006, 60
600-002, Jayne Cole, Accounts, 04/03/2006, 60
600-002, Jayne Cole, Accounts, 05/03/2006, 60
600-003, Alex Winter, Accounts, 01/03/2006, 20
600-003, Alex Winter, Accounts, 02/03/2006, 20
600-003, Alex Winter, Accounts, 03/03/2006, 20
600-003, Alex Winter, Accounts, 04/03/2006, 20
600-003, Alex Winter, Accounts, 05/03/2006, 20
Query Group By Team
Summarises data by team/date
Team – Group By
Date – Group By
Data Field 1 - Sum
Desired Results:
Sales, 01/03/06, 110
Sales, 02/03/06, 110
Sales, 03/03/06, 110
Sales, 04/03/06, 50
Sales, 05/03/06, 50
Accounts, 01/03/06, 20
Accounts, 02/03/06, 20
Accounts, 03/03/06, 20
Accounts, 04/03/06, 80
Accounts, 05/03/06, 80
PROBLEM: WHAT I AM TRYING TO DO
I don’t know how to get the query “Query Assign Team” to work!!
I would like to lookup up the ‘Staff ID’ and ‘Date’ in “Team Lookup” and return the appropriate value for ‘Team’
If the only two records in Tbl Staff data were:
600-002, Jayne Cole, 03/03/2006, 60
600-002, Jayne Cole, 04/03/2006, 60
I want the query to return:
600-002, Jayne Cole, Sales, 03/03/2006, 60
600-002, Jayne Cole, Accounts, 04/03/2006, 60
Can anybody help me?
Should I be using DLOOKUP? If so, how?
Is VBA the only way around my problem? If so, can you tell me what it is?
Am I attempting the impossible?
View 9 Replies
View Related
