Macro question

B

Bianca

I am a newbie at this so please be kind!

I have a table with a name field, an issuing date field, a payment date
field, and a valid or non valid field (we issue discount cards).

I made a query with date from - to for the issuing date field and a valid
for the valid or non valid field
I made a second query for the payment date (same as the issuing date info)

first question can I make just one query for both of these?

Second question how can I get it to show me the count automatically - the
number of cards for each month and then to take this total and multiply it by
and euro amount to give me the sales we made?

I don’t want all the information to show up (meaning the cardholders name
etc) just the total number of cards sold and the euro amount.
 
J

John Spencer

Your table description is incomplete.
Guessing that you have at least the following fields
CustomerName
IssueDate
PaymentDate
Valid
Amount

First question:
You can make a query that returns data for a date range based on IssueDate
and on PaymentDate. The assumption is that you want records where either
the IssueDate or the PaymentDate is between the specified dates.

Field: IssueDate
Criteria (Line1): Between [start date] and [end date]
Criteria (Line2): << BLANK >>

Field: PaymentDate
Criteria (Line1): << BLANK >>
Criteria (Line2): Between [start date] and [end date]

Second Question:
Use a totals query
-- Open a new query
-- Select your table
-- Select PaymentDate, PaymentDate, Valid, Amount as fields
-- Change PaymentDate (1) to Format([PaymentDate],"yyyymm")
-- Select View: Totals from the menu
-- Change GROUP BY to Count under PaymentDate (2)
-- Change GROUP BY to SUM under Amount
-- Change Group By to WHERE under valid and set the criteria to Valid

--
John Spencer
Access MVP 2002-2005, 2007
Center for Health Program Development and Management
University of Maryland Baltimore County
..
 
Top