Modules & VBA :: Construct A Query With Number Of DCounts For Summary Report
Dec 16, 2014
I am trying to construct a query with a number of DCounts for a summary report, and each of those counts is to have more than one criteria.So far, the code below is an example of the code, which returns #Error in five rows
Code:
DCount("[ID]","Attendance","[Absent]= True & " And [Date]=" Between [Forms]![DateSelect]![txtStartDate] And [Forms]![DateSelect]![txtEndDate]")
I have a feeling that it is the criteria for the date that I have got wrong, but I cannot see the wood for the trees at the moment.If it makes any difference, all of the DCounts will refer to one table and will all be within the same date range.
View Replies
ADVERTISEMENT
Sep 10, 2013
I am trying to set the following variable that will be used to create a PDF file in an already existing folder and name the file WorkorderIDddmmyyyy-hhnnss.pdf
The following variable setting creates the filenameddmmyyyy-hhnnssWorkorderID.pdf.
mFilename = "C:RPR AccessPDF Reports Emailed" & Format(Now(), "ddmmyyyy-hhnnss") & WorkorderID & ".pdf"
DoCmd.OutputTo acOutputReport, mReportName, acFormatPDF, mFilename, True
I just can't figure out how to construct it correctly.
Also, is there a way to dynamically create a folder if the folder doesn't already exist? I currently hard code the folder name but would really like to create the folder name based upon some other variable.
View 8 Replies
View Related
Mar 29, 2007
how do i use conditional statement in sql? im using access 2003 as frontend and backend.
i would like to count the number of records in a table (ex. ENROLL) where there is only 1 instance of any value in a field (ex. STUDENT_NO) plus another condition where a given value (ex. 1) should exist in another field (ex. SECTION).
example
Table name: ENROLL
STUDENT_NO SECTION FEE
16 ----------- 1 ------ 75
16 ----------- 2 ------ 75
17 ----------- 1 ------ 100
18 ----------- 2 ------ 125
19 ----------- 1 ------ 75
19 ----------- 2 ------ 75
19 ----------- 3 ------ 75
20 ----------- 1 ------ 150
Parameters:
COUNT
The number of records (count) in the table where there is only one instance of the STUDENT_NO value and the corresponding SECTION value is 1
VALUE
The sum of the FEE values in the table where there is only one instance of the STUDENT_NO value and the corresponding SECTION value is 1
The answer should come out like this:
count = 2
value = 250
Can anybody please help me to construct the SQL statement?
Thanks in advance!
View 4 Replies
View Related
Nov 27, 2014
I have a form with several fields and combo boxes on which the user will select different criteria to generate a report.The default value for each of these fields/cbo's is "*", so if the user changes nothing from the default, I execute a basic "SELECT *" query.
If however the user enters some value (selects a specific date range, customer number, salesman,etc), I would like to 'easily' modify my query to accommodate the entered search criteria.Trying to construct SQL when there are many search options like this is difficult. I took a stab at using QueryDefs but I'm struggling to find example VBA code as an example.
View 1 Replies
View Related
Aug 23, 2004
Lets say I have table with the following information:
OrderNo. ProductId Count1 Count2
01 AA 2 0
01 AB 0 1
01 AC 1 0
01 AD 0 3
02 AD 0 2
02 AE 4 0
02 AB 0 1
.
.
.
A count in Count1 means 0 in Count2 and vice-versa
How do I create a report that looks like this:
OrdeeNo. SumCount1/SumCount2
01 (3) / (4)
02 (4) / (1)
.
.
---------------------------
Totals (7) / (5)
Thank you
View 1 Replies
View Related
Sep 13, 2007
Hello all,
I have a temporary table with Yes/No fields (F101,F102,F103,....etc).
In another table i have a field called ProductCode with values (101,102,103,....etc)
Is it possible to append(or update) the records of the temp table with criteria on "F" &ProductCode field? (For example if ProductCode=101 then F101 sets to yes)
Thank you in advance.
View 4 Replies
View Related
May 19, 2015
How can I generate a report that contains multiple summary total by year. I run the query to give me selected time period(s) for my report. For example: I want to have the summary for the date ranges from the year 1994 to 2001, and then 2002, 2003, 2004 etc. I'm having difficulty for the first summary total which is the date range from 1994 to 2001.
View 3 Replies
View Related
Mar 12, 2013
I am trying to create a report or form (not sure which is most suitable) which when opened will show a summary of my data.
My data is essentially a table of timecards, with various linked tables which together enable time working on a lot of different projects to be recorded and reported on, and the value of the work can also be reported on.
In other words it's a bog standard time-recording system.
I can generate a number of reports, for example a report showing all projects with unbilled time and the value of that time.
What I want to do is produce a Key Performance Indicator (KPI) report/page showing total time worked in the last 7 days, month, quarter, year and the annualised amount of the first three of those.
Also I want to show the value of each of those in billing terms, e.g. what was the time recorded in the last quarter worth, and if that continued all year what would be the annual value.
I have created summary (total) queries which return all of these numbers. Essentially each query has a number for the period and a number for the same figure annualised.
I am trying to get all of these figures onto one page.
If I create a report based on one of my queries (by opening the query and clicking Create/Report) Access generates a lovely report showing my two figures generated by that query.
My problem is that I cannot get all of the different figures based on the different queries onto one report (or form).
Access will only allow me to show the figures that come out of the data source for the report or form. I cannot find a way to have multiple data sources.
I have tried creating text boxes where the data source is a different query. I dial up the query and the value, but all I get in the box is "#Name?".
I guess people must make this kind of KPI report or form all of the time. I read about dashboards and the like and see pictures of nice-looking Access pages containing all sorts of summary data, but nowhere can I find any description as to how to create such a page.
View 1 Replies
View Related
Jun 3, 2014
Is it possible to pull the data real-time? I have this access database, and I need to pull the data every time it was updated.
Process name is given, I need to pull the time according to the process name and the volume,
Attached files is the output. The output should be in a form.
View 1 Replies
View Related
Jan 27, 2014
I have a report that is a summary of multiple records in it. What I have next to the record on the report is a button.I have report that are labeled:
rptIncident Summary
rptIncident Report
and a button labeled
"open report"
I would like to click on the button and then it open the rptIncident Report with all the information, not just the summary. I will put a picture so you know what I am trying to accomplish.Is this a simple Onclick event with a where condition or what? Or does this go beyond to having something to do with VBA?
View 14 Replies
View Related
Jan 9, 2006
Hi,
I have a problem with form design,
I want a form like in Northwind sample database: Summary of Sales by Year Report.
It use Sorting and Grouping for Footer that I can't find it in Form design.
Is it any other way to do it in Form design so I can get the same result like in Report design?.
The reason why I want it, because I want to control the size.
thankyou in advance for your help.
Gunawan.
View 2 Replies
View Related
Sep 20, 2014
creating sequence number in a query using a starting number. For example i want to use the starting sequence number as 5
---SEQNUM---------COLOR-----------
-----5------------------blue--------------
-----6------------------red---------------
-----7------------------orange-----------
-----8------------------black-------------
View 2 Replies
View Related
Aug 29, 2006
I'm trying to build a database that will calculate the price of meals. I have a table 'ingredients' that has the ingredients, measurements and prices. As prices can change regularly I assumed that inividual recipes should be queries that calculate the total cost on the fly. My problem is that I regularly need to print out a summary of costings that includes total costs for each recipe.
From reading various posts I have formed the opinion that I REALLY shouldn't store calculated results in a table.
Is there another way to get my summary? Or will I have to resort to being an access pariah?:(
View 2 Replies
View Related
Dec 11, 2006
Hi Guys!
It's me again.
From one of my tables, custinfo, i want to generate a summary of records. What I mean is I want to count the total number of records, count those who doesn't have address entries, count those who doesn't have credit records. So in presentation, I want to have this:
Field No Records With Records Total
Address 10 256 266
CreditREc 5 261 266
Is it possible?
Thanks!
View 3 Replies
View Related
Feb 27, 2006
Hi All,
I have a approximately 70 queries in my database. I would like to be able to run a query which would run all of the queries and output the number of records for each query. Ideally, these would then be written to a table so that the user could then just read the values from the table for the latest results, rathe r than have to execute the whole thing again.
The user may wish to select which queries to run. I was thinking that I would need a table as follows called say tblQueryResults:
QueryToRun - Yes/No - DateRun - NumberOfRecords
Query1 - Yes - -
Query2 - Yes - -
...
...
...
Query70 - Yes - -
So my first dilema is to work out how to run all the chosen queries that the user wishes to run. The user will probably have all 70 ticked as Yes initially.
Should I run this from VB code with a whole lot of VB statements. I would like to loop through the whole table and collect a list of all the queries to run based on a positive Yes for some or all of the queries. The results must then go and be written into the same table under the date it was run and the number of records that was found for each query.
The whole reason for doing this is that queries which return no records need not be run by the user - saving the user time etc. I appreciate that this query will take a considerable amount of time - given that it could be as many as 70 being run one after another.
Thanks,
Evan
View 2 Replies
View Related
Jun 21, 2007
Hi..(",o) anyone can HELP?
I'm currently using MS Access 2000...
May i know how can I translate the following TABLE
Doc# Team# Status
D1 TA Done
D1 TB WIP
D1 TC WIP
D1 TD Done
D2 TA Done
D2 TB Done
D2 TC Done
D2 TD WIP
to a QUERY reflecting in the following format?
TA TB TC TD
D1 Done WIP WIP Done
D2 Done Done Done WIP
Your help will be greatly appreciated!
View 2 Replies
View Related
Dec 5, 2013
I have three tables with data.
Table1 is data for meals.
Table2 is data for room costs.
Table3 is data for payments made.
Each of these tables has a foreign key for EventID.I'm trying to produce a report that will show, for each EventID:
The total billed (which is meals + rooms)
The total paid (from Table3)
The balance due (the difference from the two above).
Do I have to create summary queries for each table?
View 2 Replies
View Related
Apr 13, 2007
I'm working in the petro-chem and industrial service industries now, and am finding there are relationships I haven't had much exposure to in the past.
Can someone please help me with constructing the tables and relationships needed here?
This is something I should probably know, yet I've never created junction tables which must take into account nested data. I have created a relationship which works, but I feel it could be done in a simpler fashion.
By the way, I cannot find Pat Hartman's many-to-many sample db. It is either missing, or the restrictions on word length in Search just aren't letting me find it.
Table: WorkOrder is the primary table I'm working with. I need to store 4
pieces of information for every WorkOrderID, two of which have subs.
Here's a picture of the data I need to collect:
View 4 Replies
View Related
Mar 21, 2013
i intially, I wish to have attendance stats for 3 groups of people over 2 days in a report
i have the two queries for the raw data, but wish to combine into a report that will ultimately become a "dashboard" report for half a dozen queries., and can only get one or the other to work do i need to combine the queries into a summary query of sorts?
View 1 Replies
View Related
Feb 18, 2014
I need to add a column to a crosstab query that counts all of the occurrences of "P" in a particular row in the crosstab query. Basically "P" stands for "Present" and I need to know how many days each employee was present for his/her shift. The SQL for the crosstab query is as follows:
PARAMETERS [Forms]![frmDashboardReports]![txtStartDateAndTime] DateTime, [Forms]![frmDashboardReports]![txtEndDateAndTime] DateTime;
TRANSFORM First(tblAttendance.AttendanceCode) AS FirstOfAttendanceCode
SELECT tblAttendance.EmployeeName
FROM tblAttendance
WHERE (((tblAttendance.AttendanceDate)>=[Forms]![frmDashboardReports]![txtStartDateAndTime] And (tblAttendance.AttendanceDate)<=[Forms]![frmDashboardReports]![txtEndDateAndTime]))
GROUP BY tblAttendance.EmployeeName
PIVOT Format([AttendanceDate],"Short Date");
This returns an "AttendanceCode" against each employee against each day in the specified time period. I just need to be able to "sum" those codes in a column.
View 3 Replies
View Related
Aug 2, 2013
I have a query run that gives me a list of records that I view on a continuos form. What I want is to press a button and run a macro/Append Query to add a Single Summary record to another table.
For example my query spits out this data
Part # Quantity Serial Number
GO2 1 123
GO2 2 456
GO2 2 789
What I'm looking to get is
Part Number Total Quantity Serial Number 1 Serial Number 2 ..
GO2 5 123 456
I'm stuck on a couple of things.
1. Getting a new single row to append.
2. Getting Serial Numbers from several records to save on to a single record.
View 4 Replies
View Related
Nov 4, 2013
I have a form where we fill in information for supply of equipment to employees.
Each item must be signed for on a printed report.
I am encountering problems trying to create enough rows in my report detail for each signature of the items supplied.
For example, on the form I will select the "equipment" - 4 hats supplied and 3 boots. On the report I want the equipment set as the group and the detail to be a number or rows which equals the number of selected items. therefore under the Hats group heading I want 4 blank rows which are made up of 3 text boxes - Print Name, Signature & Date and another group heading for boots but with 3 lines.
View 11 Replies
View Related
Jul 10, 2006
I've created a report and report has the same number of pages as the number of records that it's displaying.
If there are two records, the report has 4 pages...the first 2 are the actual report and then the other 2 are a copy. If there are 3 records, the report would have 3 copies (...6 pages).
Any idea how to change this so that I have only one copy of the report?
View 1 Replies
View Related
Aug 8, 2013
I am trying to count rows in the result of one of the queries and I am having a bit of trouble getting it going.
The current code - this is executed as on-click event when clicked on List Box feed with query below.
What I want to add is simple if that when number of rows produced by the querry is 1 it will enable a picture item in the different part of the form, however it does not want to count the rows for me.
Code:
Private Sub search_items_Click()
Me.OBSFullFilledOrdersHolder.Enabled = True
mysql = "SELECT orders.[order id] , STUDENTS.[first name]& ' ' & students.[surname] AS Name, students.[contact name] AS ContactName , ORDERS.[Online Bookshelf order] AS OBS , STUDENTS.[Delivery Address 1], STUDENTS.[Delivery Address 2], STUDENTS.[Delivery Address 3], STUDENTS.[Delivery Address 4]"
[Code] ....
The query itself works when tested but when used in code with DCount function will return error: Run-Time 2471 the expression you entered as query parameter prouced this error
'[Forms]![FULLFILL ORDERS]![search items].[Column(0)]'.
View 5 Replies
View Related
Apr 13, 2015
I have the following code with dcount
If DCount("Username", "[tbl_userinformation]", "[Username] = " & Me![Text146] & " AND [actualdate]=" & Me![Text148] & " ") > 0 Then
but i have the following error ...
runtime erro 3075: syntax error in number in query expression '[username]=f15691b and [actualdate]=13.04.2015'
f15691b and 13.04.2015 are the values those i entered.
View 13 Replies
View Related
Jan 3, 2014
I am just beginning to manipulate Access from Excel.
I am trying to insert a row into a table. I am adding values for every field (8) except an Autonumber.
I have 2 Insert strings which are identical except for the fact that the one that doesn't work, doesn't specify which fields I am entering data into, which I presume shouldn't be necessary in this case.
When I try to use the second version I receive the error message...
Number of Query Values and Destination Fields are not the same.
The string that works perfectly is:-
Code:
strSQL = "INSERT INTO Clubs (ClubNumber,ClubName,ClubGrade,ClubRegion,ClubPosition,ClubHasHistory,clubinleague,cluboriginalposition) VALUES (" & clubCount + 1 & ",'" & lstrNewClubName & "'," & lintNewClubGrade & "," & lintRegion & "," & 0 & "," & vbFalse & ",'" & lstrNo & "'," & 10 & " )"
The one that generates the error message, which is identical except that I have removed the list of fields is:-
Code:
strSQL = "INSERT INTO Clubs VALUES (" & clubCount + 1 & ",'" & lstrNewClubName & "'," & lintNewClubGrade & "," & lintRegion & "," & 0 & "," & vbFalse & ",'" & lstrNo & "'," & 10 & " )"
The execute command is:-
Code:
gcnConnection.Execute strSQL, , adCmdText + adExecuteNoRecords
View 1 Replies
View Related
