Distinct Command Not Working
Oct 4, 2006
Hey,
Ok so I have this form that has two subforms on it (sf1 and sf2), these are run by one query called 'test'. Below screenshot of what i dont want happening;
Bugger.jpg (http://i5.photobucket.com/albums/y152/greyowlsl/bugger.jpg)
Now what i want is a distinct command for the 'Document Number' column so it cant be shown more than once. basicly i want it to look like this (http://i5.photobucket.com/albums/y152/greyowlsl/This.jpg)...
what do i do?
Thanks for your time.
,Leon
View Replies
ADVERTISEMENT
Jan 15, 2015
I have an Access 2010 database with two tables and two forms. The tables are Organizations and People. Similarly, the forms are Organizations Entry Form and PeopleEntryForm. The People are linked to the Organizations table. Several people can be linked to the same organization.On my Organizations EntryForm, I created a command button to duplicate a record using the wizard. It works fine.
I did exactly the same thing on the PeopleEntryForm, but instead of copying the record, it creates a new blank record. I don't get any error messages. Is my problem due to the fact that the People table is linked to the Organizations table?
View 13 Replies
View Related
Oct 19, 2005
I have a form that contains a subform.
The subform is bound to a table.
The form is based on a query but still writes directly to the appropriate table.
In the header of the form I have a series of command buttons - new record, navigation buttons, and buttons to open new forms. None of them work at all.
I checked each button to be sure the On Click Events still had the right Event procedure, and they do.
for example, the command button to open the Cases form has the following VBA (generated form the button wizard.):
Private Sub cmdOpenCases_Click()
On Error GoTo Err_cmdOpenCases_Click
Dim stDocName As String
Dim stLinkCriteria As String
stDocName = "frmCases"
DoCmd.OpenForm stDocName, , , stLinkCriteria
Exit_cmdOpenCases_Click:
Exit Sub
Err_cmdOpenCases_Click:
MsgBox Err.Description
Resume Exit_cmdOpenCases_Click
End Sub
When clicked, the button just blinks - nothing happens. I check to be sure the form isn't a popup and also closed it after clicking it to see if maybe frmCases had opened behind the current form for some reason. Nothing - it's not opening at all.
Any ideas?
View 1 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
Mar 24, 2007
I was trying to incorporate the changes suggested by a member of this forum into my Form. I opened the Form in Design View. I clicked the Command Button and placed the mouse at the insertion point. The command button also appeared. Thereafter, nothing happened. Earlier, whenever I placed a command button, it asked for action to be performed in relation to that command button. But now nothing happens and I don't know what to do. Please tell me how I can bring back the feature.
Thanks.
View 2 Replies
View Related
Jan 19, 2005
The scenario is... also apologies if this seems a lot. Simple really.
An edit button on a subform is to open a separate form for editing, and I obviously want to apply a where condition to display the relevant record. So I have
DoCmd.OpenForm "editReviewPEST", acNormal, , ReviewID=Me.ReviewID, acFormEdit, acWindowNormal, False
The Record Source for editReviewPEST uses an INNER JOIN...
SELECT Reviews.Year, Reviews.ReviewID, Reviews.LesseeID, Lessee.DateOfOrigSanction, Reviews.Exposure, Reviews.ExpoCurrency, Reviews.SentToCredit, Reviews.Approved, Reviews.Completed, Reviews.Comments, Reviews.LevelOfLastSanction, Reviews.CreditManager, Reviews.TypeOfReview, Reviews.DateOfLastCreditReview, Reviews.DateOfNextCreditReview FROM Lessee INNER JOIN Reviews ON Lessee.LesseeID = Reviews.LesseeID WHERE (((Reviews.LesseeID)=[Forms]![Reviews]![SelectLessee])) ORDER BY Reviews.ReviewID DESC;
So I get the form to open with all the records relvant to the lessee, but to filter it further to exact ReviewID, I thought I could include ReviewID=Me.ReviewID (an Autonumber PK) as the Where Condition to the DoCmd.OpenForm. Instead I get a form which looks like Add Record Form.
View 4 Replies
View Related
Jan 7, 2008
I just created my very first db... I used the wizzard to create it most of it, including command buttons that permit you to move from the one form to another form/report. I saved the db to a shared drive (two people total accessing/updating it) only to find that the command buttons don't work for her. I created the db in 2003, but she has 2007. If I upgrade to 2007 and convert the db, could this solve the problem? THANKS!!
View 5 Replies
View Related
Oct 19, 2014
I have created an accdb file and saved as an accde file. The accde file opens with a form with command buttons. But the commands buttons do not work with the accde file (they do work with the accdb file). How do I get them working.
View 7 Replies
View Related
Oct 21, 2013
I am in trouble with the shell ocmmand,i have a text box "ExcelPath Location" in which there is a path of excel file i am using below code to open the excel file but it gives an error:
Code:
Private Sub Command11_Click()
Dim str As String
str = ExcelPathLocation.Value
strPath = Dir(str)
Shell "excel.exe" & """" & strPath & """", vbNormalFocus
End Sub
the value of text box is
O:QA FilesQC ReportingPending ReviewB329129)419479_BoxPort_RAMANDEEP BRAR_(10192013.xlsm
it gives run time error : 53 file not found.
View 3 Replies
View Related
Mar 17, 2014
I have two command buttons in a subform "sbfScoutRegDetails", one that launches a report and the other attaches the report pdf to an email, associated with the record ID when clicked.
Both of these buttons work fine when just the subform itself is open, but when viewing it in its main form, I get a window saying 'enter parameter value' for "Forms!sbfScoutRegDetails!ID" (this is the WHERE condition in the macro).The report then opens with all the record information blank.The full WHERE condition
Code:
[ID]=[Forms]![sbfScoutRegDetails]![ID]
Not sure if it's because of using a continuous form as a subform? I need the user to be able to view/print or email a contract to each group/contact that signs up.
View 5 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
Dec 17, 2013
I am rewriting an old Access 2003 database in Access 2010. When creating new command buttons, the current theme gives them a default appearance. I need to apply this appearance to old command buttons. I know there is a way to select the default button and apply its properties to others quickly. I have done it before but didn't write the process down .
View 2 Replies
View Related
Aug 28, 2005
I want to count the number of distinct dates in a table. Ie - So I know there were 80 requests over 5 days.
The syntax I imagine is something like:
myText = Dlookup("count(DISTINCT myDate)", "myTable")
Can somebody please help.
Thank you in advance
View 3 Replies
View Related
Sep 13, 2006
Hi,
I have a query with in excess of 20 fields. One of the fields is [POLICY NUMBER]. I want to use DISTINCT to only show rows of data where the Policy number is unique.
If I put DISTINCT after SELECT the query will only include results where the combination of values from all the fields are unique and I dont want this.
How would I go about this.
Any help would be appreciated.
Regards
Matt
View 8 Replies
View Related
Feb 22, 2007
I am running a query in a combo box but for some reason the following SQL statement does not give me the result I am after. Which is to only displaying the distinct records. It continues to display the multiple occurrences of the records. I also tried distinctrow with no better results.....
View 4 Replies
View Related
Jul 29, 2005
Hi Everyone!,
I have a table with the follwing;
ShopID, CustomerID, Month, Item_ref, Product Category
There are about one thousand shops.
The CustomerID field is only unique per shop.
I.e. all transactions for customer ID '002' in shop 1 will be for the same customer, but customer ID '002' could appear in another shop and will be a different customer,
I want to be able to bring back a list of distinct customers, the number of items they have had and the product categories.
Has anyone got any ideas how I do this? I presume I would need to do a count distinct or something??
thanks!,
Sasha
View 2 Replies
View Related
Aug 9, 2005
I need to select two fields in a select DISTINCT query
Select tbl_Aplaws.AplawID, DISTINCT tbl_Aplaws.Level2 FROM tbl_Aplaws WHERE tbl_Aplaws.Level1 = 'Business' ORDER BY tbl_Aplaws.Level2;
anyone know how to make this work?
View 1 Replies
View Related
Sep 13, 2005
Hello,
I have a strange problem: one of my comboboxe is looking up a column in one of my table. Some of the record can be duplicated so I decided to change SELECT to SELECT DISTINCT in my SQL for the row source but it doesn't seem to change anything. Is anybody's got an idea why?
View 1 Replies
View Related
Jan 28, 2007
I have a table like this in MS Access database:
IDDateCustomerIDInvoiceNoItemNoAmount
101/01/2007A1AAA1
201/01/2007A1BBB2
301/01/2007A2CCC3
601/01/2007B5AAA6
701/01/2007A6BBB7
801/01/2007B5BBB8
I want to get disctinct count of InvoiceNo while summing up Amount by Date and CustomerID in one query. Firstly, I tried to use correlated query to count unique records but it failed.
SELECT A.Date, A.CustomerID, (SELECT COUNT(B.InvoiceNo) FROM Table1 B WHERE B.Date = A.Date AND B.CustomerID = A.CustomerID ) AS CountOfDistInvNo, Sum(A.Amount) AS SumOfAmount
FROM Table1 A
GROUP BY A.Date, A.CustomerID;
The result of Count is not a unique count. Can anyone tell me what's wrong with this correlated query? :confused:
After hours of googling on internet, the solution I got is equivalent to querying on top of the resulting table from another query.
My final query is
SELECT B.Date, B.CustomerID, Count(B.InvoiceNo) AS CountOfInvoiceNo, Sum(B.SumOfAmount) AS SumOfAmount
FROM (SELECT A.Date, A.CustomerID, A.InvoiceNo, SUM(A.Amount) AS SumOfAmount FROM Table1 A GROUP BY A.Date, A.CustomerID, A.InvoiceNo) AS B
GROUP BY B.Date, B.CustomerID;
Is there a better solution than this in Access? :)
Thanks,
Huyeote
View 5 Replies
View Related
Jun 18, 2007
Tried several suggestions in the forum but haven't found one to match my needs.
I have one table, Jupiter and need to use this query
SELECT Jupiter.codename AS Build, Count(Jupiter.tooling_ind) AS [# Needing Tool], Count(IIf([tool_req_type]='OR',([requisition_no]))) AS [# Tooled], [# Tooled]/[# Needing Tool] AS [% Tooled], Jupiter.tooling_ind
FROM Jupiter
GROUP BY Jupiter.codename, Jupiter.tooling_ind
HAVING (((Jupiter.tooling_ind)="Y"));
For each "codename", there is an attached "part_no" which can appear under a codename several times. The problem is that I need to count only one instance of the "part_no" for each "codename".
So, my query looks like this:
Build # Needing Tool #Tooled
LP1 769 192
The # Needing Tool is counting every record that, say pn 10592 shows up in and I need it to be counted only once in each "Build"
I did try this suggestion I found with no results.
SELECT Jupiter.codename AS Build, Count(Jupiter.tooling_ind) AS [# Needing Tool], Count(IIf([tool_req_type]='OR',([requisition_no]))) AS [# Tooled], [# Tooled]/[# Needing Tool] AS [% Tooled], Jupiter.tooling_ind
FROM (SELECT DISTINCT Jupiter.part_no FROM Jupiter)Jupiter
GROUP BY Jupiter.codename, Jupiter.tooling_ind
HAVING (((Jupiter.tooling_ind)="Y"));
Is there a way to do this somehow?
Thanks!
Toni
View 3 Replies
View Related
Jun 19, 2007
I have a problem using distinct or distinctrow in one of my query. The result shown reflects all records instead of distinct records. May I know what may be the cause of the problem?
Query:
SELECT DISTINCT Assets.*, Depreciation.DepreciationDate, Depreciation.DepreciationAmount
FROM Assets LEFT JOIN Depreciation ON Assets.AssetID=Depreciation.AssetID;
Thanks in advance!
View 2 Replies
View Related
Jul 6, 2007
I've searched on google for count distinct help but I had no luck. I'm counting 2 different columns and when the query is run the counts in both columns appear exactly the same. Also, I need a SUM distinct (if possible) to sum only where it is being counted in 'CountOfCar_Nr'. Here is my SQL....
View 3 Replies
View Related
Jul 12, 2007
Hello
I have a little problem with a query.
There are some tables, one is n:m with the main table.
main_table n:m a_other_table
Now when I run the query, it displays all relations. But I want only the first.
I tried TOP 1 but, this only show 1 record, DISTINCT will not work, I select * from the query.
Now my question, is it possible to DISTINCT only one argument?
I have id's, one id should only appears one time.
Select DISTINCT idAny, * will not work.
Any ideas?
View 5 Replies
View Related
Aug 31, 2007
All,For some reason, I cannot seem to get a result set with unique records using the DISTINCTROW predicate in Access 2007. However, if I simply use the DISTINCT word (even if I am querying more than one field in a table), the result set gives unique records. This seems backwards....it's certainly not right. I know that the DISTINCTROW predicate does what it's supposed to do in earlier versions of the program, because I use them as well. Does anyone have any ideas on this??Has anyone else experienced this?? Thanks!!
View 9 Replies
View Related
Nov 15, 2007
Ok , I will try and explain this as best I can.
I have a database and would like to run a DISTINCT query ( which I can do no problems ) I would also like to count the number of records at the same time. Say for example my database looked like this
Name Fruit
Adam Apple
Carrie Apple
Hollie Apple
Joe Apple
Adam Orange
Carrie Orange
Hollie Orange
Joe Orange
What I would like it to do is Select the distinct names and list them once ( I can do this bit )
Adam
Carrie
Hollie
Joe
I would then like to count the number of records found for each name and put it next to the relevant name
for example Adam (2) (one apple and one orange)
How would I write the actual query
I hope this makes sense.
Thanks in advance
Adam
PS , great site btw
View 10 Replies
View Related
Feb 15, 2008
Hey all
I have data in the table.
(various IDs - in example below just 1)
Each ID has date and status.
I need to pull each ID only ones
for MAX date
If there are 2 like that (with different statuses) i need the one with lowest importance. Any advise ?
StatusLogIDDateID DateImportance
ssss02/04/200802/04/2008ghj6
ssss02/04/200802/04/2008ghj6
tttt02/04/200802/04/2008ghj5
tttt02/04/200802/04/2008ghj5
rrrrrr02/07/200802/07/2008ghj4
rrrrrr02/07/200802/07/2008ghj4
ssss02/08/200802/08/2008ghj6
ssss02/08/200802/08/2008ghj6
ssss02/11/200802/11/2008ghj6
ssss02/11/200802/11/2008ghj6
tttt02/11/200802/11/2008ghj5
tttt02/11/200802/11/2008ghj5
View 3 Replies
View Related
