I have an Access 2003 mdb which contains software records. I need to
sort on a particular type of software, and then identify and count how
many copies there are per each group of that type purchased on the same
date.
I have no trouble doing a query to extract the type (say MS Excel 2002,
for example). The trouble is, there could be 50 copies purchased on
date "x", 80 copies purchased on date "y", and 250 copies purchased on
date "z", etc.
Is there any way I can use a function, or is there a code example that
would scan the date field, and count the number of records which exist
for each separate date of purchase?
Any help is appreciated!
BlackFireNova
*** Sent via Developersdex http://www.developersdex.com ***
Don't just participate in USENET...get rewarded for it! 2 2298
I believe you're going to find this easier than you anticipated.
Create a query in the Query Grid, including the Date and some other field
which will always be present. In design view, on the menu, choose View |
Totals. Under the Date, select GroupBy, and under the other field, select
Count.
If your "dates" are actually date and time, then extract the Date using the
DateValue function (in the query itself) and GroupBy the extracted date
value.
Larry Linson
Microsoft Access MVP
"BlackFireN ova" <BF************ *****@myrealbox .com> wrote in message
news:40******** *************@n ews.frii.net... I have an Access 2003 mdb which contains software records. I need to sort on a particular type of software, and then identify and count how many copies there are per each group of that type purchased on the same date.
I have no trouble doing a query to extract the type (say MS Excel 2002, for example). The trouble is, there could be 50 copies purchased on date "x", 80 copies purchased on date "y", and 250 copies purchased on date "z", etc.
Is there any way I can use a function, or is there a code example that would scan the date field, and count the number of records which exist for each separate date of purchase?
Any help is appreciated!
BlackFireNova
*** Sent via Developersdex http://www.developersdex.com *** Don't just participate in USENET...get rewarded for it!
Larry:
Your solution gives me only two elements of the information. No
matter what I try, if I try to acquire the additional date info it
skews the results.
I can, as described, do a query that groups by Software Type, and
counts the date groups, however I also need to retrieve the actual
DATE of each group counted. This is where I'm having trouble.
As you described, I can generate an output that shows me (Software
[grouped]) and the count of how many per date purchased. When I try
and add the additional field to show the ACTUAL DATE of each date
group counted, it skews the query output.
How can I achieve this additional output?
ie: Say there were 200 Excel Licenses purchased in four groups, on
four dates
Software # of License per Date of Purchase Date
Purchased
Excel 25
xx/xx/xxxx
Excel 100
yy/yy/yyyy
Excel 75
zz/zz/zzzz
As I said, the query to group by type, and count the # of licenses per
each date group is great. How can I derive the date per each group?
Thanks,
BlackFireNova
"Larry Linson" <bo*****@localh ost.not> wrote in message news:<tF******* ************@nw rddc01.gnilink. net>... I believe you're going to find this easier than you anticipated.
Create a query in the Query Grid, including the Date and some other field which will always be present. In design view, on the menu, choose View | Totals. Under the Date, select GroupBy, and under the other field, select Count.
If your "dates" are actually date and time, then extract the Date using the DateValue function (in the query itself) and GroupBy the extracted date value.
Larry Linson Microsoft Access MVP "BlackFireN ova" <BF************ *****@myrealbox .com> wrote in message news:40******** *************@n ews.frii.net... I have an Access 2003 mdb which contains software records. I need to sort on a particular type of software, and then identify and count how many copies there are per each group of that type purchased on the same date.
I have no trouble doing a query to extract the type (say MS Excel 2002, for example). The trouble is, there could be 50 copies purchased on date "x", 80 copies purchased on date "y", and 250 copies purchased on date "z", etc.
Is there any way I can use a function, or is there a code example that would scan the date field, and count the number of records which exist for each separate date of purchase?
Any help is appreciated!
BlackFireNova
*** Sent via Developersdex http://www.developersdex.com *** Don't just participate in USENET...get rewarded for it! This thread has been closed and replies have been disabled. Please start a new discussion. Similar topics |
by: Bambero |
last post by:
Hello all
Problem like in subject. There is no problem when I want to count days
between two dates. Problem is when I want to count years becouse of
leap years.
For ex.
between
2002-11-19 2003-11-19
|
by: edwardfredriks |
last post by:
I'm looking for a script that, instead of counting down, can "count up"
from a given date. So the output should be something like "(xx) days
since (date/event)" or "(date/event) was (xx) days ago".
Does anybody know where to find a script like that, or could someone
code one for me?
Thanks in advance.
Yours,
|
by: Steve Claflin |
last post by:
I have a database with a moderate number of records in several tables
(the biggest table at the moment is about 800 records). In development
it got moved between 2K and XP repeatedly. Several days ago, it
wouldn't open, and proposed creating a backup before fixing. OK, but
the "fixed version" didn't open -- same messages as the original -- but
the backup did. But, in the 800 record table only 200 records
appeared. We went back several...
|
by: Jason |
last post by:
I have a table of 650,000 records associated to contracts. Some of
the contracts have multiple records but each contract only has one
Active Record (there might be several inactive records). There are
dates associated with each of the records (whether active or
inactive). I need to compare the dates between the active and
inactive contract records.
So far, I've created a "find duplicates query" for contract to
identify contracts that...
|
by: Gee |
last post by:
Hi Folks,
I have a small database that contains test dates. We can only sit 9
people per test. I need to count the dates that are the same and NOT go
over 9.
I read MSAcess help on the DCount function and on the Count function,
but I am still lost. It seems DCount will only work with VBA, and Count
is supposed to work in a macro.
I don't know VBA, so I can't use it for this purpose. As always, this
| |
by: jpr |
last post by:
Friends, I have a form with four fields, date1, date2, date3 and date4.
All these have all a mm/dd/yyyy format and have their source to a
table.
I need to add an unbound control (I will name it txtresult) which will
count in months and years the difference between the dates.
Ex.
date1: 01/01/1970
|
by: RussCRM |
last post by:
I need some help getting unique records from our database! I work for a small non-profit homeless shelter. We keep track of guest information as well as what services we have offered for statistical purposes.
I've been using
Here’s the situation:
I have two main tables:
Guest (stores data such as GuestID, First Name, Last Name, etc.) and
Services (stores data such as the type of service the guest used (Shelter Bed, Lunch, Dinner,...
|
by: Dr Al |
last post by:
I have a table with four date fields, some of which may not be filled
in based on our data entry needs. I have a criteria set as <date()-180
which is supposed to pull dates older than 180 days ago. The problem
is that when I use that criteria for all four fields I am not getting
the expected results.
I am trying to find out from this query is the date in date field one
is older than 180, same thing for the other three date fields. For...
|
by: sara |
last post by:
Hi -
I have had this problem MANY times and I just don't think I have the
best solution.
I am running a parameter query to retrieve records where work was
completed between 2 dates. The "completed date" field contains both
date and time (e.g., 11/4/07 15:44:00) and does need the time for the
data to be properly recorded.
That said, is it possible to format the parameter "Between and " to enable the user to enter just the
|
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: 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: 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 tapestry of website design and digital marketing. It's not merely about having a website; it's about crafting an immersive digital experience that captivates audiences and drives business growth.
The Art of Business Website Design
Your website is...
|
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: 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: 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: muto222 |
last post by:
How can i add a mobile payment intergratation into php mysql website.
| |