Hi guys I am working on access 2007 database that deals with entering client information and then outputting reports.
The reports I am trying to setup is how many cases of a certain kind has a branch produced from this date to this date.
I have two fields in the database for this, one called Branch, it contains drop down menue with locked values NW, NE, SW and another field called CaseType that contains drop down menue with values Hardest, Hard, Normal, Easy.
Should ooks like this:
Report Period: Month, Day, Year to Month, Day, Year
Branch | Hardest | Hard | Normal | Easy | Total
NW 4 35 14 23 76
NE 23 42 3 76 144
SW 20 12 34 13 79
How do I calculate the numbers, in other words when I create the report what should the formula/code look like for those fields? Also how will the Date option be implemented? Do I need to create a separate report of some sort?
thanks for your help, I am new to databeses but have been on the scripts for quite some time =)
-=Cyberdyne=-
8 1545 Delerna 1,134
Recognized Expert Top Contributor
Hi Cyberdyne
What do the tables look like?
By the term fields I guess you mean drop down boxes on a form
that are selected and then the report is called up by a button on the form?
Hi Cyberdyne
What do the tables look like?
By the term fields I guess you mean drop down boxes on a form
that are selected and then the report is called up by a button on the form?
yea the fields are drop down boxes on the form. Branch and CaseType each containing different options. The reports are called outside the form from a switchboard "Reports"
Delerna 1,134
Recognized Expert Top Contributor
and what do the tables look like?
In particular tha data in the tables from which the report is to be built.
and what do the tables look like?
In particular tha data in the tables from which the report is to be built.
the data is text so....
table Branch has NE, NW, SW, etc as options
table CaseType has Hardest, Hard, Normal, Easy etc
There is also a Date Field for the day the info was entered
so for the report I need to see the numbers so for example from Dec 1 to Dec 31 in the Branch NE - an x number of Hard, Easy and Normal cases came in, and then the total number of these....
ther report will be tabular
Branch>>|Hard| >Easy|Normal|Total>|
NE>>>> |>2>>|>45>|>>41>>|>88>|
NW>>> |>23>|>6>>|>>32>>|>61>|
SW>>>>|>14>|>12>|>>7>>>|>43>|
I know how to create a report and Add the Field Branch to it but after that how do I add the Easy, Hard, Normal in separate boxes and make it show the number of cases that came in for the particular Branch that month?
I imagine it would be a formula or code of some sort
First there would be a condition to the report to only show data from X to X dates
then it would show fields with formula inside... for this branch there were an x number of CaseType (Easy, Hard, or Normal) and then the total of all of the CaseTypes for that month.
Delerna 1,134
Recognized Expert Top Contributor
Sorry Cyberdyne, I think we might be on different rails of the same track.
I understand that you have a form with two combo boxes on it.
one is linked to a branch table with NE,WW etc as the data in it.
one is linked to a Case type table with hardest, hard etc as the data in it.
Actually, I'm not entirely sure how these combo boxes will affect the report
because the report you mention shows all branches and case types.
Anyway,
When I asked what does the table look like I was referring to the table where the numerical info is going to come from. Ie the 2 45 41 88 etc from below.
Branch>>|Hard| >Easy|Normal|Total>|
NE>>>> |>2>>|>45>|>>41>>|>88>|
NW>>> |>23>|>6>>|>>32>>|>61>|
SW>>>>|>14>|>12>|>>7>>>|>43>|
From your posts so far I guess that there is a table that has the date and the number of each type of case that was produced in each branch on that date.
What I need to know is what does that table look like. What are the fields and what does the data look like. I guess the formula you talk about will be an aggregate query on that table that the report will use but I need to see what the table and some of its data looks like in order to see how the query should be constructed, or even if I am on the same track as you.
Bear with me and we will sort it out.
NeoPa 32,556
Recognized Expert Moderator MVP
Assuming you have a table ([tblA]) with these two fields ([Branch] & [CaseType]) then the query you need to run your report from would look something like : - SELECT [Branch],
-
Sum(IIf([CaseType]='Hardest',1,0)) AS [Hardest],
-
Sum(IIf([CaseType]='Hard',1,0)) AS [Hard],
-
Sum(IIf([CaseType]='Normal',1,0)) AS [Normal],
-
Sum(IIf([CaseType]='Easy',1,0)) AS [Easy],
-
Count(*) AS [Total]
-
FROM [tblA]
-
GROUP BY [Branch]
-
ORDER BY [Branch]
PS. Feel free to ask for explanations if there's anything not clear here Cyberdyne. As such a long-standing moderator we'll pull out the stops for you :)
Assuming you have a table ([tblA]) with these two fields ([Branch] & [CaseType]) then the query you need to run your report from would look something like : - SELECT [Branch],
-
Sum(IIf([CaseType]='Hardest',1,0)) AS [Hardest],
-
Sum(IIf([CaseType]='Hard',1,0)) AS [Hard],
-
Sum(IIf([CaseType]='Normal',1,0)) AS [Normal],
-
Sum(IIf([CaseType]='Easy',1,0)) AS [Easy],
-
Count(*) AS [Total]
-
FROM [tblA]
-
GROUP BY [Branch]
-
ORDER BY [Branch]
PS. Feel free to ask for explanations if there's anything not clear here Cyberdyne. As such a long-standing moderator we'll pull out the stops for you :)
Thanks so much NeoPa, working on it =)
Sign in to post your reply or Sign up for a free account.
Similar topics |
by: CSDunn |
last post by:
Hello,
I have a situation with MS Access 2000 in which I need to display report
data in spreadsheet orientation (much like a datasheet view for a form). If
you think of the report in terms of what...
|
by: Nathan Bloomfield |
last post by:
Does anyone know if there is any documentation which relates to Access2k + ?
or can anyone help adjust the code?
I am having trouble converting the DAO references.
TITLE :INF: How to...
|
by: Pol Bawin |
last post by:
Hi All,
Did somebody already define attributes for numerical properties to define
value : minima, maxima, a number of decimal, ...? This information would be
useful to unify syntax
Polo
|
by: KR |
last post by:
KR
Feb 6, 1:48 pm show options
Newsgroups: microsoft.public.access.forms
From: "KR" <kra...@bastyr.edu> - Find messages by this author
Date: 6 Feb 2006 13:48:00 -0800
Subject: Extract Number...
|
by: Martin Jørgensen |
last post by:
Hi,
I've made a program from numerical recipes. Looks like I'm not allowed
to distribute the source code from numerical recipes but it shouldn't
even be necessary to do that.
My problem is...
| |
by: rinmanb70 |
last post by:
I have a table of transactions, some with past dates, some dated the
current date, and some dated in the near future. On a report, I'm
looking for a way to get four different sums using the...
|
by: rinmanb70 |
last post by:
I use a db for keeping up with my checkbook and I'm having trouble
setting up a new functionality. I get paid every two weeks on Friday,
and I need a report to figure my balance that includes only...
|
by: Johannes Nix |
last post by:
Hi,
this might be of interest for people who are look for practical
information on
doing real-time signal processing, possibly using multiple CPUs, and
wonder
whether it's possible to use...
|
by: ollyb303 |
last post by:
Hi,
I am trying to create a dynamic crosstab report which will display number of calls handled (I work for a call centre) per day grouped by supervisor.
I have one crosstab query (Query1) which...
|
by: Hystou |
last post by:
There are some requirements for setting up RAID:
1. The motherboard and BIOS support RAID configuration.
2. The motherboard has 2 or more available SATA protocol SSD/HDD slots (including MSATA, M.2...
|
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,...
| |
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,...
|
by: jinu1996 |
last post by:
In today's digital age, having a compelling online presence is paramount for businesses aiming to thrive in a competitive landscape. At the heart of this digital strategy lies an intricately woven...
|
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...
|
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,...
|
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...
|
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...
| |
by: muto222 |
last post by:
How can i add a mobile payment intergratation into php mysql website.
| |