Relationships And Building Drop Down Boxes From Various Tables
Aug 2, 2006
Hi
I am trying to build a database in which there is a main table and in this main table there are products and there are types of products eg.
Ringtones - is the Product and Type - True Tone(mp3), category - R&B
another eg is Graphic is the product and Wallpaper is the type of graphic Category - friends .
I would like to combine this so when you are in the MAIN products table with
"Code" ,"Product" ,"Type" , "Category", "Title", "Artist" and "Price" that the drop down boxes say for instance when you click on "product" and you chose ringtone then in the "type" column there are only the options from the Ringtone type and not also for graphic etc.. is this hectic to do?
Hope I have explained it ok...?
Thanks !
Really hope some one can help me with this ?
Melissa
Cape Town
SA
I have created an invoicing system for my business, as i was unhappy with MYOB. Basically i have Product ID and Desciption in 1 table. In another, called registry, this is where i input the data for the order.
What i basically want to do is? When i type in the Product ID in the registry table, i want the description field to automatically appear in the cell next to it. As this would save a lot of time
I am really at the end of my teather with this problem so i really hope someone here can find a solution.
I have 2 tables; Client (Client general info, defined by their location), ClientHardware (Info on the hardware a client has and also it's condition).
I need to be able to select one or more clients and display one or more conditions of their hardware, e.g. London, York, Bury + Red, Amber, Green condition.
After the Query is working right i will need to output it to a report through a button on the form.
I attempted adapting This Method (http://www.databasedev.co.uk/query_using_listbox.html) but adding another list and query just resulted in the report showing the all the records of the selected client (e.g. london) then all the records with the selected condition (e.g. bad) it would be ok if i could merge the list box selections into one query but right now it looks like this
MyDB.QueryDefs.Delete "qry ClientName" Set qdef = MyDB.CreateQueryDef("qry ClientName", strSQL)
MyDB.QueryDefs.Delete "qry RAGType" Set qdef = MyDB.CreateQueryDef("qry ClientName", strSQL2)
Is it possible to put the variables in strSQL2 into the creation of qry ClientName somehow?
Any help, large or small will be appreciated as I'm really at a dead end with this.
In looking through the forum I am not finding what i need to do. I am sure the answer is on here, but perhaps I am not wording my search correctly. I apologize in advance if the answer is on here and I don't see it.
My problem is I don't know how exactly to begin.
I want to keep track of 11 rules and that could have 1 of 4 different things (violations, transfers, letter sent, referred out) could happen to each of those rules.
For example rule #1 I could have 10 that no violation occurred, 5 transfers, 1 letter sent and 2 letters sent as seen in the attached picture.
I created tables for each of the following: violations, transfers, letter sent, referred out.
Each of those tables has a fieldname for the 11 rules. How do I link all those tables together?
Hopefully I explained myself and i appreciate any help that I can.
I have a drop down box which shows Quote: Code | Surname | Forename | Faculty | Team However I can only get 1 drop down box to enter its data into one cell. What I want is for 1 drop down box where there are currently 4 which will fill in all the details.
I am just starting out with a database and last night got more success than I imagined in that I have the basic database, a usable form and a report set up and displaying mostly as I want.What I would like to do is to be able to look on a form and select certain criteria and only see records that fall into them.The database is for magazines so for example I would like to be able to select "Prima" magazines and only see those, then maybe select 2005 and only see the Prima magazines i have from 2005,
I have a form ive created as part of my database used for data input.
Someone opens the form , enters their project number, and then enters what country it is in, what region it is in, and what sub region it is in. They are all dropdown menus with the information coming from a table via a lookup wizard.
What i want is when someone choses england in the country field, i want the region field to only displays the regions in england, and then in the sub region field i want only the sub regions that are in the regions displayed in the drop down list.
Is there a way in which i can link three drop down boxes together?
I'll create an example of what I mean...
I'll attach a Excel file which contains two identical filters, one of them where the filter is turned on.
If I select the "Serviced" filter, I am left with three options for "Make" and two options for "color". If I select the "color" as "White" I am left with just one "Make" - "MG".
I understand that some sort of "hierarchy" needs to be in place so I will Make it so that "Serviced" Must be entered first, then "Color" then "Make" so that if you select a different "Service", the "Color" and "Make" will repopulate with the appropriate values.
This is certainly possible with a REDICULOUS amount of coding that would map each value to its corresponding values however is this possible some other (easier) way?
I'm making a library database program thing... There's an option for the user to view all books on loan.
I have two tables:
Books, which has columns ID*, ISBN, Author, Title, Year, Location BorrowerStorage, which has columns Book ID, Name, Email Address, Desk Number
Book ID in BorrowerStorage is related to the Books primary key.
Now, for the viewing all books on loan, I want it to produce a read only table which contains all the entries from the BorrowerStorage table and the corresponding Title/Author columns (i.e. the records for which the ID in Books column = BookID in Borrower Storage column)...
Hello to all The problem that I am facing is the following. I am trying to create a form that has the following: 1. A combo box in which you select from a list of users 2. A list box which is populated when I select a user showing me the courses that they have to take 3. Another list box that is populated when I select a course showing me the dates available for that course 4. Finally a third list box which is populated by what I drag or double click on the dates list box basically scheudling the user to his courses.
The first 3 parts of this done what is left to do now is the hard part which is to be able to drag and drop the dates in the other listbox which basically maps that user to that course date and populates the corresponding table. Any suggestions on how to do this would be highly appreciated. Thank you in advance
I have created a database that has both English and French forms feeding information into the same table. I'm not concerned if the info stored in the table is stored in French or in English. It all works fine other then the drop down boxes in the forms, here I only seem to be able to either have the English or the French options visible on both the English and the French form. Is there a way to have the English options on the English form and the French options on the French form, both feeding the results into the same field in the table.
I've made a query and designed a report for it. Simply it includes:
Area code, customer name, other customer details.
I want other people, when they click on the report to be given a drop down box which allows them to choose a specific area code before it generate the report. So, for example, they just want to look at Yorkshire region records, they choose Yorkshire from the drop down box and it'll generate the Yorkshire report. I've searched around but can't find what these are called.
I am designing a search query that will allow the user to look up a record in a database to view it. I have everything already set up, and most of it working properly.
The user can recall a database entry using 7 different criteria--Type, Customer, PartNo, JobNo, Warehouse, Bin, and Shelf. The Type and Warehouse entries on the database are drop-down values, the other 5 values are text entries.
So far, I have been able to get the look up query to pull up the desired records on the Customer, PartNo, JobNo, Bin, and Shelf criteria using
Code: Like "*" & [Forms]![Search Form]![Customer] & "*" .
However, with Type and Warehouse criterion, the two that use drop-down boxes in the database, I have been unsuccessful in being able to call up any records using either the above partial or the more exact:
Code: =[Forms]![Search Form]![Type]
I did try to change Type to a textbox on the look up query, but that was similarly unsuccessful. On a side note, I must use drop-downs on the Warehouse field since I have another query that concatenates that value with a couple others.
How can I, without delving into VB coding unless absolutely necessary, format the lookup query so that it will read the values of the drop downs?
I want to be able to attach a date field to a dropdown check box. For example, say I have a client who we submit multiple deliverables to on different dates. I want to be able to check the deliverables submittted and add the date for that deliverable (each deliverable has a different date). image which is how the drop down is currently set up.
I have a query which works perfectly fine, it's the report that I'm having issues with displaying correctly.
My report is a daily personnel accountability report that shows where everyone is for the day. Instead of having a cumbersome query like I did before, I have opted to just use the results of the selected drop-down option to move the X to the appropriate box of where so-and-so is for the day.
Using
Code: If [marked_as] = 1 Then Me.Morning.Value = "X" ElseIf [marked_as] = 2 Then Me.Afternoon.Value = "X" ElseIf [marked_as] = 3 Then Me.Evenings.Value = "X" End If
I was unable to get it to work accurately outside of showing the three dummy names under the same column, even though the three dummy names were each placed in one of the three test categories.
I have this set as a private function called when the report loads, which is based on a query that filters down to the exact department or office (depending on the user's selection). Like I said, that part works fine, it's getting the code to accurately display in the correct column.
I am trying to setup a database in order to demonstrate a tie in between active directory and the HR side of a business.As such, I would like to select two fields from drop down menus that reference in Department and Location tables, but use this data to actually Populate the Personnel Records table rather than Query.I know this is not normally best practice from a DBA perspective.
I have this setup already in Filemaker, just through using relationships.However, I want to move to Access, as it is more industry standard and much lighter weight, so am trying to find the best way.I have attached two pics of my filemaker database, and a zipped copy of my Access file.
Why is it that everytime I print a report containing Drop-down Boxes, the selected value for that particular record is blacked out?
This is a screenshot of what I'm talking about: http://ww w.hotlinkfiles.com/files/1177902_txqed/AccessReport-Error.JPG All the blacked-out bits are supposed to be the selected values for each record.
I use Microsoft Access 2002. How can I fix this so that the actual value is legible?
To recreate the problem: 1) My table contains a field (called "Colour") that can only be satisfied by certain values ("Black"; "White"; "Coloured") as defined by a drop-down list. 2) I enter a record about a white scarf. Therefore under "Colour", I select "White" from the drop-down list. 3) I make a report from my table. 4) I print the report. 5) However, when I look at the record of the white scarf, all I see under "Colour" is a box containing the values "Black" and "Coloured", and in between them, the selected value "White" should be, is a black line.
I currently have 3 tables within a database with student details of three different classes. I need to create a user form that has a dropdown box which I can select a student from one of these tables with a number of text boxes below which brings up all the students details, then once the student has been selected and the correct details are shown then I need to create a button which allows me to move that student from one table to another.
I have 5 tables and 2 forms. The primary form is what I input all the information into (Tracking) and the other form is to update employee information (update form).
The "Tracking" form is where I add information to 4 of the 5 tables. Here is where I'm stumbling. Would it be more practical to just have 1 table and just expand the fields, or have the form put the information into the separate tables. Those 4 tables are Employee, phone, spotter and radio.
I'm wanting to keep a running tally of who doesn't turn in what equipment on what day.
I am converting / developing a database that stores information pertaining to individual birds and their recaptures over many years. Here is a condensed version of the many tables in this database:
tblIndividual Bird: Autonumber (Primary Key) Band Number - also, unique to the individual bird Sex - M or F etc ..
tblCaptureInformation: Autonumber (Primary Key) Band Number - look-up from tblIndividualBird (using hidden Primary Key) Capture #- # which indicates what capture this is (ex. Intial capture - 1) Place Age Date etc ...
Each time a bird is captured, we record information pertaining to TIME, MEASUREMENTS, and NEST INFO. So, I have seperated the data based on these headings and made them into individual tables.
Now, my problem .... I have already created a relationship between CaptureInformation and Individual Bird. However, in the last 3 tables I would like to create a drop-down menu which shows the Band Number and Capture Number and make relationships there. What is the easiest way to do this? As of now, when I make a look-up field in the last 3 databases to show this info, the Band Number comes up with the Autonumber (because I am using the CaptureInfo table) which does not really help someone entering the data. Thanks for your help.
I am trying to create a db for service orders for customers. At the moment I have four tables, customer, service_order, parts and totals.
I have one form for customer records that has a button that when clicked opens another form for that customer's service orders. The service order form has two subforms, one for parts and one for totals.
When I try to add a new service order for my test customer it says "you cannot add or change a record because a related record is required in the table 'customer'.
As you can see here (http://www.abstractmusic.org/relationships.gif) I have three relationships setup. cust_no in customer table is a PK and so is service_order_no in service_order table.
Also I am having problems with the totals, as the fields are from different tables the equations won't work from within the subform (I guess I need some kind of query). I need the totals in a seperate table other wise I have a total for every part entry.
I am trying to set up a database to detail dances published in a magazine over the years.
I currently have all the information in an Excel Spreadsheet but know that Access would be better.
The columns in my spreadsheet are:
Dance Choreographer(s) Level Count Date Published Song 1 Artist 1 Count In 1 Song 2 Artist 2 Count In 2 Song 3 Artist 3 Count In 3 Song 4 Artist 4 Count In 4 Song 5 Artist 5 Count In 5 Song 6 Artist 6 Count In 6 Song 7 Artist 7 Count In 7
There can be two or more dances with the same name The same choreographer(s) could have written more than one dance The same count can be used for many dances About 15 dances are published on the same date One artist can have more than one song used One song can have more than one artist singing it One song and relevant artist can be used for more than one dance
I tried using Access For Dummies but it has confused me even more. I cannot work out what tables there should be and what relationships.
Not all dances have 7 songs for it - some have 1, some 2, some 3, etc.
What is listed as song 4 for one dance could be song 1 for another or song 5, etc.