Queries :: Sum Of Same Fields Many Times With Different Criteria In Same Query

Mar 20, 2015

I have a table [maintable] that holds print records, with the following fields :

id,userlogon,printername,pagecount,color(yes/no),duplex(yes/no),timestamp and papersize.

I want to create a query that will show the following:

Sum of pagecount where papersize like 'a4' and between 2 dates but FOR EACH USERLOGON.The dates will take them from 2 fields in a form so i will use this

"WHERE (((maintable.Timestamp)>=[forms]![reportsform]![frmdate] And (maintable.Timestamp)<DateAdd("d",1,[Forms]![reportsform]![todate])))" and some more..

So i want the query to show,first the userlogon then a field to show sum of a4 then a4 color prints etc..(remember,i want the between 2 dates criteria to be global,to the whole query)

here are all of the criteria fields i want:

1) - papersize=A4
2) - papersize=A4 and Color=True
3) - papersize=A4 and Color=False
4) - papersize=A4 and Duplex=True
5) - papersize=A4 and Duplex=False
6) - papersize=A4 and Color=True and Duplex=True
7) - papersize=A4 and Color=True and Duplex=False
8) - papersize=A4 and Color=False and Duplex=True
9) - papersize=A4 and Color=False and Duplex=False

View Replies


ADVERTISEMENT

Queries :: Query Criteria Between Two Dates And Two Times

Sep 1, 2014

I have a table which list a load of items, one field is date and one field is time.I have a form with two date boxes and two time boxes, the idea is for the user to search between the two inputted dates and the two inputted times.This then runs a query for a report to be produced. The problem I having is getting the query to runs both criteria it returns nothing.here is the Where part of the current SQL.

WHERE (((tblIncident.IncDate) Between [Forms]![FRM_SearchMulti]![txtrepdate]
And [Forms]![FRM_SearchMulti]![TxtrepDateB]) AND ((tblIncident.IncTime) Between [Forms]![FRM_SearchMulti]![txtreptimea]
And [Forms]![FRM_SearchMulti]![txtreptimeb]))ORDER BY tblIncident.IncDate, tblIncident.IncTime;

View 7 Replies View Related

Queries :: Using Same Field Multiple Times Under Varying Criteria?

Dec 30, 2014

Preamble edit: I'm running Access 2003.

I have two tables, Students and AttendanceRecords.

Students just has studentID and studentName

AttendanceRecords has AttRecID, studentID, presence, thedate

I'm looking to create what looks like an Excel grid, with the last 10 days as columns and the student names as rows. All the cells in middle will be filled with the values of 'presence' for that student/day (e.g., P for present, A for absent).

Here's something I'm currently considering.

-I could make 10 queries, each using LEFT JOIN to connect studentName with presence & thedate on studentID, varying the 10 queries only in that 'thedate' will have a criteria of Date() -1 , Date() -2 , etc.
-If I'm understanding it correctly, I'll then have 10 tables, each containing 3 rows -- student name, presence, and the date (with each table having only 1 date repeated throughout).
-I could then join those 10 queries together on studentName, theoretically resulting in 1 big table with all the student names and the corresponding presence values for the last 10 days

If I do that, I could make a form in Continuous view and have each row show the studentName and 10 text boxes closely bunched up with presence values.

That seems very inefficient? Making 10 queries separately and then manually merging them seems redundant.

Also, now that I think about it, will the final product end up being read-only, or if the user changes one of the presence cells will it update the corresponding record in AttendanceRecord?

View 3 Replies View Related

Queries :: Field Appears Multiple Times But Different Criteria For Each Column

Jun 2, 2013

I'm working with a table of bird survival data I am trying to summarize in a query. I've got a bit of a roundabout way to achieve my goal, but I'm curious if there is a simpler approach.

