Design Problem - Filtering Combo Box Items Based On Related Table Field
Hi im stuck on filtering a combo box (i am using an sql query to populate a combo box with a filtered selection, this is in Access but im after some design sanity checking). I have users who can be assigned a category. Jobs can be assigned a category and also have a list of people working on that job (in the JobDetails table). I have the following tables:
Categories Table:
CategoryID (PK)
Description
Users Table:
UserID (PK)
Username
CategoryID (FK on Categories.CategoryID)
Jobs Table:
JobID (PK)
CategoryID (FK on Categories.CategoryID)
JobDetails Table:
JobID (FK on Jobs.JobID)
UserID (FK on Users.UserID)
Then in the job details when listing users for a job (many users can be for one job) i would like to only show the users which have the same category as the jobs category. Is this possible? i tried the SQL below for the lookup column field JobDetails.UserID but it doesnt work:
SELECT Users.ID, Users.Username, Users.CategoryID
FROM Users, Jobs
WHERE (((Users.CategoryID)=[Jobs].[CategoryID]));
All the tables are linked with relationships but my SQL isnt so hot!
Any ideas as to how i would do this and get it working?
Even if it can be done, is this even recommended? I can see funny conditions happening if the job details category changes or the users category changes then even if they are existing in the job details list they will not be shown? Even so, i would be interested in the above to know how it is done (if possible).
Thanks in advance,
Chris
View Complete Forum Thread with Replies
Related Forum Messages:
Filtering On A Combo Box Based Off A Autopopulate Field
Hi, i've searched the forums but couldn't find an issue/resolution that fit my problem. I tried to put as much info as needed but please let me know if more info is needed. I've set up one of my forms that require a signature with a combo box "CurrentUserName" to capture an electronic signature from a jpeg stored on the local server. The CurrentUserNamecombo box has a one-to-many relationship between another table named "Electronic Signature" that holds the user's network ID and the Path on the server where the signature file is stored. The primary key in the second table is also named "CurrentUserName". This works perfectly, i select a username from the drop down list on the form and the picture atumomatically populates an unbound image field with the image. The issue is, i only want the current user to be able to select their own username from the combo box on the form and hide the other usernames so i need the CurrentuserName combo box to be filtered on the form. So what i've done is create another field named "CaptureUser" that will automatically capture the current user's network user ID using the =CurrentUser () as the defualt value of that field once the form is opened. What i need to do now is connect the two fields so for instance, if current username "jblow" is in the CaptureUser field, the CurrentUserName combo box will check the CaptureUser field and filter the choices so his name will only appear in the CurrentUserName combo box. Hope that made sense, any help is greatly appreciated!
View Replies !
Combo Box To Add Data Items Based Upon Table
I have a small database, that I would like to use a combo box to allow user to select a value based upon values already entered on table, and then if value is not found enable the user to enter a new value that will then become part of the selection for future record adds.
View Replies !
Filtering Out Items In Combo Boxes
Need some help here.... I am designing a database to keep track of workers for a haunted house. I have a Roster table, a table of all the nights we are open, a table of Spots in the house, and a table to record who works what nights and what spot they are in. This table has a Room combo box and a Spot combo box. The Room combo has a Row Source of SELECT DISTINCT SpotsAll.Room FROM SpotsAll ORDER BY SpotsAll.Room; The Spot combo is then populated with VB code all the Spots that are in that room. That works fine. This is what I'm trying to accomplish: When a Spot is assigned to a Worker for that night, I want that spot to no longer be available in that list FOR THAT PARTICULAR NIGHT. So, lets say Joe Somebody works in Spot 1 (out of 4 lets say) of the Library Room, when we assign another person to the Library room, I don't want Spot 1 in the Spot list. Can this be done with a query or VB code? If I'm not making sense, please let me know. I can also upload the Database I am creating so that you can play around with it and see what I mean. (I have attached a word doc. that shows the table relationships.) Thanks a lot ahead of time!! ScrmingWhisprs
View Replies !
Table Design Question -- Baseball Related
Hello, I'm not sure why I'm drawing a blank but I am. Could I please get a few suggestions on how you might organize these fields into tables? These are not baseball stats fields. I'm keeping track of how often teams win over the years. I would like to do generate a team webpages showing all historical wins, losses and championships. TeamID TeamName Year Wins Losses AL_DivisionChampion (3 divisions in each league EAST,WEST, CENTRAL) NL_DivisionChampion (3 divisions in each league EAST,WEST, CENTRAL) AL_WildcardChampion NL_WildcardChampion ALCS_Champion NLCS_Champion WorldSeriesChampion Initially I thought about YES/NO fields but there are so many variables. I need to know STL won the division but also that it was the NL Central. Any help would be greatly appreciated. Thanks Rob
View Replies !
Combo Box & Related Field
Hi, Can anyone tell me how I can do the following without using code: I think it's really easy, and am sure I've done it hundreds of times before, but my minds gone blank... When I select an entry from a combo box in a form, I'd like a related entry to appear in another field on the same form. I have a table with the both fields stored in it, although theres only 3 records so this might not be necessary. Many thanks, Nicola
View Replies !
Table Related To A Field
Hello Could you help me ? My problem is : I'm developing a ms database for the a "repair service" . I created a table witch contains basic data from customer, including its unique ID number (key field), name and contacts ... Now I need to store the "repair service" data from each costumer ( its time consumption, replaced materials...) in a separate table but, If I'm thinking well, I must have a table for each costumer so, I will need to link each individual "repair service" data table from each costumer to each costumer itself. Do you know how to do this ? Lots of thanks in advance, Miguel
View Replies !
Querying Two Inter-related Field In A Table
Hi, Need some help. Here's the problem:- I've two fields in a table (A & B) A B 1 Null 2 Null 3 1 4 2 5 3 6 5 7 Null 8 4 9 6 The fields are somehow related where values of B (unique) is taken from A(unique). I trying to do an SQL such that if I input a single value A=9 or 6 or 5 or 3 or 1, the following should be the output: A B 9 6 6 5 5 3 3 1 1 Null Or in another case where I input a single value A=8 or 4 or 2 the following should be the output: A B 8 4 4 2 2 Null How can I do that? Many thanks in advance!! :D
View Replies !
Field Show And Hide In Form: With Ref To Related Fields In Other Table
Hi all i am working on a hospital inventory system, can any one help me with my problem--ie i have made: 1. Transaction table: Fields include both sale and purchase field 2. Transaction Type: include Sale,Purchase and sale purchse return 3. table 2. is look up table in table 1 4. Is there any way in which i can display related fields in form eg if i use drop down in transaction table ie sale it will show me bill details and if i use purchsase it will hide bill details and show related fields
View Replies !
Filtering Field In 2nd Subform Based On Selection In 1st Subform
I almost have this form done. I have a form with a combo box, and 2 subforms from it. When you select in the combo box (Group), it brings up the choices (Sections) for that group in a subform. Then there is another subform, that is supposed to bring up a series of questions that are related to the section that has been selected. The question field has a drop down to a list table, that has all the questions. Once the question has been selected, it stores in a Master table. The relationship is there. If you try to select any questions that do not pertain to the section, it gives you an error message. What I need it to do, is when the section is selected, filter out the questions that pertain to that section, and have those questions be the only ones available in the drop-down list to choose. I know that I need to have an After Update code once the section has been selected, but not sure how the coding should be. I hope that someone can help with this! :confused:
View Replies !
Filtering Field In 2nd Subform Based On Selection In 1st Subform
I have a form that is almost complete I have a form with a combo box, and 2 subforms from it. When you select in the combo box (Group), it brings up the choices (Sections) for that group in a subform. Then there is another subform, that is supposed to bring up a series of questions that are related to the section that has been selected. The question field has a drop down to a list table, that has all the questions. Once the question has been selected, it stores in a Master table. The relationship is there. If you try to select any questions that do not pertain to the section, it gives you an error message. What I need it to do, is when the section is selected, filter out the questions that pertain to that section, and have those questions be the only ones available in the drop-down list to choose. I know that I need to have an After Update code once the section has been selected, but not sure how the coding should be. I hope that someone can help with this!
View Replies !
Design Question: Updating Related Tables
Greetings. As a relative newcomer to Access, I am having trouble deciding on how to design the form flow for updating and creating related records. I'm looking for a variety of suggestions so that I can decide what's best for the user and what's best for screen flow. Here's the structure: I have what's called "an Event". Each Event can have multiple "Trials". Each "Trial" can multiple "Classes". (This is the structure for a dog obedience competition) Tables: tblEvents - PK = eventID tblTrials - PK = trialID, FK = eventID tblTrialClass - PK = trialclassID, FK = trialID Currently, for "display" or "browse": I have the following form structure: frmEvents - single form format with a subform called sfrmTrialInfo, no navigation control - entry to this frmEvents is from an unbound pop-up form that uses a combobox to select the Event for viewing. sfrmTrialInfo - single form format with a subform called sfrmTrailClass, has navigation control so that the user can display all trials for this event sfrmTrailClass - continuous form format with navigation control so that user can display all classes for this trial. My dilemma is what is the best method for letting the user create a NEW Event-Trials-Class structure. Right now I have a button on that pop-up that is used to create a NEW Event. It brings up a blank frmEvent form with the blank imbedded subforms. This is still in the design stage. I want to have control over the data (as the user is typing ... don't let the user move forward until all requisite data is entered), but I, also, want to make the process relatively pain-free for the user. Do you have any suggestions on a design? Thanks, in advance, for your input. :) Regards, SueB
View Replies !
Auto Insert Field Info Based Upon Combo Box
Someone once posted an database on how to do this but I was not able to see how they were able to automatically input the data into a text field. I know how to do a combo box to query and input those results into a combo box but I am confused how they got the information from the combo box selection to automatically put the other variables into the testbox. They did this without using the dlookup command. In fact it was done without any code. They showed how to do this automatically without code and with two other methods (one being dlookup). Can someone please help me if it is clearcut as I don't have too much time to search the forums for empty answers (believe me I have already wasted too much time) Thank You
View Replies !
Field Values Based On User Selection In Combo
Hello, I'm stuck! I want to create a form which has a combo box where you select someone's name, and their address appears in another text box on the same form. The names and addresses are in a table called "cardholders". I'm doing separate combos for first name, surname cos I think that's the only way you can do it. The fields in the table are credit_add, credit_name & credit_surname. The combos are called "cardholder name" "cardholder surname" etc. This is the code in the text box where I want the address to appear:- =DLookUp("[credit_add]","cardholders","[credit_name] = '" & [cardholdername] & "' And [credit_surname] = " & [cardholdersurname]) Can anyone help? Thanks.
View Replies !
Table Field Design Help !!!
Recently I'm helping a friend to convert his MS-Excel spreadsheet into MS-Access. I met a problem and need some help on defining the table fields. In one of spreadsheets, he gets 3 columns, the 3rd column values is calcuated by a formula based on the previous two entries as the followinf: D3= C3+B3 When I tried to turn this table into MS-Access design, I don't know how to define the 3rd column (field). Is there any chance I can set any expression/formula for 3rd field in the table defnition to retrieve data from previous columns( I don't want to design this function in the query)? Many thanks
View Replies !
Make Field Auto Populate Based On Value Of Field In Another Table
I'm creating a database that keeps a track of questions and scores. The questions in the database need to be dynamic and are changed frequently. I have a scorecard table which keeps a record of scores and the applicable question at the time the record was saved. I need to do this because in 6 months time we may want to provide feedback. As the question may have changed we need to be able to refer back to what the question was. I want the question field in my scorecard table to populate with the value in my question table. I have tried a number of things including setting the question field in the scorecard table to a lookup based on the following query: SELECT tblQ1.Q1 FROM tblQ1; This works however only as a list or combo box. I don't want the user to have to select the question. I want it to auto populate, is this possible?
View Replies !
Second Combo Made Visible Based On Associations Table And Query
I have a form that is used for recording test scores. The first selection that is made is a type of test. After the test is selected, some tests have a second sub-type of test that needs to be recorded. My quetion lies in how to hide or grey out this second combo box until a test is selected that requires it. I don't want to "hard-code" it in the sense that I create an If Else statement that requires looking at values from the 1st test type combo box. I want to make sure that the DB is scalable and when additional tests are added, additional sub tests can be added if necessary. If this makes sense and anyone has any ideas, I would appreciate it.
View Replies !
Related Consultants And Assignments Based On Dates
In my database I have: * A table of consultants * A table of contracts * A table of assignments (shows which consultants are assigned to which contracts, along with the start and end dates of those assignments) * A table in which consultants book their time to projects The consultant assignments change over time. What I need to do is create a query that shows a) the amount of time a consultant booked to a project(s), and b) the contract to which he/she was associated at the time. For example, if a consultant was associated to Contract A in February, Contract B in March, and Contract C in April, I want to be able to accurately reflect that in my query. I'm not sure how to go about doing this in my query. I'm sure many here have solved similar problems, so any help you can give will be appreciated.
View Replies !
Creating A Form Based On Three Non Related Tables??
Hi I was wondering how to create a form which is using data from three tables, the data in the tables does not have any relationship setup as they are not related to each other. When I use the wizard and I select the three tables, Access starts complaining about the fact that no relationship has been setup... Please help, Thank you, Gurkie
View Replies !
Filtering The Last Record Of Many From Many People Using Totals In Query Design View
I have made a couple of posts about this and had no response as yet. Maybe i didn't explain it well. I have a form that logs emails. The emails are either "to" or "from" a person. I want to have two queries that pick out either to or from. Now each person i know sends me several emails over time obviously but i am only concerned with their last email or MY last email to THEM because that determines if i should email them back or if i am waiting for them to contact me. At the moment i am having problems filtering out the last record for each person which determines whether i need to email them or they need to email me. I also use the record to log when the last mobile text was sent, phone call etc so it is not just emails. The following is what my formsfields look like CommunicationID Communication type TO or From Date Day (automatically taken from the date) Subject I cannot for the life of me get the filter to work properly. Currently i am either getting more than one record for each person or the results are jumbled up. I really need a better understanding of how to use the totals thingy in the querys design view. I'd really appreciate some help on this. Thanks....Ross :confused: :confused: :confused:
View Replies !
|
TRACKER
