Access Version: 2003
OS: XP Pro
Sorry if this is a bit long winded. First a little info about the database. This database is used to keep track of checkout information from our production department. It has three main tables that store information, the first one, that I have named "Checkout", stores the information I am querying for the two reports. It has six different fields - OrderNumber, Date, CheckedBy, BuiltBy, SerialNumber, and ComputerType. Using the following query, I pull information for a certain person and date range then group based on the date and computer type, count up the number of that computer type for that date and calculate the hours of work that translates to. (Query 1) -
SELECT Checkout.Date, Checkout.BuiltBy, Checkout.ComputerType,
-
Count(Checkout.ComputerType) AS CountOfComputerType,
-
IIf([ComputerType]="Desktop",[CountOfComputerType]*1,
-
IIf([ComputerType]="Laptop",[CountOfComputerType]*0.5,
-
IIf([ComputerType]="Server",[CountOfComputerType]*2,
-
IIf([ComputerType]="MB Kit",[CountOfComputerType]/12)))) AS Hours
-
FROM Checkout
-
GROUP BY Checkout.Date, Checkout.BuiltBy, Checkout.ComputerType
-
HAVING (((Checkout.Date) Between [Forms]![Production Report]![From Date] And [Forms]![Production Report]![To Date])
-
AND ((Checkout.BuiltBy)=[Forms]![Production Report]![TechName]));
-
In the second query, which follows, I select the number of machines they have checked out by using the same date range from above and searching for their initials in the "CheckedBy" field, then count the number of occurrences and calculate the hours of work that translates to. (Query 2) -
SELECT Checkout.Date, Checkout.CheckedBy, Count(Checkout.CheckedBy) AS CountOfCheckedBy, [CountOfCheckedBy]/3 AS Hours
-
FROM Checkout
-
GROUP BY Checkout.Date, Checkout.CheckedBy
-
HAVING (((Checkout.Date) Between [Forms]![Production Report]![From Date] And [Forms]![Production Report]![To Date])
-
AND ((Checkout.CheckedBy)=[Forms]![Production Report]![TechName]));
The problem is that I am not sure how to combine the information from these two queries in one report that will list the total number of calculated hours by day and then put an average for the these days at the bottom of the report.
Anybody have any thought?
1  2283 
It took me two day but I came up with a solution. I had to create four different queries, one main report, and four different subreports.
Sign in to post your reply or Sign up for a free account.
Similar topics | |
by: frizzle |
last post by:
Hi,
I know this might sound strange but i think(/hope)
it's quite simple:
I'm running 2 queries in a mysql DB, first one returns 20 results.
Now how can i echo results from the second query in the 1st query's
result,
like:
|
by: Anne |
last post by:
I have a report containing multiple subreports (approximately 37
subreports) that are based on individual queries. The query calls to
only display the subreport if there is valid data in the table (that
the subreport is based on). Because the subreport may not always
appear, there are spaces in between the subreports that do contain
valid data. Is there any way to resize or reformat the main report so
all subreports with valid data...
|
by: Simon Pleasants |
last post by:
Am something of a newbie at this, so please bear with any stupid
questions.
I have created a database to track shipments that we import. The
information is stored in a table and I have created a form which
displays the information from the table. The end users have access to
the form only - this is deliberate.
I have created some queries, including parameter queries, to aid them
which are accessible via buttons on the main form. ...
|
by: Sherman H. |
last post by:
I have a few questions for crosstab and popup form questions:
1. I created a crosstab as follows:
IT Financial Operation
John 21 22 22
Mary 15 17 16
Joe 28 29 31
For some other instances, the Operation column was not created because the
|
by: justin tyme |
last post by:
Hello Experts!
I would like to combine (which may not be the correct technical term) two
text fields from the same table in a query. Specifically, text field A and
text field B are both lists of names. My goal is to have one long list of
all the names, not A next to B like one gets when you use the & operator.
The eventual goal is to be able to print labels of all the individuals. I
have no problem creating a label-Report from the Query....
| | |
by: afr0ninja |
last post by:
Hello!
I'm fairly new to access, but I'm starting to get the hang of it.
This will be a bit lengthy but hopefully it'll have enough information
for some kind soul to help me out. I'm currently working on a database
where I export data from another program, import the data into access
and then run various queries and what not to generate information
needed for business planning. I can't link directly to the existing
program, so I need...
|
by: tcveltma |
last post by:
Hi again,
Ok, so I have about 15 crosstab queries. Each crosstab query is an employee's name. Within the query are the weeks as the column heading, the work order numbers as the row heading, and the hours as a value. Each query comes from a different employee table (1 table per employee). Is there any way I can combine all the queries into 1 query/report?
So far it seems to me that the only way I'll have a chance at making every...
|
by: Gilberto |
last post by:
Hello
I have created TWO different queries (for products belonging to FRONT and REAR) which use product information to filter the total (sum all belonging to the same category) of all the FRONT products belonging to every category. Same thing applies to the second query but just with products belonging to the REAR.
The type of categories are the same for both queries (RED, BLUE, BLACK) and i just change the "CRITERIA" under the SEATING...
|
by: Ken Fine |
last post by:
This is a question that someone familiar with ASP.NET and ADO.NET DataSets
and DataTables should be able to answer fairly easily. The basic question is
how I can efficiently match data from one dataset to data in a second
dataset, using a common key. I will first describe the problem in words and
then I will show my code, which has most of the solution done already.
I have built an ASP.NET that queries an Index Server and returns a...
|
by: marktang |
last post by:
ONU (Optical Network Unit) is one of the key components for providing high-speed Internet services. Its primary function is to act as an endpoint device located at the user's premises. However, people are often confused as to whether an ONU can Work As a Router. In this blog post, we’ll explore What is ONU, What Is Router, ONU & Router’s main usage, and What is the difference between ONU and Router. Let’s take a closer look !
Part I. Meaning of...
|
by: Hystou |
last post by:
Most computers default to English, but sometimes we require a different language, especially when relocating. Forgot to request a specific language before your computer shipped? No problem! You can effortlessly switch the default language on Windows 10 without reinstalling. I'll walk you through it.
First, let's disable language synchronization. With a Microsoft account, language settings sync across devices. To prevent any complications,...
| | |
by: Oralloy |
last post by:
Hello folks,
I am unable to find appropriate documentation on the type promotion of bit-fields when using the generalised comparison operator "<=>".
The problem is that using the GNU compilers, it seems that the internal comparison operator "<=>" tries to promote arguments from unsigned to signed.
This is as boiled down as I can make it.
Here is my compilation command:
g++-12 -std=c++20 -Wnarrowing bit_field.cpp
Here is the code in...
|
by: tracyyun |
last post by:
Dear forum friends,
With the development of smart home technology, a variety of wireless communication protocols have appeared on the market, such as Zigbee, Z-Wave, Wi-Fi, Bluetooth, etc. Each protocol has its own unique characteristics and advantages, but as a user who is planning to build a smart home system, I am a bit confused by the choice of these technologies. I'm particularly interested in Zigbee because I've heard it does some...
|
by: agi2029 |
last post by:
Let's talk about the concept of autonomous AI software engineers and no-code agents. These AIs are designed to manage the entire lifecycle of a software development project—planning, coding, testing, and deployment—without human intervention. Imagine an AI that can take a project description, break it down, write the code, debug it, and then launch it, all on its own....
Now, this would greatly impact the work of software developers. The idea...
|
by: isladogs |
last post by:
The next Access Europe User Group meeting will be on Wednesday 1 May 2024 starting at 18:00 UK time (6PM UTC+1) and finishing by 19:30 (7.30PM).
In this session, we are pleased to welcome a new presenter, Adolph Dupré who will be discussing some powerful techniques for using class modules.
He will explain when you may want to use classes instead of User Defined Types (UDT). For example, to manage the data in unbound forms.
Adolph will...
|
by: conductexam |
last post by:
I have .net C# application in which I am extracting data from word file and save it in database particularly. To store word all data as it is I am converting the whole word file firstly in HTML and then checking html paragraph one by one.
At the time of converting from word file to html my equations which are in the word document file was convert into image.
Globals.ThisAddIn.Application.ActiveDocument.Select();...
|
by: TSSRALBI |
last post by:
Hello
I'm a network technician in training and I need your help.
I am currently learning how to create and manage the different types of VPNs and I have a question about LAN-to-LAN VPNs.
The last exercise I practiced was to create a LAN-to-LAN VPN between two Pfsense firewalls, by using IPSEC protocols.
I succeeded, with both firewalls in the same network. But I'm wondering if it's possible to do the same thing, with 2 Pfsense firewalls...
| | |
by: muto222 |
last post by:
How can i add a mobile payment intergratation into php mysql website.
| |
