Quick Beginner's question

G

Gary Hillerson

I created a table of records with three fields: Date, Counter, Sales.
Each records a pair of values for a certain date: 1) the hit counter
value for a web page, and 2) the salesID number of the last product
sale on that date.

I can create a report that shows me all of the values. But I don't
know the best way to pop up a question and then retrieve the data to
answer a query something like:
"How many sales and how many hits have occurred since date XX?"

I'm very familiar with VBA (for Word), so I can write a macro and
figure out any Access-specific stuff I need to work with. But is there
a built-in way to create that query without writing code?

thanks,
gary hillerson
 
D

Damien McBain

You can use a form with a textbox (or textboxes) and command button. set the
criteria in your underlying query (or the filter on your report) to
something like:
BETWEEN txtDate1 And txtDate2
make the command button's OnClick event run the report
DoCmd OpenReport "MyReport" etc etc
The command button wizard can help you create this code then you can edit it
later. You also can specify a "where condition"
[Date] >= Forms!frmMyForm!txtDate1 and [Date] <= Forms!frmMyForm!txtDate1
in the OpenReport command. I prefer to do it this way rather than set the
criteria in the query so I can use the same query for a number of differnt
reports based on the same data. I don't know which is more programatically
efficient.

Damo
 
A

Albert D. Kallal

Ok, you will need a bit of code...but not too much.

I assuming we have:

Date Counter Sales
----- ---------- ----------

It is not clear if you have multiple dates for a given day (I am assuming
yes).

I also assume that you are going to send the results of your "question" to a
report....right??

Also, do you want to report to give just the totals...or do you want totals
for each day?

I would first build a query for the report. Fire up the query builder,
select the sales table, and then
Drop in the above 3 fields into the grid. Whack the "sum" button at the top.
Leave Date as group by, and other fields as "sum"

save the query...and even run it to take a quick peek to see how it looks...

Now, create a report based on this query. (you can use the wizard). Since
the query is summing by each day..then you ONLY need to sort the report by
date...but don't need any of the sorting and "grouping" options that a
report has. You might add some total fields for counter and sales.

Try the report out....

Now, we are ready to build a nice cute form that asks the users the starting
date. Heck, you might as well ask the ending date also (default it today).

So, on this cute form we simply drop in two text boxes. These two text boxes
will allow us to ask the user for a start and end date.

Call the first text box txtStartDate, and the 2nd textBox txtEndDate

We also will put a button on the form that when pressed will launch the
above report..and RESTRICT the report to the given date range.

The code behind the button will look like:

dim strWhere as string

strWhere = "[Date] Between #" & format(txtStartDate,"mm/dd/yyyy") & "#" & _
" and #" & format(txtEndDate,"mm/dd/yyyy") & "#"

docmd.OpenReport "YourReport",acViewPreview,,strWhere

The above is all you need (two lines of code!).

Feel free to ask for more info on the above idea....

If you want to see some screen shots of the exact idea in action...take a
look at these..
http://www.attcanada.net/~kallal.msn/ridesrpt/ridesrpt.html
 
D

Dirk Goldgar

Gary Hillerson said:
I created a table of records with three fields: Date, Counter, Sales.
Each records a pair of values for a certain date: 1) the hit counter
value for a web page, and 2) the salesID number of the last product
sale on that date.

I can create a report that shows me all of the values. But I don't
know the best way to pop up a question and then retrieve the data to
answer a query something like:
"How many sales and how many hits have occurred since date XX?"

I'm very familiar with VBA (for Word), so I can write a macro and
figure out any Access-specific stuff I need to work with. But is there
a built-in way to create that query without writing code?

thanks,
gary hillerson
Click to expand...

Use the query designer: at the Queries tab of the database window,
double-click "Create query in Design view". In the Add Table dialog,
choose your table and click Add, then Close. The table will appear in
the upper pane of the design window. From there, drag all three fields
to the field grid in the bottom pane and drop them there. Under the
Date field (bad name for a field, by the way), uncheck the "Show" check
box, and on the Criteria line in that column enter
=[Enter start date]
Click to expand...

Click the Totals button on the toolbar (the caption is a capital sigma).
The Totals: line will appear in the field grid. On that line, change
the value under the Date field to "Where" and change the value under the
Counter field to "Sum". I'm not sure what you want there for the Sales
field. If the field will be Null if there is no sale on that date, and
non-Null if there was a sale, then you probably want to put "Count" on
the Totals: line under that field. That will give you a count of the
dates that have at least on sale.

Flip this query into datasheet view, answer the parameter prompt with a
date, and you should get something like the answers you're looking for.
 
G

Gary Hillerson

Great help....Thanks. Now I just need some basic Access structural
understanding and I can do what I want.

Now that I know how to grab the records for a range of dates and then
run a report on that range. What I don't know how to do is access a
specific value in a specific record.

So I can ask the user and get a start date and an end date, and then I
can use where to select the records in my database for all dates in
that range.

Now I need to simply grab the counter value from the first record in
the range, and the counter value from the last record in the range and
display their difference (EndDateRecord.counter -
StartDateRecord.counter). Similarly with my sales values.

I'm not clear on how to access the first and last records in my "set"
and I'm not clear on how to access specific fields in those specific
records.

sorry for such elementary questions
 
Top