Tables :: Lookup Queries For Data Entry - One Table To Another
Oct 8, 2014
I am creating a table for data entry. Three fields in the table are going to be Firstname, Lastname, and Address. I want the choices for data entry to be read from a master table which contains first and last names and town of residence.
That being said I would like the choice of Lastname to be all last names from the master table, and the choices of Firstname to be those from the master table but are limited to having the Lastname as entered in the previous field, finally I want the Address field to be limited to those records which match the lastname and firstname. I have been playing around with lookup queries for each of the fields to no avail.
Okay, for simplicity's sake, I have a data entry form.
It is bound to tableData.
Inputs are:
ProductID Customer Name
ProductID is a combo box.
There is another table called tableProduct. In this table, is ProductID and ProductName.
For convenience sake, when a user chooses a ProductID from the combo box, I want it to lookup that ID from tableProduct. However, how can I do this when everything is already bound to tableData?
Okay, for simplicity's sake, I have a data entry form.
It is bound to tableData.
Inputs are:
ProductID Customer Name
ProductID is a combo box on the form.
There is another table called tableProduct. In this table, is ProductID and ProductName.
For convenience sake, when a user chooses a ProductID from the combo box, I want a separate textbox to lookup that ID from tableProduct and display the ProductName.
A field in a table can be populated by a lookup up but it has to be done manually or with a form.A "new" table can be created with a query that matches the data.
Is it possible to skip these steps and create a field that automatically populates with the data from another table based on other common data?I can do this in Excel but not sure it can be done in Access.
We have a lookup table that has a list of CLIN numbers and their costs. The contract that governs those CLIN numbers and costs will be changing to entirely new numbers. Unfortunately, I still need to have the old and new CLIN numbers linked to the other tables.
Will I need to merge all the CLIN numbers into one lookup table, or can I do it from two lookup tables?
I have a field in a table that keeps rounding the data entry off. It has identical properties to the field next to it but one rounds and the other does not. I tried deleting the field and creating a new one.
I have a form that looks up office names and will automatically populate a field called office number based upon their selection in the combo box. I have a submit button on click event set up to run a query.
Now, here's where I am running into issues: In this query, I need to pull selected columns of information based upon a multivalue lookup field. This multivalue lookup field is joined with the table that the values populate from.
The form will only have one value stored in the txt box field, and I need to be able to search for all records containing that one value.
This is what I have for code:
SELECT FilePlan.FPName, FilePlan.Description, FilePlan.[File Code], FilePlan.GRS, FilePlan.Schedule FROM Offices INNER JOIN FilePlan ON Offices.[Office Number] = FilePlan.OfficeNumb.Value WHERE (((FilePlan.OfficeNumb.Value) Like "*" & [Forms]![RetCutOff]![txtOffNumb] & "*"));
Do I need to string multiple queries together to make this work, or is it just not possible?
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.")?
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.
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.
I am new to Access in a way, and developed a small tool as a warehouse management system. the system is working great but I need to add a rule or condition to this tool.
I have Tables having warehouses locations (LOCATION1,LOCATION2, LOCATION3, etc...)
Some items (products) will be entered into a transaction table where with queries and reports, can see each item in which location it is or in a particular location what are the products stored in.
however, if there is no limitation or condition in a way or another into the access application to limit users from entering an item OUT in the transaction from a location which doesn't have this product, I need access to tell me a message or stop me from entering that transaction.
To explain more, if I have ProductA in LOCATION1, and one of the data entry guys entered a transaction in LOCATION2 for the same ProductA, access will not allow him since there is no quantity of that particular product in that LOCATION2.
How to do it? I checked in Data Validation and Validation rules, but its either hard for me to understand or am not getting how to do it?
What is the best way to do this without losing data? I have several FK that I made the mistake of setting up as lookup fields. I now want to correct this without losing data if possible.
DataType currently says "Number" under each of these. So I can't just change them to number. Is there another way to do this?
Hello, I understand that this is against any database rules but I need to add data to my database concurrently in two tables.
I will use one form (frmMaster) and write my data to my main table (table1). At the same time, I need some of this data to be stored into another table (table2).
I do not want to use an append query.
Is this possible via code withough having to close the active form? Can anyone help with with some code?
Can I use subforms to create ONE form that will do data entry to more than 3 tables at once in a relational db? How can I do this? Do I have to use Javascript, etc?
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.
I've got a working database with pretty good structure etc. How I would go about creating 3 prepopulated records on a many side of a relationship on entering a new record in the one side of the relationship.
I have been trying to figure this one out for a while and I can't seem to find anything helpful. I'm going to present a simplified version of my problem to help keep it simple.
I have an "Activities" table that contains fields: ActivityID ActivityName
I have a table of 7 Risk areas which contains fields: RiskID RiskName
What I need to do is to create a form that will allow the user to select an activity and then, for each of the 7 risk areas, input a risk measurement.
This involves the creation of a 3rd table to collect the user input which would contain fields:
MeasurementID RiskMeasurement ActivityID RiskID
I can do a dropdown of the Activities and have a subform that links to it but the problem I'm having is trying to get that subform to list the 7 Risk Areas AND collect the RiskMeasurement information from the user. How can I have a listing of these 7 areas from one table that are tied to inputs that go into another table? This seems so basic and simple yet I have tried numerous things and all have failed.
I am having an issue with a database I'm creating. It is a root cause analysis database. One of the fields is Target_Date. I need to set up queries that alert us if the target date is coming up, (in a week or so), or if it is past due. The issue is that some target dates are simply, "Next Run", which is to say that the corrective action must happen on the next run of the particular item which, at that point, has not been scheduled.
In order to do the above query, the dates must be formatted as Date/Time as <date() will bring up all dates regardless of whether they are in fact less than today if they are in text format. However, this restricts the ability to put in the target_date of "Next Run". Next Run will be required for another query in which we will specify the products coming up and it will let us know which "Next Run" items associated with those products we will do.
Essentially, I have taken the long route to ask, is it possible to create a field format that measures dates as dates and text as text?
I am designing a little database to hold physical activity log info for 2 school districts, required by a grant. Four-five times each year every kid (2000) will complete a log form for 3 days that lists separately for 34 intervals (1/2 hour each, from 7am - midnight) in which physical activity s/he engaged (59 options) and the level of effort of each (4 options, from light to very hard).
The log sheets look like a datasheet with 5 blank columns: #1 enter the activity code (1-59) and #2-5 mark an "X" in the column describing level of effort (light to very hard). There are 34 rows, each labeled from 7am thru midnight in intervals of 30 minutes. The kids **have to** enter an activity code and a level of effort on every row. They do this for three days in a row, 4-5 times during the year. (believe it or not ... federal grant requirement)
I am maintaining survey responses (also 4-5 times a year) in this same database. I have a basic demographic table (Id, name, grade level, school). I am also maintaining eating habits (4-5 times per year) from food logs, like the activity logs, but these are for 5-7 days at a time. Finally, I will be importing number of steps walked for 4-7 days (also 4-5 times each year) from pedometer downloads, one per kid.
I have all tables and data entry forms except for this activity log set up, all data entered for the baseline period. What I would like to do with the activity log is something like a main form showing kid name, ID. I think I want to enter each row of the log (34 rows) into something like a datasheet, each row with a date field (I will use CTRL-' to copy down for one page of the paper log), and an activity code field (1-59 as codes) and a level of effort field (1-4, light to very hard).
What I think I want is one table for activity info, each record with these fields:
Kid_ID Date_Logged Time_Interval (these are the 34 rows, 7am - midnight, in 1/2 hour intervals) Activity_Code (options 1-59) Level_of_Effort (options 1-4)
I can see this in a datasheet format, but entering 2000 kids * 3 pages * 4-5 times a year [* 3 years] into Excel does not seem to be an efficient solution.
If I could open a form on the screen with a combo box to pick a kid and then pick a date, then fill in 34 rows of activity codes (1-59 as options) with a level of effort for each (1-4 as options), that would be great.
My obstacle right now is how to display 34 empty rows already loaded with an index related to the time interval, so that I don't have to enter that for each row -
Hence my subject descriptor, "data entry in matrix format". I am using Access 2007, Windows XP Pro (all service packs etc. up to date).
My question is this: I have a table where I'm entering employees' hours worked. Basically, it's something like this:
ID WorkerNumberDateworkedTimeStartTimeEnded 121/2/201310:00:00 AM3:00:00 PM 221/3/20132:00:00 AM11:00:00 AM 321/4/201312:15:00 AM11:30:00 AM 421/5/201310:25:00 PM11:00:00 AM 531/2/201311:00:00 AM3:30:00 PM 631/3/201312:00:00 PM10:00:00 PM 731/10/20137:00:00 AM4:00:00 PM
I have a query that (easily) determines how many hours an employee has worked on any given day. What I can't figure out at all, is how to write a query that can figure out how much time an employee had off in between shifts.
Thus far I'm able to run a query that separates this main table into individual workers by their id numbers, but can't figure out how to determine time off between shifts - as the last hour worked one day, and the first hour worked the next day are on two different lines (they are two different table entries).
I have several tables which have an indexed, no dup field. When inputting a entry that is not in the referenced table, how can I be taken directly to the input form for that field.
I know experienced DB developers say never to use a lookup in a table for a foreign key and instead to use it on the form level. For this reason, I am going through my tables and removing lookups from the table level now.
But how do y'all feel about lookup value lists? (so the list is typed-in instead of looking up a table value)
If you think they shouldn't be used then what should I do instead? Should I make a table for the handful of values and link with a FK field?
Or is there a better way? I would rather not have to make a million tables for these short, stable value lists.
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'.
