Hello. I'm making a database to store movie info for a DVD collection. I have a table with fields: title, year, runtime and such. I want an indefinite number of genre to be associated with a movie, so I created a "genre" table and a junction table between that and the "movie" table. Now, on form I want to be able to filter a list of movies based on a genre selection from a combo box. The only way I can see to do this is to make the rowsource of the listbox a query with "tblMovie.MovieID, tblMovie.Title, tblJunction.GenreID" connected through MovieID. This would work except that if a movie is associated with more than one genre on the junction table, the movie appears on the list more than once. I want a movie to appear only once. That's my problem. Thanks in advance for the help.
Hello all. I've got the query: SELECT DISTINCT tblCustomer.Customer_id, tblCustomer.Customer_firstName, tblCustomer.Customer_lastName, tblTicketType.TicketType_subscriber FROM (tblCustomer INNER JOIN tblAttendance ON tblCustomer.Customer_id = tblAttendance.Customer_id) LEFT JOIN tblTicketType ON tblAttendance.TicketType_id = tblTicketType.TicketType_id WHERE (((tblTicketType.TicketType_subscriber)=True) AND ((Year([tblAttendance].[Attendance_date]))=2006)); and it's not working properly.
It gets the correct values fine, but I have problems where a single record in tblCustomer corresponds to two seperate records in tblTicketType that has the first record of TicketType_subscriber set to 'True' and the second to 'False'. This results in bringing up the same customer ID, etc twice in my report.
The only way that I know that I can get around this (in MySQL anyway) is to use a GROUP BY statement, that'd make it look like SELECT DISTINCT tblCustomer.Customer_id, tblCustomer.Customer_firstName, tblCustomer.Customer_lastName, tblTicketType.TicketType_subscriber FROM (tblCustomer INNER JOIN tblAttendance ON tblCustomer.Customer_id = tblAttendance.Customer_id) LEFT JOIN tblTicketType ON tblAttendance.TicketType_id = tblTicketType.TicketType_id WHERE (((tblTicketType.TicketType_subscriber)=True) AND ((Year([tblAttendance].[Attendance_date]))=2006)) GROUP BY tblCustomer.Customer_id; Unfortunately, Access won't allow a GROUP BY clause like this without every field that I'm selecting being included, and that means that because there's two distinct values for TicketType_subscriber that the record still comes up twice.
Hi, Wish if some one could help me ASAP. I have a table which contains name, tel, email i need to import only records which have distinct email. I do need need to import data of all three fields but only which has distinct email. As there are number of record which are duplicate. They have different names but same email. So i need to condition only for distinct email but dump the data in a new table with all three records. so same names can have different email. but same email can't have duplicate email. So need only records which have distinct email. Please help .......
I have an access db that is linked to a sharepoint list (the list originated in Access).I tried a simple select query with parameters to search for by id and another query of the same table to search by date.They both come back with the same error of "cannot reference a table with a multivalued field using an IN clause that refers to another database"Iv'e gone through the table and these fields that the query is using and none seem to have any multivalues.
Using a database 2010. I have a monthly import from a spreadsheet to one of my tables. Sometimes it has the same previous data from prior month or months that has not changed. I don't want to keep appending this data to my table if none of the fields have changed.
Is there a way to append data to table only if fields have changed in an append query? Ex. (Name, Address, PhoneNum, Rate) If all fields are the same; don't append. If one or more fields are changed; append.
Below is a sample of the table with the data. I manually added the 1 and 0 to the hc_Year field. However, I would like to create an Update query that will add a 1 to the hc_Year if its the first instance of PIDM & regsYear and add a 0 to the records that are not the first instance.
I have a table of data which includes a date field and also various other fields which may or may not be filled with data.I'm trying to populate a listbox with the months for which this data is missing (a separate macro will then loop through these months to fill the missing data) But I only want each distinct month to populate the listbox - not each individual date.
Code: strSQL = "SELECT DISTINCT month([EntryDate]), year([EntryDate]) FROM [SampleTable] WHERE [ValueField] Is Null" Me.lstSampleListBox.RowSource = strSQL
I am trying to do something that I would think is very simple but I'm having a hard time with it. I have a form that consists of questions that the user is needed to answer. It displays the question, a combobox where the User can select 'Yes' or 'No', and a textbox where the user can input their comments. What I would like to do it the following: if the user selects 'Yes' as an answer, I want the corresponding comments box to be required.
I just started learning Access and I have created a database with 53 records, 3 tables and 1 Split form. I creared a new yes/no field and I am attempting to display "Completed" in green for yes and "Not Completed" in red for no. I used the correct code in the format field:
"Not Completed "[Red];" Completed "[Green]
This worked for different field before I created the split form but now I cannot get the new field to display anything but check boxes regardless of what code I use. It won't even let me use the default yes/no, true/false or on/off options.
I've been studying this for about 2 weeks and I've figured out quite a bit but this one thing is really iritating me....
I have a field that is in text format that is used as a discussion box. This limits the user to 250 characters because of Access limitations. I have tried the memo format but that will not allow me to query off of that field. How can I have this discussion field with unlimited text capability and also be able to query off of it? Any help is greatly appreciated. Thanks!
I have a number in a column that can be up to 10 characters long. where it is not the full 10 characters i want to be able to insert zeros at the front. IE 12345678 to 0012345678
I would like to be able to do this in an append query. Is there a function that allows me to do this?
Private Sub Phone_Click() Me.Phone.SelStart = 0 End Sub
Access will no longer make an MDE file. I was trying to make it so when the Phone field was clicked in the focus would be at the beginning of the field.
If I remove these lines, an MDE is possible again. I have tried it at the beginning and at the end. Any thoughts?
I previously used an older Access version under XP, but I switched to Windows 7 (64-bit) and Access V10. In general, most everything appears to work as expected, but I can't be sure because I have not gotten past the "previously simple" task of making a simple label !!!!
When I construct a LABEL layout, if I use ONLY one field per line, everything appears to work OK.When I put MORE than one field per line, the additional lines are "accepted", but when I click "finished" to preview the finished labels, I get an #error on every line that has more than one field, but the lines with only one field print out as expected.If I then go into the DESIGN mode, each line with more than one field displays:
=Trim([field-x] & " " & [field-z]), where the " " is related to the space I place between field-x and field-z.
During this process I also get a box with the message "Enter Parameter Value", and below that is the text, "Trim", with a box in which to insert some kind of "parameter". I have absolutely no clew what to put into the "box", and no where in the 812 page, "The Missing Manual" is the question answered.I have wasted approximately five hours trying to solve this issue, including google searches for potential answers, some of which seem reasonable, but do not work when implemented. At this point I am ready to trash ACCESS and move on to some other process.
I have a form where I would like one of two fields mandatory to fill.I have an encryption password field and a pen drive password field. I would it so when the user tries to close the form, one of these two fields has to be filled in.
Hi I have a very simple table, with say 5 fields (all text). There is only 1 table. The first field is name, and I want to enforce its uniqness across all other table.name values. I am completely new to Access database and am not sure how to do this. Any help would be greatly appreciated.
I have a subform that shows notes entered but if there is more text in the field then you cant see it all. Can I make it so that this field will grow so the whole input is visible or can I make it so when you click on it it opens in a larger window showing all the txt?
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...
I have a subForm set to continuous forms. I need a field called txtStatusDetails required (cannot be null) if the field txtDutyStatus = "Not Available"
Here's what I have so far:
Private Sub Form_Current() ' This procedure runs after the record changes. On Error GoTo Err_Handler ResetAvailability Exit_Proc: On Error Resume Next Exit Sub
In my Student Administration database I am trying to launch a popup form from a sub-form of a Job Positions and Seekers form. The sub form is actually one of 2 sub-forms, both Continuous Forms default view, on a form linked via an unbound control (MasterJobPosID).
The first sub-form is called and lists open Job Positions and the 2nd sub form is called and lists all Students Seeking each of the positions. The Students Seeking subform is linked to the unbound control MasterJobPosID in the main form which is set to equal the Job Positions subform's JobPosRecNo field which is the key to the Job Positions table.
The popup form is called Job Employment and is being launched via an event macro from the Students Seeking subform. It will allow the user to create a record in the Job Employment table. The event macro has a Where statement that says "=JobPositionID And Student", an attempt to link it to both the Job Position record being filled and the Student filling it.
I am able to populate the popup form's fields in expressions setting Default Values equal to fields on the loaded Job Positions subform and the Student from the Students Seeking subform. What I am unable to get is the Job Position record key from any of the 3 fields it appears in on the Job Positions and Seekers form:
MasterJobPosID on the main form.JobPosRecNo on the Job Positions sub form.JobPositionID on the Students Seeking subform.
I want that to link the new Job employment record to the Job Position record the student is filling. In fact I get a parameter prompt for JobPositionID when the popup form is launched.
I've inherited a data base which has the address details of our members spread across multi fields i.e. Add 1, Add2, Add 3, Post town, Post code etc.
Not all the fields contain information which means when I do a mail merge for address labels there are blank lines.
I would like to either be able to create a single field in the table (like a memo field for example) which contains all the address detail, or create a mail merge without blank lines.
