Making One Field Distinct With A "where" Clause?
Aug 28, 2007
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.
View Replies
ADVERTISEMENT
Jan 15, 2007
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.
Anyone got any ideas of how to get around this?
View 4 Replies
View Related
Sep 15, 2006
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 .......
View 1 Replies
View Related
Oct 10, 2005
I have data which consists of:
xxxxx123 A.Nother 123456
xxxxx123 B.Jones 123457
xxxxx456 D.Smith 123458
xxxxx456 Z.Zephir 123489
How would I ensure that the query returned only unique rows (where column 1 is unique) based on the first alphabetical record of column 2?
Any ideas?
View 1 Replies
View Related
Sep 18, 2014
I am trying to open a form using the where clause to max of a table field.I cannot get the syntax correct.
Code:
DoCmd.OpenForm "frmInput", , , Max(tblClients.ClientID) AS MaxOfClientID FROM tblClients
View 4 Replies
View Related
Jul 3, 2014
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.
View 6 Replies
View Related
Jun 5, 2013
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.
View 14 Replies
View Related
Jun 4, 2013
How to query Select Distinct field + showing other fields? E.g.:
Code:
SELECT DISTINCTROW assetMovementTable.assetNo, assetMovementTable.moveCode, assetMovementTable.compCode, assetMovementTable.compCodeDesc, assetMovementTable.assetDesc, assetMovementTable.equipType, assetMovementTable.equipManufacturer, assetMovementTable.equipModel, assetMovementTable.constYear, assetMovementTable.plateNo
FROM assetMovementTable
[Code] ......
View 7 Replies
View Related
Sep 2, 2013
I want to be able to point my where clause to a specific record in a field when I double click on the record.
At present when I doubleClick it is defaulting to the Field but not the specific record.
View 14 Replies
View Related
Mar 12, 2013
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.
PIDM | regsYear | hc_Year
52 | 2009 | 1
52 | 2010 | 1
201 | 2007 | 1
201 | 2007 | 0
201 | 2007 | 0
201 | 2008 | 1
View 6 Replies
View Related
Nov 27, 2012
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 want to return the month in <mmm yyyy> format.
View 2 Replies
View Related
Dec 23, 2006
I would like to make a join between two field in 2 different tables:
1.) Table with number field for Purchase Order number
2.) Table with text field for customers PO ref (where normally above PO number is entered)
When I link the both I get an error/warning message that the "types in the expression don't match"
Can anyone help me to overcome this problem ??
Thanks
View 3 Replies
View Related
Jun 18, 2015
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.
View 10 Replies
View Related
Nov 14, 2007
I posted this in the wrong thread yesterday
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....
Help
Any comments would be appreciated
John
View 3 Replies
View Related
May 1, 2006
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!
View 5 Replies
View Related
Jun 10, 2007
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?
Cheers
View 4 Replies
View Related
Mar 8, 2005
When I insert this code:
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?
View 7 Replies
View Related
Dec 16, 2012
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.
View 5 Replies
View Related
Nov 4, 2011
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.
View 1 Replies
View Related
Oct 23, 2004
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.
Thanks, Edin
View 3 Replies
View Related
Jan 26, 2014
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?
View 1 Replies
View Related
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
Sep 6, 2014
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
[Code] ....
View 3 Replies
View Related
Sep 24, 2012
I am trying to make my primary key in my table a calculated field.
I want the field to be subject + catalog + topic no. The only problem is that there is not always a topic no.
How would I write this expression?
View 5 Replies
View Related
Mar 6, 2015
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.
View 4 Replies
View Related
Feb 24, 2013
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.
View 3 Replies
View Related
