H,
I'm hoping some of you can give me ideas on how to deal with our challenge. We are developing a system that will accept excel data files on counseling and health services accessed by users. The users may get the same or different services from multiple agencies in different locations. We want to analyse the combined data to report on the cascade of service accessed and the mobility of users among the locations. This initial phase is meant to show what can be reported from the data and support discussions with the users for the final system design. The final system will most likely be programmed in SQL 2012, but we are using Excel and Access for the initial phase.
The data looks like this, but the real files contain many more services:
ID Age Sex Date Service Cinic
XX123 30 M 10/03/2016 TEST North
XX123 30 M 15/03/2016 ART North
XX123 30 M 25/03/2016 ART South
XX111 21 F 3/03/2016 TEST East
XY222 25 F 5/03/2016 TEST West
XY222 25 F 20/03/2016 TEST Upper
ZZ321 42 M 22/03/2016 TEST Lower
ZZ333 38 F 20/03/2016 ART North
ZZ333 38 F 28/03/2016 ART South
Is there a way of using functions such as COUNTIFS, SUMIFS, etc without having to merge the rows into a single row per ID? Do I need to use pivot tables, macros or move the data into Access? Thanks for any ideas!
4  3710  Rabbit 12,516
Recognized Expert Moderator MVP
I don't know what you mean my using count if without merging rows. What would the results be of the sample data above?
Hi Rabbit. Thanks for replying. My initial thought was to combine the rows so there would be one record per person and then proceed with the counts of who accessed what service and where. But, I'm trying to see if there is a way to analyse the data from the multiple rows, as a friend thinks that would be more flexible.
For example, we want to create reports showing the number of people who have accessed a certain mix of services (choices include counselling, condom pickups, medical referrals, tests and treatment) and the number that have moved between the various clinics.
Rabbit 12,516
Recognized Expert Moderator MVP
Yes, you can analyze multiple rows. And you should, that's the preferred method. Denormalizing the data and bringing all onto one row is usually a bad idea.
Again, your descriptions are very vaguely defined. All I can say is you can and should analyze it over multiple rows. And you will probably use an aggregate query of some sort. How you go about doing so will depend on your exact requirements which you have not specified.
Sign in to post your reply or Sign up for a free account.
Similar topics | |
by: shank |
last post by:
I have a recordset that contains multiple records of product a user is
purchasing. For clarity, I converted the recordset fields to variables. I
need to take that entire recordset and insert it into another table on a
remote server. The below code only inserts 1 record. How do I change the
code to get all records inserted?
thanks!
<%
Dim DataConn2
Set DataConn2 = Server.CreateObject("ADODB.Connection")
|
by: crispy |
last post by:
On this page, you'll see that some PubID's are repeated several times:
http://www.ihea.info/pages/website/test1.asp
That's because some records have more than one attribute. For example,
Title with pubID 10 is about both Cervical Cancer and Endometrial
Cancer, so pubID 10 appears twice.
How do I make ASP show me only one instance of record 10 and then
simply concantenate Cervical Cancer and Endometrial Cancer?
|
by: Kaur |
last post by:
Hi, I have been successful copying a vba code from one of your posts on
how to copy and paste a record by declaring the desired fields that
needs to be copied in form's declaration and creating two button "copy"
and "paste". Works like magic.
My problem is how can I copy multiple records and paste them at the
same time. My data entry form has main form that has a Questionlist box
of Questions. The second list box on the same form displays...
|
by: NumberCruncher |
last post by:
Hi All,
I am struggling with setting up my first system of tables, forms,and reports, and could use your help!
I am setting up a database to keep track of the production of a produced item. The item is a panel, with a specific texture. There are standard panels that are then produced in a limited number of specific textures. The number of panels/project varies, so I keep track of the actual number of panels per project with the count:...
|
by: Pim75 |
last post by:
Hello,
I want to store multiple records at once in a SQL database with a
For..Next instruction like the sample code below:
For counter = 0 To 100
Dim dbInsert As New SqlCommand( _
"INSERT INTO table (Field1, Field2) VALUES ('" & Field1 & "', '" &
Field2 & "')", _
myConnection)
| | |
by: wozza |
last post by:
hi
I'm a Dreamweaver user who's created a few simple data entry/
registrations forms in my time, but I'm not really a coder (though I
can follow instructions and am not afraid to dabble...) - I generally
make use of DW's builtin commands and some extensions.
Anyway I'm creating a registration site for event exhibitors and I've
been asked to come up with a method of automatically generating
passwords for inserted records, either as...
|
by: phill86 |
last post by:
Hi,
i have a form that runs a query in a recordset on the after update method if i copy and paste one record at a time the query picks up the records in the underlying table but if i paste multiple records the query fails to pick up the set of pasted records i think the after update method is running before the table is properly updated when i paste a group of records I have also tried running the code from the after insert method with no...
|
by: harmony123 |
last post by:
I would like to display the multiple records in multiple textboxes
Following is my tables and data:
tblJan with these data:
col id
1
2
3
col January
10
|
by: postman |
last post by:
I need to combine multiple records into one row with multiple columns for those records. Very similar to a crosstab query, but one that shows all the records, not a summary of them.
For example, if the table is like this:
Name Month Score
John Jan 92
John Jan 90
John Feb 97
John Feb 91
|
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: Hystou |
last post by:
Overview:
Windows 11 and 10 have less user interface control over operating system update behaviour than previous versions of Windows. In Windows 11 and 10, there is no way to turn off the Windows Update option using the Control Panel or Settings app; it automatically checks for updates and installs any it finds, whether you like it or not. For most users, this new feature is actually very convenient. If you want to control the update process,...
| | |
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: 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: adsilva |
last post by:
A Windows Forms form does not have the event Unload, like VB6. What one acts like?
|
by: 6302768590 |
last post by:
Hai team
i want code for transfer the data from one system to another through IP address by using C# our system has to for every 5mins then we have to update the data what the data is updated we have to send another system
| | |
by: bsmnconsultancy |
last post by:
In today's digital era, a well-designed website is crucial for businesses looking to succeed. Whether you're a small business owner or a large corporation in Toronto, having a strong online presence can significantly impact your brand's success. BSMN Consultancy, a leader in Website Development in Toronto offers valuable insights into creating effective websites that not only look great but also perform exceptionally well. In this comprehensive...
| |
