I have a query that contains Invoices(usually 2 line items per invoice) Taxable and NonTaxable. I need to able to basically export it to excel on multiple worksheets when the Amount of unique invoice number reaches 15.
From all my scouring on the internet I can't find anything with this criteria.
Been hunting a round looking for some code that will output my access report to excel but more inportantly add a unique field from the report to the name of the file. example would be [FileName] & [ReferenceNo].xls.
View 2 Replies View RelatedHi 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
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
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
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 RelatedI'm building a sort of uberquery to do some calculations on a dataset for me, and I reached a point where I'm having some problems. I'm going to have to assemble the main query by doing a join on several subqueries.
Essentially, I have one table with the following fields (DrawingNo, DrawingRev) and another table with a great deal of data in it, but as far as this problem is concerned, it has (DrawingNo, DrawingRev, OrderNo, SPOOL). I have several tests that have to be performed.
The first table contains a master listing of al drawings we are supposed to have, the second contains information related to procesing the drawings. There is one row in the second table for each piece to be made from each drawing.
The first test is to verify that no gievn revision of any drawing has been split across more than one OrderNo. Essentially, I Count(OrderNo) and group by DrawingNo and DrawingRev, so that there is one line in the query for each drawing/revision pair, and a count of the number of OrderNos that meet those criteria.
The second test (and this is the one I have a problem with) involves taking a count of the number of unique values of DrawingRev that occur for a given DrawingNo. Something like SELECT Count(DISTINCT Drawingrev) FROM SecondTable GROUP BY DrawingNo should work, but Access doesn't appear to support "Count(Distinct". How do I get around this, and remember that any answer has to be able to be used as a subquery.
Hello all,
I am new to this forum. I have this problem where I need to sort the 4 books in the tables below according to the highest quantity of book sold.In this scenario,
Book C would be 1st in the table with a total quantity of 8.
How do I write a query in access to perform such operation. I have to use DISTINCT to get each book and also count the quantity(e.g. Book C:5+8).
But it seems that I can't use Count and distinct together in Access.
Title SalesNo Quantity
BookA22
BookB 31
BookC 45
BookC 53
BookD 6 1
Hope u all can help!
I have a query I have 4 fields:
E.G.
Field1 Field2 Field3 Field4
C 20 25 145541
C 40 20 145541
C 10 10 145540
D 20 20 145540
D 10 10 145538
I need to obtain:
C 70 55 2
D 30 30 2
I need to group Field1 and sum Field 3 and field3 and count the UNIQUE ID in field 4.
Hello, guys. First of all, may all of you have a very cool New Year 2007!
In my MS Access database i have 2 tables: Table1 and Table2. These are related by means of the fields Field1 (Primary key of Table1) and Field2 (Secondary key of Table2). The relation is 1 to Many.
I pretend to count the rows from the Table2 with different Field2 values. Thus, the following query would be an ideal solution:
SELECT COUNT(DISTINCT Field2)
FROM Table2
Unfortunately, the DISTINCT clause is not admitted within COUNT parenthesis. So, what i came up with is:
SELECT COUNT(*)
FROM Table1
WHERE Field1 IN (SELECT DISTINCT Field2 FROM Table2)
It works, but it takes too long to run the main query, where i embedded several subqueries like the one above.
Is there any other way to get the same result reducing the response time?
Thanks for your attention in advance.
Extended Cost] and salestran.[Extended Price] for each salestran.[SKU Code/Number], Count the number of unique customers, salestran.[Customer Code/Number], per SKU for any transactions equal to or after salestran.[Transaction Date] 11/1/2014.
Problem is, doing a regular count on the Customer field returns an incorrect value. It counts the number of times the sku appears within the given date range, ie 6 transactions with 2 customers, my count says 6. I need a distinct count on the customer, for the above example I want to see 2. Here is the code I have so far which yields an error of "At most one record can be returned.." distinct count in my Select statement along with the other fields I want to see, ie Summary data and SKU.
SELECT Salestran.[SKU Code/Number], (SELECT COUNT(cd.[Customer Code/Number]) AS Count FROM (SELECT Distinct [SKU Code/Number], [Customer Code/Number] FROM Salestran) as cd GROUP BY cd.[SKU Code/Number]) AS [Number of Customers]
FROM Salestran
WHERE (((Salestran.[Transaction Date])>=#11/1/2014#))
GROUP BY Salestran.[SKU Code/Number];
I have a table with the following values.
EMPLLOYEE_ID
JOB
PAY_DATE
LOCATION
Most employees have 10 to 15 rows. I only want to pull ALL rows for employees ONLY if there is a distinct count from DATE_PAID greater than 1. There are cases where an employee might have two pay checks generarted for the same PAY_DATE.
I would like to count distinct values on a control in the footer of a search form. How to accomplish it.
View 2 Replies View RelatedI have the following SQL which returns rows of distinct numbers that are calculated from a field.
SELECT Distinct (Left([ProjectID],4)) AS NumberOfCalls
FROM tblProject;
ProjectID looks something like this : 1307-IND-001 and NumberOfCalls looks like : 1307
I need to count the number of unique "NumberOfCalls" entries that there are in a list of about 50 rows. Currently the SQL returns a list of numbers like this:
1307
1311
1401
etc...
I just want NumberOfCalls to show "3". Is there an easy way to do this?
I'm trying to write a query to get a count of Volunteers under a certain Job Code for a given year - problem comes in that a single Volunteer may record hours multiple times under a Job Code in a given Fiscal Year. I can't seem to get "Unique Values" to work. From my research it looks like I need a two-part query but (as a newbie) I'm not quite sure how to write that. I have two queries, one that works and one that doesn't.
This one counts total amount of hours volunteered under a given Job Code, it works:
Code:
SELECT tblHoursWorked.FiscalYear, tblHoursWorked.JobCodeLookup, Sum(tblHoursWorked.HoursWorked) AS [Sum Of HoursWorked]
FROM tblHoursWorked
GROUP BY tblHoursWorked.FiscalYear, tblHoursWorked.JobCodeLookup;
HAVING (((tblHoursWorked.FiscalYear)=[Enter Year:]));
This one attempts to count number of Volunteers that worked under each Job Code in a given year - it instead counts number of entries under that job code. What I think I need to do is count unique instances of the NamesIDFK, but I can't seem to get that to work.
Code:
SELECT DISTINCT tblHoursWorked.[JobCodeLookup]
FROM tblHoursWorked
GROUP BY tblHoursWorked.[JobCodeLookup];
1) correcting my second query and 2) putting them into one query so I can use them in a report.
SELECT qryHoursReq.VolunteerId, qryHoursReq.Name, qryHoursReq.Program, Sum(qryHoursReq.Total_Time) AS SumOfTotal_Time, qryHoursReq.S_date
FROM qryHoursReq
GROUP BY qryHoursReq.VolunteerId, qryHoursReq.Name, qryHoursReq.Program, qryHoursReq.S_date
HAVING (((qryHoursReq.S_date) Between [startdate] And [enddate]));
This is my query
I'm trying to get a count of distinct volunteerID in an unbound textbox on report.
If I use =Count(*) I get 2 when I put in date parameters and it groups by program
This is kind of correct, but, these 2 are the same person, she had different days she participated.
What code can I use for only get a count of 1
FOrgot to mention, when I use the DCount with numerous examples, I get #Error
I have 2 tables linked with a 1 to many relationship by NAME. Tables look like this....
TBL-STORE
NAME ADDRESS
Walmart 12 Nowhere
Target 14 Somewhere
Lowes 10 Anywhere
Sears 16 Nowhere
TBL-PURCHASES
NAME [NUMBER OF ITEMS] [PURCHASE DATE]
Walmart 4 1/4/14
Walmart 2 1/5/14
Target 0 1/5/14
Lowes 3 1/5/14
Sears 1 1/5/14
Sears -1 (returned to store) 1/6/14
I want a DISTINCT COUNT of [NAME] if Total[NUMBER OF ITEMS]>0, so the correct number will be 2 in the example.
This is one way I've tried to write the SQL, but I keep getting an error...
SELECT Count([TBL-STORE].[NAME])
FROM [TBL-STORE] INNER JOIN [TBL-PURCHASES] ON [TBL-STORE].[NAME] = [TBL-PURCHASES].[NAME]
WHERE ([TBL-PURCHASES].[NUMBER OF ITEMS]>1)
HAVING ([TBL-STORE].[NAME])="DISTINCT");
I also tried an Count(IiF( and can't get that to work either..
I am trying to get a count of the unique customers in an access 2010 database
After some research it seems i should be using
SELECT Count(Distinct [Customer]) FROM [tblMain];
But when i use this i get
Syntax error (missing operator) in query expression 'Count(Distinct Customer)'.
I have tried leaving out the square brackets but this does not work....
I have a form where a user reviews information input by another user, once they have done this they sign it off by selecting their name from a drop down list. On this form there is a scrolling message which tells the user how many un-signed entries there are. This works fine until there are 0 (zero) entries to be signed off.
The scrolling message is linked to a count query which basically counts any records that does not have a name entered in the required field. I have done this with ' Not Like "*" '
Like I say it works when there is one or more entries to count, but as soon as there are none the query does not output a zero it is just blank. This is the problem.
I need some sort of statement to say if there are no matching records please display a zero.
Any help would be much appreciated!
Hi Have scoured the forum and have found people with a similar problem, but I just can't seem to get my head round this.
Basically I have a table with various fields, one of these fields is a sign off field where the user enters their name once the record has been reviewed.
I want to count all the records that do not have a name entered in this particular field thus are null/blank. I then want this figure to appear on a form thus representing the total number of records still to be reviewed.
I have tried doing this with various methods with no joy.
Any help would be much appreciated.
Hi
If I open a query in Access and uses the "Export to Excel" button everything works out just fine.
But when I want to make the export with a button using the code:
DoCmd.OutputTo acOutputQuery, "qryToExcel", acFormatXLS
I only get 255 chars from the PM-fields to excel.
Can anyone help me solve this problem?
I also want to make some filtering, with code string, to the query before I export it, could use some suggestions there also.
Thanks in advance
I am having a problem getting Access to output to an excel file.
I want to take a form that I have that has 20 or so records with 6 or 7 fields for each and put it into an excel file that is formated the way I need everythign to look.
I also need it to make the file name such as CCCAAAMMDDYYYY (3 letter company abbreviation,3 letter initials and then todays date) I am thinking that the company abbreviation will be pulled from a query, then initials will be a form that opens, and the date can be pulled from access' date function.
I tried using vb code and doing an output to comand but that did not work.
I am confused someone please help if possible.
I have written some code that will output to a spreadsheet in a given location:
how can I rework this code so that the excel output displays on screen rather than saving to a specified location:
Code Written:
Private Sub outputToExcel_Click()
DoCmd.TransferSpreadsheet acExport, acSpreadsheetTypeExcel9, "C4C Period Final", "\uk.michaelpage.localdfsGroupDataNSCHElite Database ReportsGross Cash For Car.xls"
MsgBox "C4C Period Final Exported"
End Sub
Windows XP Pro/Office 2000
I was able to output to excel before fine, now all of a sudden I cannot. I keep getting a message when I go Tools>Office Links>Analyze with MS Excel:
"Cannot access "xxxvac_Inv_DBQuery.xls"
It's wierd. It won't output the data, but it still creates the spreadsheet file.
Any Idea's?
Thanks to all in advance, your help is greatly appreciated! :(
Hello first of all I have learned a lot from this forum so thanks you all who post questions & solutions. Ok I have a table which I use to query info and export to excel. My question is there a way to add sum totals to a few of the columns once it is in excel. I was thinking it would be easier to do a sum statement then just add it as the bottom row while it was exporting. Or would it be easier to do it once it is in excel. Here is my code:
Private Sub Command63_Click()
Dim strSQL As String, strOrder As String, strWhere As String
Dim dbNm As Database
Dim qryDef As QueryDef
Set dbNm = CurrentDb()
strSQL = "SELECT tblCONSOLIDATED.ACCOUNT1, tblCONSOLIDATED.COMPANY_NAME, tblCONSOLIDATED.CUSTOMER_TYPE, tblCONSOLIDATED.ADDRESS1, tblCONSOLIDATED.ADDRESS2, tblCONSOLIDATED.CITY, tblCONSOLIDATED.STATE, tblCONSOLIDATED.ZIP, tblCONSOLIDATED.CONTACT_NAME, tblCONSOLIDATED.E_MAIL, tblCONSOLIDATED.TELEPHONE, tblCONSOLIDATED.FAX, tblCONSOLIDATED.REP_NUMBER, tblCONSOLIDATED.PROMOCODE, tblCONSOLIDATED.SALESCODE, tblCONSOLIDATED.CURRENT_YTD, tblCONSOLIDATED.PRIOR_YTD, tblCONSOLIDATED.PRIOR_TOTAL, tblCONSOLIDATED.YEAR2_TOTAL, tblCONSOLIDATED.YEAR3_TOTAL, tblCONSOLIDATED.YEAR4_TOTAL " & _
"FROM tblCONSOLIDATED"
strWhere = "WHERE"
strOrder = "ORDER BY CURRENT_YTD DESC"
If Not IsNull(Me.txtCSONME) Then
strWhere = strWhere & " (tblCONSOLIDATED.COMPANY_NAME) Like '*" & Me.txtCSONME & "*' AND"
End If
If Not IsNull(Me.txtCSOSLD) Then
strWhere = strWhere & " (tblCONSOLIDATED.ACCOUNT1) Like '*" & Me.txtCSOSLD & "*' AND"
End If
If Not IsNull(Me.txtCSOARN) Then
strWhere = strWhere & " (tblCONSOLIDATED.CONTACT_NAME) Like '*" & Me.txtCSOARN & "*' AND"
End If
If Not IsNull(Me.txtCSOCTY) Then
strWhere = strWhere & " (tblCONSOLIDATED.CITY) Like '*" & Me.txtCSOCTY & "*' AND"
End If
If Not IsNull(Me.txtCSOST) Then
strWhere = strWhere & " (tblCONSOLIDATED.STATE) Like '*" & Me.txtCSOST & "*' AND"
End If
If Not IsNull(Me.txtCSOZIP) Then
strWhere = strWhere & " (tblCONSOLIDATED.ZIP) Like '*" & Me.txtCSOZIP & "*' AND"
End If
If Not IsNull(Me.txtCSOSSM) Then
strWhere = strWhere & " (tblCONSOLIDATED.REP_NUMBER) Like '*" & Me.txtCSOSSM & "*' AND"
End If
If Not IsNull(Me.txtCSOM1) Then
strWhere = strWhere & " (tblCONSOLIDATED.PROMOCODE) Like '*" & Me.txtCSOM1 & "*' AND"
End If
If Not IsNull(Me.txtSLCYYD1) Then
strWhere = strWhere & " (tblCONSOLIDATED.CURRENT_YTD) BETWEEN " & Me.txtSLCYYD1 & " And " & Me.txtSLCYYD2 & " AND"
End If
If Not IsNull(Me.txtSLLYYD1) Then
strWhere = strWhere & " (tblCONSOLIDATED.PRIOR_YTD) BETWEEN " & Me.txtSLLYYD1 & " And " & Me.txtSLLYYD2 & " AND"
End If
If Not IsNull(Me.txtSLPYR11) Then
strWhere = strWhere & " (tblCONSOLIDATED.PRIOR_TOTAL) BETWEEN " & Me.txtSLPYR11 & " And " & Me.txtSLPYR12 & " AND"
End If
If Not IsNull(Me.txtSLPYR21) Then
strWhere = strWhere & " (tblCONSOLIDATED.YEAR2_TOTAL) BETWEEN " & Me.txtSLPYR21 & " And " & Me.txtSLPYR22 & " AND"
End If
If Not IsNull(Me.txtSLPYR31) Then
strWhere = strWhere & " (tblCONSOLIDATED.YEAR3_TOTAL) BETWEEN " & Me.txtSLPYR31 & " And " & Me.txtSLPYR32 & " AND"
End If
If Not IsNull(Me.txtSLPYR41) Then
strWhere = strWhere & " (tblCONSOLIDATED.YEAR4_TOTAL) BETWEEN " & Me.txtSLPYR41 & " And " & Me.txtSLPYR42 & " AND"
End If
If (Me.PROSPECTBX) = True Then
strWhere = strWhere & " (tblCONSOLIDATED.CUSTOMER_TYPE) Like 'P' AND"
End If
If Not IsNull(Me.txtSLCLS) Then
strWhere = strWhere & " (tblCONSOLIDATED.SALESCODE) Like '*" & Me.txtSLCLS & "*' AND"
End If
If strWhere = "WHERE" Then
strWhere = ""
Else
strWhere = Trim(Left(strWhere, Len(strWhere) - Len("AND")))
End If
Set qryDef = dbNm.QueryDefs("qrySALESDATA")
qryDef.SQL = strSQL & " " & strWhere & "" & strOrder
DoCmd.OutputTo acQuery, "qrysalesdata", "MicrosoftExcel(*.xls)", "QUERY RESULTS.xls", True, ""
End Sub
Sorry for adding the whole thing but I'm not sure it should be done after "Query Results.xls" is written or during the select statement.
Thanks in advance for your help.
Bryan
Hi Guys,
I have 2 tables
tblXRFResults
ResultID
SampleName
Date
SampleDate
a few others not necessary in this
and tblXRFResultsConcentration
ResultID (many records linked to 1 record in tblXRFResults)
Concentration
CompoundName
What I need to set up is a query that will allow me to generate a report that will give an excel like format, with the column headings being the SampleName, followed by the CompoundName(s) from the other table and the "rows" will be corresponding sample name and concentrations.
I am sure this is pretty easy, but I am stumped!
Any help would be greatly appreciated.