Customer Count Database

[Mr-Re Man]

Hi, I am in the process of trying to devise a very simple database that tells
me how many visitors we have in each day for each particular department we
serve. It needs to be very simple and quick to use as the reception area is
very busy.

I'm thinking of a form that automatically updates a date field with the
current days date and time (which I can do) but then I want a series of
buttons (with departments names on) that increment by one each time they are
pressed by our receptionists (I don't know how to do this part)

Then hopefully, at the end of each day I'll have some kind of report that
that tells me how many visitors we had and between what time bands they came
in.

If anybody can share some experience on best practice/tips/code or know of a
similar database available that I could have, it would be most appreciated.

 

[Klatuu]

This will be very simple and very fast for the reception people to use.
Create a form that has a command button for each department. In the form
module, put a sub like this:

Private Sub AddVisitor(VisitDept As String)
Dim strSQL As String

On Error GoTo AddVisitor_Error

strSQL = "INSERT INTO tblDeptVisits ( Dept, VisitTime ) " & _
"SELECT """ & VisitCity & """ AS Expr1, #" & Now() & "# AS Expr2;"
CurrentDb.Execute strSQL, dbFailOnError

AddVisitor_Exit:
On Error GoTo 0

Exit Sub

AddVisitor_Error:

MsgBox "Error " & Err.Number & " (" & Err.Description & _
") in procedure AddVisitor of VBA Document Form_Form4"
GoTo AddVisitor_Exit

End Sub

Now, for each command button, you will call the sub with the name of the
department like this:
Private Sub Command0_Click()
Call AddVisitor("PeanutButter")
End Sub

So all the user has to do is click the button once for each vistor to the
department. It will add a record to the table. The table will need two
fields. A text field for the department name and a Date/Time field for the
visit.

You will have to modify the code to use your table and field names.

Rather than doing the count in the table, use forms or reports to display
the counts by department and time period. You will usually do this using
totals queries.

 

[Mr-Re Man]

Thank you for the fast response, its was as fast as your solution.

Excuse my typing in capitals, it is only to ask a question based on your
response.

This will be very simple and very fast for the reception people to use.
Create a form that has a command button for each department. In the form
module, put a sub like this:

WHAT IS A FORM MODULE, IS THIS A LINK IN THE FORM WITH THE BUTTONS I JUST
CREATED OR SOMEWHERE ELSE?

Private Sub AddVisitor(VisitDept As String)
Dim strSQL As String

On Error GoTo AddVisitor_Error

strSQL = "INSERT INTO tblDeptVisits ( Dept, VisitTime ) " & _
"SELECT """ & VisitCity & """ AS Expr1, #" & Now() & "# AS Expr2;"
CurrentDb.Execute strSQL, dbFailOnError

AddVisitor_Exit:
On Error GoTo 0

Exit Sub

AddVisitor_Error:

MsgBox "Error " & Err.Number & " (" & Err.Description & _
") in procedure AddVisitor of VBA Document Form_Form4"
GoTo AddVisitor_Exit

End Sub

Now, for each command button, you will call the sub with the name of the
department like this:
Private Sub Command0_Click()
Call AddVisitor("PeanutButter")
End Sub

I HAVE PUT THE ABOVE CODE IN MODULES AND CALLED IT MODcount, IS THAT
CORRECT?

So all the user has to do is click the button once for each vistor to the
department. It will add a record to the table.
HOW?

The table will need two
fields. A text field for the department name and a Date/Time field for the
visit.

DOES THE TABLE LITERALLY HAVE TWO FIELDS SET-UP? ANY KEY FIELD?

 

[Klatuu]

--
Dave Hargis, Microsoft Access MVP

Thank you for the fast response, its was as fast as your solution.

Excuse my typing in capitals, it is only to ask a question based on your
response.


WHAT IS A FORM MODULE, IS THIS A LINK IN THE FORM WITH THE BUTTONS I JUST
CREATED OR SOMEWHERE ELSE?

A form module is the module that is attached to the form. When you click on
the Code Builder option in the event procedure, it opens the form's module in
the VB Edtior. Nothing magic about it. So, the answer is yes.

I HAVE PUT THE ABOVE CODE IN MODULES AND CALLED IT MODcount, IS THAT
CORRECT?

That will do. You could just paste it into the form's code. You would put
it below the Option statments but before any event procedures.

HOW?

That is what the code does in the sub I provided. It builds an SQL statment
to create a new record in your table with the name of the department and the
current date and time. Then the CurrentDb.Execute statment executes the SQL
and adds the record to the table.

DOES THE TABLE LITERALLY HAVE TWO FIELDS SET-UP? ANY KEY FIELD?

That is up to you. I would suggest you add a primary key field. I would
use an autonumber field and designate it as the primary key field.
Glad to help. Post back if you need additional assistance.

 

[Mr-Re Man]

I think I'm getting there, I have a table called tblDeptVisits with 3 fields
("ID" as an autonumber, "Dept" as Text and "VisitTime" as Date/Time)

My form is called Form1 with two buttons, 1 called cmdEE the other cmdPER,
both have an onClick event that states:

Private Sub cmdEE_Click()
Call AddVisitor("EE") and one for PER
End Sub

My Module is called MODcount and it has the information you provided me, not
sure if I have to change the text VisitCity, Expr1 and Expr2 in the code to
reflect something in my database.

However, I left it as it is and opened the form and clicked a button, to
which I received the followign error. It opened the coding screen and
indicated a compile error, sub or function not defined and within the code
(below) Cal AddVisitor was shaded in grey.

Private Sub cmdEE_Click()
Call AddVisitor("EE")
End Sub

I guess I have left off something crucial.

thanks again for your time

 

[Klatuu]

The function I sent is defined as a Private sub because I had it in the form
I built for testing. Since you have it in a separate module, change Private
to Public:
Public Sub AddVisitor(VisitDept As String)

Expr1 and Expr2 do not have to be changed. They are actually just place
holders to satisfy SQL syntax requirements.

 

[Mr-Re Man]

We're just about there, no errors popping up, the only detail is the
department (EE or PER) not populating the table.

I can taste success and a pat on the back for us!

 

[Mr-Re Man]

I did it, I changed the VisitCity in the Module code to VisitDept and it works.

Ace, you did it, thank you so much, you said it was simple..it was.

Amazing.

 

[Klatuu]

Glad I could help.

Now, you will probably want to be able to report on this. To get the
reporting you need, you will want to use Sorting and Grouping in your report
to get the counts by time period. Post back if you need help with that.

 

[Mr-Re Man]

I'm trying to expand the answer in the module to also pick up the overall
depaertment. I changed the code to (below) where VisitSA was the additional
code. I updated my buttons oclick code to Call AddVisitor("Building
Control", "PER")

Public Sub AddVisitor(VisitDept, VisitSA As String)
Dim strSQL As String

On Error GoTo AddVisitor_Error

strSQL = "INSERT INTO tblDeptVisits (Dept, SA, VisitTime ) " & _
"SELECT """ & VisitDept & """ AS Expr1, #" & VisitSA & """ AS
Expr2, #" & Now() & "# AS Expr3;"
CurrentDb.Execute strSQL, dbFailOnError

....plus all the rest

I now get this error message when i click a button to record the details.
error 3075 (syntax error (missing operator) in query expression '#PER" AS
Expr2, #04/06/2008 11:53:57# AS Expr3;'.) in procedure AddVisitor of VBA
Form_Form4

Would you mind taking a look and letting me know what adjustment I need to
make.

I have managed to design a report, so thats all good

 

[Klatuu]

Since VisitSA is a string and I assume SA is a text field, your syntax is
incorrect. You have it coded is if it were a date field on the left side,
but as text on the right side. This should fix it.

IN Jet SQL, the delimters are:
For Numbers - No Delimiters
For Text - Single or Double Quotes.
For Dates - #

strSQL = "INSERT INTO tblDeptVisits (Dept, SA, VisitTime ) " & _
"SELECT """ & VisitDept & """ AS Expr1, """ & VisitSA & """ AS
Expr2, #" & Now() & "# AS Expr3;"

 

[Mr-Re Man]

Once again, thank you for the help - I think we have this one nailed now!

 

[Mr-Re Man]

One final thing I promise, I only just noticed that the date from the module
is updating the table in US format, how do I change this to UK? I did try
making the date field in the tables Format Properties to dd/mm/yyyy. But
nothing altered.

 

[Klatuu]

Date fields are not stored in any format. They are actuall floating point
decimal numbers where the whole number represents the date and the decimal
part represents the time. How they are displayed depends first on the
regional settings on your computer and then on how you format them.

 

[Mr-Re Man]

I've been trying a few different options to try to resolve the date issue.

So today, in the table I changed the date field to be a numeric field so it
gives me a number. I changed todays date on my pc as the 6/6/08 wouldn't
really tell me anything, so when I clicked on a button and it populated the
table, the numeric string I got was 39635. I copied this into excel and
changed it into a date and it came up as 6 July 2008.

All my other dates on the pc seem to be working fine, would you have any
further ideas as to what i can do as it will make the reports meaningless
when viewing stats between two different dates.

This is a strangeone, that has me stumped.

 

[Klatuu]

It does seem strange, but without it in front of me to experiment with, I
don't know what I can do to help. If it is just the one date, I would
suspect there is something different about the control where you are
displaying it or something in the field definition that is causing the
problem.

 

[Mr-Re Man]

Do you think this code would help,

Function SQLDate(vDate As Variant) As String
If IsDate(vDate) Then
SQLDate = "#" & Format$(vDate, "mm\/dd\/yyyy") & "#"
End If
End Function

It was taken from a website that indicates that dates in SQL seem to stick
to American formats and this code above should allow for other countries,
namely UK to capture the correct format.

I have tried to call it from the module you gave me, but wasn't sure how or
if it would work.

If I was in America this thread would have finished a long time ago

 

[Klatuu]

That format is for a US date, don't you want "dd\/mm\/yyyy"?
Where do you want to present the results?

 

[Mr-Re Man]

The module you gave me here (part of)..

Subject: RE: Customer Count Database 6/2/2008 9:53 AM PST

By: Klatuu In: microsoft.public.access.tablesdbdesign


--
Dave Hargis, Microsoft Access MVP

Thank you for the fast response, its was as fast as your solution.

Excuse my typing in capitals, it is only to ask a question based on your
response.


WHAT IS A FORM MODULE, IS THIS A LINK IN THE FORM WITH THE BUTTONS I JUST
CREATED OR SOMEWHERE ELSE?

A form module is the module that is attached to the form. When you click on
the Code Builder option in the event procedure, it opens the form's module in
the VB Edtior. Nothing magic about it. So, the answer is yes.
The Now(), that is what i guess is making the date do strange things.

The results then populate a table directly.

 

[Mr-Re Man]

The module you gave me here (part of)..

Private Sub AddVisitor(VisitDept As String)
Dim strSQL As String

On Error GoTo AddVisitor_Error

strSQL = "INSERT INTO tblDeptVisits ( Dept, VisitTime ) " & _
"SELECT """ & VisitCity & """ AS Expr1, #" & Now() & "# AS Expr2;"
CurrentDb.Execute strSQL, dbFailOnError

The Now(), that is what i guess is making the date do strange things.

The results then populate a table directly.