Background : In my table, each row represents a day I check a given nest and includes a [Nest ID] (not unique, multiple visits to each nest), a [visit ID] (auto numbered, so it's a unique value for each visit at each nest), the calendar day I visited a nest [Date], and [Survive] (1 or 0) depending on whether a nest survived or failed.

I'm trying to convert this detailed table into one that is more concise. Instead of each visit to a nest being a row, each nest becomes a row with 4 fields: The Nest ID, the minimum date (the day I found a nest), the last day a nest was checked (Max[Date]), and the last day a nest was checked alive (essentially max date where survival=1).

My current solution is to run 3 separate queries. The first queries the max date where survival=1, the second queries the max and min dates regardless of any other criteria, and the third brings both queries together.

I am curious if there is a way to create the same final product in a single query rather than doing multiple ones as I have done?

View 3 Replies View Related

Prompted Multiple Times For Query Criteria

Sep 27, 2006

I have a report that is based on a query. In the query, I have it prompt for a criteria for a field. It prompts for the name of an event, so only the participants of that event are displayed.

Also on that report, I have a subreport, which is based on that same query, but returns different data.

My problem is that since both the main report and the subreport are based on the query that prompts for input, it's prompting me multiple times for the same input when I view the report.

Is there a way to have Access use the same prompted input for both reports, so I don't have to enter it more than once?

View 1 Replies View Related

Queries :: Allowing Users To Select Multiple Table Fields As Query Criteria

Apr 22, 2013

I am fairly new to Access and I would like to create a form to allow users to create their own query. I would like to allow users to select multiple fields (perhaps with checkboxes?) from all possible fields in a table to return either all data from that field or narrow their search by inputting certain criteria or choosing from a drop down into a text box. Is this possible in Access and any detailed specifics on how to achieve this?

View 3 Replies View Related

Queries :: Run Append Query N Times

Jun 10, 2014

I have a table with 3 fields:

numberFROM, numberTO, Quantity

e.g.:
10, 15, 6

I would execute an append query that will store in another table:

10
11
12
13
14
15

How could I mange it?

View 14 Replies View Related

Queries :: Dynamic Query Needs Parameter Two Times

Oct 18, 2013

Sometimes i have a problem with dynamical queries. For some of these i need to put in the same parameter value two times, before it works. Why this occurs?

View 2 Replies View Related

Queries :: Append Query One Record Several Times

Feb 13, 2014

I have a table with rooms each room have a number of gust. I want to append each room many times equal to the number of gusts.

View 1 Replies View Related

Queries :: Count Of Times A Appears In Query

Oct 24, 2014

I have a query that gives a value in a column either "A","V","L","H","P","S" or blank.

I want to be able to count the amount of occurrences of each letter and hold that number in a column. Is it easy to do?

View 5 Replies View Related

Queries :: Count The Amount Of Times A Name Appears In A Query?

May 31, 2013

I have a log in table that counts every time a user logs onto the database. Each time a user logs in it adds another entry to a table.

I have built a query to display the user name and date of log in so I could display this information in a report. The report is now getting rather long, and I am looking for a way to display each users name and have the total number of times they have logged in, not display each time they have logged in separately.

The Query has two fields "Agent Name" and "Logger Date"

The report displays the data as follows

Agent Name Logger Date
User 1 26/5/13
User 1 26/5/13
User 1 27/5/13
User 2 28/5/13

What I would like to do is have a report listing each users name, with a column showing how many times they have logged on. e.g.

User Name Login Count
User 1 3
User 2 1

View 10 Replies View Related

Queries :: Query Filter Out Data From A Table Between Times On 2 Dates

Jul 24, 2014

Running Access 2010 and developed this query to filter out data from a table between times on 2 dates (day before report run and day of report). Covers data from a shift that carries over to the next day.Trouble is, the PC i developed on still operates the query as expected. However, on the PC the database resides (not networked just stored) and operates, the query brings up no data at all unless I remove the Time filtering.

This PC used to operate correctly up until early this year (about 18 months operated correctly) when the PC was replaced due to failure. Access version is the same and I am at wits end to what the cause is. Here is what my query looks like:

Quote:

SELECT Breakdowns.BreakdownDate, Breakdowns.Time, Breakdowns.Shift, Breakdowns.Downtime, Breakdowns.Equipment, Breakdowns.Conveyor, Breakdowns.Fault, Breakdowns.Stopper, Breakdowns.Gate, Breakdowns.Dolly, Breakdowns.Carrier, Breakdowns.FaultType, Breakdowns.Comments, Breakdowns.Tradesman
FROM Breakdowns
WHERE (((Breakdowns.BreakdownDate)=Date()) AND ((Breakdowns.Time) Between #00:00:00# And #6:29:00#) AND ((Breakdowns.Shift)="Night")) OR (((Breakdowns.BreakdownDate)=Date()-1) AND ((Breakdowns.Time) Between #22:30:00# And #23:59:00#) AND ((Breakdowns.Shift)="Night"));

View 14 Replies View Related

Queries :: Create A Query To Show Rows With Timestamps In Between Two Times

Jul 7, 2013

Create a query to show rows with timestamps in between two timestamps? my variables are cutoff time and the current time where cutoff time is saved on a table (tblcontroltable.cutoff ) a pseudo code of what i want to achieve is

select date, product, timestamps
from tblsample
where date = date and timestamps > cutoff and timestamps < time

View 4 Replies View Related

Queries :: Method For Combining Crosstab Queries With Same Criteria From Multiple Fields?

Dec 2, 2014

I have 3 cross tab queries that are completely identical with the exception of the field that they pivot. Each field is searching for the same values just in different columns, with the end goal being to get the sum of the values for each pivoted column. I'm wondering rather than having 3 almost identical queries is there a way to use a crosstab to sum the values from each of the three fields rather that having 3 queries which then have to be aggregated in a fourth?

QUERY1

Code:
TRANSFORM
IIf(Count(PT_LEVEL.UNIT) Is Null,0,Count(PT_LEVEL.UNIT)) AS CountOfUNIT
SELECT
PT_LEVEL.INF_YEAR,
PT_LEVEL.INF_MONTH,
PT_LEVEL.UNIT

[code]...

View 2 Replies View Related

Queries :: Run Query Multiple Times (different Data Parameter) To Generate Single Dataset

Mar 30, 2015

I have a reasonably complex query (3 subqueries into 1 main query) which gathers data from various tables into a single dataset based on a specified date.

I now need to generate a similar dataset but across a range of dates (a month) for reporting purposes. However, I can't just adapt the query and change the parameter from a "=#<Date>#" format to a "Between #<Date1># And #<Date2>#" format

The reason being, each date has to be treated individually and has to be queried as a standalone. It's to do with the type of data I have (one-to-many relationships between tables)

So what I really need to do is run the same query multiple times, for each date in scope, then stitch all of those datasets together into one 'giant' one.

How to do that in SQL (effectively, have one query produce the dates in scope, then join that onto the other query, passing each date as the parameter - I don't even think that's possible to be honest)

The other option I can think of is to use VBA to loop through the dates in scope, then use a QueryDef object to set the parameter and read the records for each date into a Recordset object. But then I have the problem of stitching all the Recordsets together, without looping through all the fields and rows each time.

View 3 Replies View Related

Queries :: 2 Different Date Fields With Same Criteria

Apr 1, 2014

I am trying to figure out how to build my query that returns equipment reservations where "date out" OR "date In" are between 2 dates that are specified on my form using [Forms]![Myformname]![StartDate] and [Forms]![Myformname]![EndDate].

The purpose of the query is to find the amount of companies that have had a piece of equipment reserved between 2 dates, which could be a calendar year, or a fiscal year for example.

This is my current SQL statement that returns records where only DateIn falls between the criteria. My objective is to also return records where DateOut falls between 2 dates.

Code:
SELECT tblReservations.ReservationNumber, tblCompanies.CompanyName,
tblLookup_Contact_types.[Contact Type], tblReservation_details.Dateout, tblReservation_details.DateIn
FROM tblLookup_Reservation_Status INNER JOIN (tblCompanies INNER JOIN ((tblLookup_Contact_types INNER JOIN tblReservations ON tblLookup_Contact_types.[Contact Code] = tblReservations.CategoryID)

[Code] .....

View 4 Replies View Related

Queries :: Criteria To Display Records With Values >0 In Different Fields

Sep 24, 2014

i made a query to display students results after an exam. there are 12 subjects but a student chooses 8 from that. After an exam, the results are keyed in and a result slip is printed later. The problem is that the result slip displays all subjects including those not done by the student. I typed a criteria >0 in one of the fields and no records appeared. Is there a way that i can use to display only the subjects chosen and marks attained by a student and leave out the rest?

View 1 Replies View Related

Queries :: Update Fields Of One Table With Multiple Criteria

Aug 12, 2015

How can i update fields of one table with update query where creteria are more than one 1

Example:

DoCmd.RunSQL "UPDATE Salary2009toUpdate SET " & Me.Combo2 & "='" & Me.Text4 & "'
Where Empid='" & Me.Combo0 & "'" and where bank='" & Bankname & "'"

What is the syntax error ...

View 5 Replies View Related

Queries :: Not Criteria To Bring Up Records - Multiple Fields With Data

May 30, 2015

I'm creating a query from one table using two fields that require certain records to NOT bring up records that have the following text:

One table, two fields

First field ECO LifeCycle Status Criteria is Not "ERP UPDATE" or "CANCELLED"
Second field ECR LifeCycle Status Criteria is Not "COMPLETE"

When I put the Not Criteria in the first field only I get a result.
When I put the Not Criteria in both fields I get no results.

View 9 Replies View Related

Reports :: Blank Fields - Counting Times?

Feb 12, 2014

I have a report that is pulling data from a query and on the report, i have a controlled field that is supposed to count only data where a field says "no". Here is the string.

=Sum(IIf([Caller Used Resources]="No",1,0))

when i run the report i get the four results that say no, but i also get a blank field counting the times when this field is blank. If this field is blank, i don't want it to show anything.

View 1 Replies View Related

Query Two Fields Based On One Criteria

Aug 21, 2005

Hi all,

I am endeavouring to filter a form based on the position of an individual.
I would like the users to be able to select their position and then any record where there is a match in the "Primary Responsibility" field or the "Secondary Responsibility " field will be displayed.

I have an underlying query that is populated by a combo box on an unbound form.

This has worked when generating reports for individuals but I cannot get the required result when using the form.

Thanking anyone in advance who can assist me with this.

Regards,

Rod

View 2 Replies View Related

Query Based On Criteria In Two Fields?

Sep 7, 2007

Hi All

I want to create a query which is based on two fields.

One field is a date query i.e >=Date()-7 OR >=Date+7

The Other is a Combo box where the criteria is No

My problem is that access is currently running this as two individual queries.

Is it possible to make this query return data only when both fields are matching?

Or do I have to run this as a parameter query?

Thanks!!

View 2 Replies View Related

How To Sum Fields In A Query With Different Date Criteria From Other Table

Aug 31, 2015

Table 1: contains sales summed by salesrep by week_date for 5 years + current
Table 2: contains sales rep, start_date and end_date

Query: Sum sales by rep where (start_date >= week_date and end_date <=week_date)

Each rep has different start and end dates.

View 7 Replies View Related

Queries :: Sum Between Two Times

Nov 22, 2013

I work for a chemical company, over the course of the day there are 2 shifts, one from 07:00 - 19:00 and the other from 19:00 - 07:00. I want to measure the amount of time a piece of equipment has been offline for, and Sum these times for each shift, these pieces of equipment can go offline multiple times per day which is why I need the sum function. The fields I currently have are:

[Date1] : The date at which it went offline
[Time] : The time at which the piece of equipment went offline
[Duration] : The length of time it was offline for

For each day I wish to sum the offline time in two fields called:

[Shift 1]: The previous nights shift (between 7pm the day before and 7am that day).
[Shift 2]: The days shift (between 7am and 7pm that day).

Where to begin but the logic should be something like:

[Shift 1] = Sum of [Duration] For(([Date1]-1 and [Time] between 19:00 and 23:59) And ([Date1] and Time between 00:00 and 07:00))

[Shift 2] = Sum of [Duration] For([Date1] and [Time] between 07:00 and 19:00)

I currently have two tables storing all this data, one containing [Date1], and another containing the breakdown information such as [Duration], [Time] etc. They are linked by the [Date1] relationship. The reason for the first table only having the [Date1] field is because it is a Main Form which contains various sub-forms.

View 3 Replies View Related

Queries :: Between Two Times Within IF

Mar 29, 2014

I am trying to build a query that depending on the value stored in a combo box called SHIFT, either lists all the entries that have been made by our dayshift (07:00-19:00) or our night shift (19:00-07:00)

Each of these components work if I seperate them out

IIf([forms]![FRMFRame74]![SHIFT]=1,Between #07:00:00# And #19:00:00#,Between #19:00:01# And #23:59:59# And Between #00:00:00# And #06:59:59#)

View 2 Replies View Related

General :: MS Query Criteria With Two Fields If Either Contain A January Date

Jun 16, 2015

Basically, I have a table showing our clients and when their next service dates are:

I need the query to show NEXT SERVICE DUE dates in January, AND/OR if NEXT LOLER has any January dates.

These should show as either or both fields have January dates:

Next Service Due: February
Next LOLER: January

Next Service Due: January
Next LOLER: December

Next Service Due: January
Next LOLER: January

I managed to do the NEXT SERVICE DUE with January dates, but not both

View 3 Replies View Related







Copyrights 2005-15 www.BigResource.com, All rights reserved