How to count same value as 1 in a query

[Fernando@Sartorius]

Good day to all who read this post. I am in need of assistance. I am trying
to create a query in which I need to state that This X Material was purchased
Y amount of times during a year, but if the products was bought multiple
days, I want the query to count all those values from that same date as 1.

These are my Fields:
Mat 1st Date Purch. Last Day Purch. # of times bought # of days
product was bought.

Can anyone provide me some feedback? Thanks in advanced.

 

[Michel Walsh]

Make a query that will group by product AND by date, next, make a second
query that will be based on the previous query, but this last query will
group by product and count the number of records:


q1:
SELECT product, theDate
FROM somewhere
GROUP BY product, theDate


q2:
SELECT product, COUNT(*)
FROM q1
GROUP BY product



Vanderghast, Access MVP

 

[Fernando@Sartorius]

Thank you for your inout Michel, but I am having a bit of a problem. I am an
novice on Access, and the Select function I believe is SQL, correct?

Maybe I should I have been more specific about what I am looking for. Here's
an example.

Product: iPod
Dates Purchased: 1/12/09 1/12/09 2/12/09 2/12/09 3/1/09 3/13/09
Total of Days iPod was purchased: 4

I need for the same values under dates to count as 1 when the iPod is
purchased on the same date.

Hope I'm clear this time. I apologize about any confusion I'm causing with
this. And again, thanks.

 

[Fernando@Sartorius]

Michel, I was able to understand what you were trying to explain to me in SQL
code by reading the data out of SQL View (sorry, I'm a newb). Still, the
product of your data gives me the same as creating a count of products, but
what I need is the amount of days that this product was actually bought in,
disregarding the actual amount of products sold.

 

[Michel Walsh]

I don't understand, the last query should return the number of days PER
product, NOT the count (number) of product (per date, or otherwise).

In the graphical environment, the first query involves only your table, and
it is a Total query, where you just GROUP BY on the two fields, product and
theDate. I assume that the theDate field holds only a date (and no time
other than 00:00:00).

The second query is also a Total query, but now, it involves the previous
query (which removed any duplicated occurrence, per day) and now, GROUP BY
on product and COUNT(*) or COUNT on theDay.


If your date field contains also an hour, change the first query to:


SELECT product, DateValue(theDate)
FROM somewhere
GROUP BY product, DateValue(theDate)


Vanderghast, Access MVP