General :: Creating Entries In A Table Based On Multivalue Lookup
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 Replies
ADVERTISEMENT
Apr 14, 2014
I've been doing some reading on multivalue fields and it seems to be exactly what I'm looking for. My question: Is there a way to use the multivalue field without making it a lookup? I need the user to be able to put multiple values in a field but can't limit them to a list. The field that needs multiple values happens to be for SKU numbers from a large department store franchise.
One work around I've come up with is to have a pop up that asks for the separate sku numbers so they can be used for the value list each time, but then the user also would have to select those from the value list in order to enter it.... too many end user steps.
I want to avoid making 10-15 different fields each for SKU number, Item, and cost... I also need to avoid having to populate my db with the department store's full list of products...
View 5 Replies
View Related
Apr 16, 2013
Is there another field that can be used beside the lookup field in an Access table? I am currently using the lookup field as a multivalued lookup field and I am limited to the the things I can do with it when creating a report or a query on that field. Is there a similar field in Access 2010 that has the same look as a lookup field and allows for multiple values to be selected?
View 4 Replies
View Related
Sep 20, 2013
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?
View 6 Replies
View Related
Dec 19, 2006
Hi Everybody,
I've a question that is teasing me. How would I create a multivalue/multiple answer field in access 2002. P'se forgive if this question has been asked before. Thanks in advance,
Samia
View 9 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
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
Sep 18, 2012
I have setup a simple access database consisting of 3 tables
Volunteer
Id (PK)
Name
Job
Id (PK)
JobName
Link
Id (PK)
VolunteerId (FK)
JobId (FK)
There is a one to many relationship between Volunteer and Link and a many to one between Link and Job
I want to create a form from which I'll select a Volunteer Name and then choose a JobName from a list box of options and have it create an entry in link with the id of the volunteer selected and the id of the job selected
So let's say there are 5 entries currently in the Job Table.. let's call the JobNames A,B,C,D and E.
In my form I select my name from the list (sourced from volunteer.Name) and then select B from the dropdown. My desired result would be a single entry gets created in Link with VolunteerId = 1 (representing me) and JobId = 2 representing the selection of B. Problem that I am having is that after I select B in the dropdown it creates a new record for B in Job and the Link table gets updated with that JobId as opposed to simply using the one that was already in the table.
View 1 Replies
View Related
May 16, 2013
I am looking to have entries made to the table based on a date range. I have a database of events and most repeat daily, weekly, etc. When I enter them into Access, they go in as a single entry with a start and end date. But I need to have them as individual entries for each day so that rather than one entry of 5 days, I need to create 5 entries of one day each.
View 5 Replies
View Related
Mar 13, 2014
I have a table in access which is updated weekly; I need to create two tables from this updated table.
1st table will consist of all the new entries for the current week
2nd table will consist of all the entries from the previous week - an amalgamation of all the entries which are not from the "current week" (table) For example; the table below shows the two entries from last week.
ID
Name
1
Adam
2
Ben
This week I have three new entries New entries
ID
Name
3
Charles
4
Richard
So when I run the same query next week I will get something like this.
Old Entries
ID
Name
1
Adam
2
Ben
3
Charles
4
Richard
[code]....
How do I get a query /queries which divides up the weeks new entries and also all the old entries.
View 11 Replies
View Related
Jul 26, 2012
What I am wanting is to be able to build dynamic form content/elements based on entries on a table. This is for a gym membership system. What this form is going to be used for is to allow the front desk to scan a membership card which then performs a search on the database. Part of this search is going to be on a table that contains various add-on classes, tanning sessions, etc that a member can add beyond their base membership.
On the left side of the form, I will display a picture of the member and their name. What I am wanting to do on the right side of the form is to build a dynamic list of the add-on perks they are enrolled in.
I know I could hard program elements on the form to be visible or invisible, but that would be static and leave gaps when I have to turn things off b/c that member isn't enrolled in that class, etc.
Is this even possible with Access and VBA? I know I could do this sort of thing with a webpage using PHP, PERL or whatever. I don't know the limits of Access Forms and VBA.
View 5 Replies
View Related
Jul 18, 2005
Hi,
Anyone know how to create a combo box that has unique month and year entries from a table (month in one column and year in another - i.e. 2 columns)?
I've been able to get the textbox of the combobox to output the correct format using a custom format but it does not affect the combobox data. Also, I'm not sure how to separate this by two columns...
Any help would be much appreciated!
View 2 Replies
View Related
Dec 6, 2004
Last week some very altruistic folks here helped me solve a many-to-many relationships quandry, and I am hoping that there may some others out there who can help with another question.
I have a feeling this is simple, but I can't quite get my head around it, and have not been able to find it in the past forum threads, but....
I want to create a tblJobs that has information about our clients' Companies, as well as Contact. As there can be many Contacts for each Company, my current setup looks like this:
tblContacts
ContactID (Pkey)
Name
Lookup_to_qryCompanies_to_Contact(shows Contact's company)
tblComanies
CompanyID (Pkey)
Company Name
(Client info is included as embedded table based on qryCompanies_to_Contact)
qryCompanies_to_Contact
Company (from tblCompanies)
Contact (from tblContacts) (one-to-many)
In my new tblJobs, I would like to have a combo box in which I can input the company name, and then the next field will have another combo containing ONLY names of the contacts affiliated with the chosen company. (I will also eventually like to make a form that allows the same function, but am assuming that the process will be the same for updating the table trhough a form as it would be updating it directly).
Thank you for any advice!!
Blair Sly
View 3 Replies
View Related
Nov 12, 2014
- I currently have a table that is labeled WorkSchedule. I then Created a query called WorkSchedule Query.
- The column labels are as follows (listed from right to left): Project, Event (something inside of the project), Employee1, Employee2, StartDate, EndDate
- I created a split form that shows each record.
- On each record I made the employee1, and employee2 as a drop down box that allows you to pick from all employees.
*MY QUESTION*
How do I make a pop up form appear when there is a conflicting date based on the employee I picked?
For example: If I had assigned Rebecca to an event that went on from 11/12/2014 - 11/14/2014. Then I tried to assign her to another event from 11/13/2104-11/15/2014. I want a pop up message saying that these dates conflict.
View 2 Replies
View Related
Nov 25, 2013
I attached a screenshot with notes that describes my problem.
View 2 Replies
View Related
Nov 15, 2013
I have two tables, "Membership" and "Class." The Class table has a class ID, class name, and cost of the class.
In the Membership table, I have a field to accept a class ID that is entered by a user. I also have a column called Tuition. When the user enters a classID in the Membership table, I would like the dollar amount associated with that classID to automatically appear in the field called Tuition in the Membership table.
I tried the LOOKUP data type and calculated fields, but no luck. I know that DLOOKUP can be used on forms, but I don't see how it can be used directly with tables.
Is this even possible?
View 1 Replies
View Related
Feb 3, 2015
I am trying to create a image based off a drop down result. I have had no problem with setting up a image based off a static number. e.g 0456432 in the student id field will bring up students photo in the network share.
What I am having an issue with is the control source will look for a number instead of the name that the drop box displays. this is due to a separate table for the drop box.
what is the expression i need to make in order for the name to appear from the dropbox rather than the source id number?
View 3 Replies
View Related
Sep 3, 2014
how data is best structured in Access.I have a table of values (for instance: weight) and I need to be able to look up a weight based on the column header (age) and row header (height).How is this sort of data best structured and accessed in Access?
View 12 Replies
View Related
Feb 12, 2014
II have been working in Access 2010 and by no means would I call myself an expert. I have two different tables, one is called 'JobsList' and the other one is called 'StatHolidays'. On the Jobslist form, I have a field that requires a ship date, however, I don't want to allow the user to select the dates listed in the StatHolidays table. Is there a way that when a user picks a date that is listed in StatHolidays that a pop up box will say "This date is Christmas, do not choose this as a Ship date". The 'StatHolidays' table has a field for a date and for a description of the holiday.
View 7 Replies
View Related
Jul 27, 2015
I have three large source tables imported into my database. I have created queries to retrieve relevant values from fields in each source table which feeds into my form. Each field on my form that is connected to the relevant query is a lookup field. For example, one field called "Supplier_Name" another called "Supplier_Code" and a third called "Route_Number".
Needless to say each of my lookup fields are very long. I am trying to filter my search based upon the selection from the previous Lookup field. How I can filter a lookup field's value based upon the previous lookup field selection? Each Supplier has a code and assign route(s) and I have already established these relationships.
View 4 Replies
View Related
Jun 14, 2005
I am working on a jobs database where employees enter information where the job is being handed off to. I want to create a table showing the latest job entry by date. The jobs are listed by "Job Number" and when I try to create a table and remove the duplicate "Job Number" it does not always remove the oldest entries.
Any help would be appreciated.
View 1 Replies
View Related
May 28, 2014
I have a table holding a list of post codes, and their servicing depot EG -
ID Postcode Depot
1 AB10 Edinburgh
2 AB11 Edinburgh
Then a form, which has a field for depot ( Fld_Depot )
What i would like is, when the user clicks on the field, a msg box prompts, asking for them to input a postcode
Once the postcode is entered, it populates that field with the relevant depot from the table....
View 3 Replies
View Related
Jan 11, 2014
i am working on a school database, in data base i have create two tables tblAccounts and tblTransaction and a form frmTransaction .
tblAccounts contain two fields
GLcodes
Description
and frmTransaction contain
Glcode
transaction type
debit
credit
date
narratives
in form when i enter a glcode, lookup field match the code from tblaccounts and shows the description in form against gl code.But i am facing a problem when i enter a wrong gl code my form accept it and move to the next field and when i leave blank field of glcode same problem that i am facing, i want that , when i enter a wrong glcode in a form amsgbox will apear that asking for correct glcode.
View 5 Replies
View Related
Aug 2, 2013
Table name: Items
Field: ItemName
Form name: Frmpayments
when I open the above form, it should create texts boxes on "frmpayments" based on how many data available in "Items" table under "Itemname" field. It'll be even better, if it can put those item's name in each text box it creates.
View 1 Replies
View Related
Sep 14, 2012
i have developed an application in access 2010 . and split into front and back end . now i want to add more tables in back end and i need to define lookup list in table definition from the query presently in front end . when i get into lookup list and query builder doesnt show front end content ... how to solve this problem ?
View 2 Replies
View Related
Sep 30, 2011
I created a multivalue list in my table. It contains a list of names and a checkbox. I designed the form based on this and am able to check off multiple names. When I go back to look at the table, it stores in each name separated by a comma. So far so good.
When I create a report to display the data, it displays it as 1,2,3..etc., instead of the names separated by a comma.
What do I need to do to display the names ?
View 5 Replies
View Related
