Creating a visitor log based on a specific date out of date ranges

[AgentCopyKat]

My office would like to keep track of visitor access into our building and
many of the visitors we have tend to visit over a period of days and
sometimes are recurring on a monthly or yearly basis. I have been asked to
help the office transition from a shared excel file to access database.

On my main table with visitor information I have [Arrival Date] and
[Departure Date] headings to keep track of the visitor's stay. For example,
John Smith arrived on June 16 and will leave at the end of the week on June
20. However, when I create the report for the guard at the main desk, John
Smith only shows up on June 16th's Visitor Access Report - and he needs to
show up on June 17, 18, 19 and 20th's Access Reports in order to enter the
building.

Do I need to create a separate table for multiple dates and how do I
encorporate that into my main table, the forms I created for employees in the
building to submit their visitor's access request, and the reports for my
office and the guard at the main desk?

To throw in another wrench in to the multiple-days of entry situation, we
have several visitors who perform maintenance and need access every other
Monday. They all so need to show up on the access lists for those Mondays and
I would hate to enter or update their visitor's arrival and departure dates
in the table every other week.

Please let me know what additional information you need. Since I am not as
familiar with Access as I would like to be, I'd appreciate any help or
suggestions in trying to resolve this conundrum.

 

[Larry Daugherty]

A "log" is similar to a diary in that it records what has already
happened. You seem to be seeking a program generated daily list of
authorized visitors. That's a whole different kettle of fish!

If you are able to develop applications with Access then you might try
starting with one of the many templates that Microsoft offers and
modify it to suit.

The unpaid volunteers who respond to issues in these Access newsgroups
do so in the spirit of peer support. That usually comes down to a
single technical issue per thread although it might also concern a
concept or a strategy. If you are seeking a complete solution to the
issues you have started to list then you should be seeking it via paid
assistance.

If you intend to pursue the design yourself then I recommend lurking:

microsoft.public.access.tablesdesign and
microsoft.public.access.gettingstarted

a terrific site to visit for Access lore is www.mvps.org/access

HTH

 

[Allan Murphy]

I assume your "main" table has a list of visitors with their start and
finish dates?

You need a table called visitors which I call tbl_visitors.

Your input form has the visitor's name with their arrival date and departure
date, make this an unbound form. When the entry is saved you will need,
using code, add a record for each day of the visit to tbl_visitors. For this
you will need the date, determine the day of the week if it is Saturday or
Sunday do not add record.

For multiple entries you will need check boxes for each day of the week,
then use similar coding to check that the day of the date is the same as the
checked box so that the record can be added.

I have done something similar but not for ypur visitors scenario.

Allan

 

[AgentCopyKat]

I understand creating a separate table for the dates the visitor will be in
the building - after reading many of the related posts I realized this is
something I hadn't included yet. However, I have no idea where to start
looking for code that will create records for each date based solely on
arrival and departure dates. And how to I link this table to the main one I
have? My next concern is creating a query that will know to go to this table
to check if this person belongs on the daily access list. And our building is
open on Saturday and Sunday - some of our visitors need access during those
days so I will not need to worry about removing the weekend dates out of the
caluculations.

Thanks!

I assume your "main" table has a list of visitors with their start and
finish dates?

You need a table called visitors which I call tbl_visitors.

Your input form has the visitor's name with their arrival date and departure
date, make this an unbound form. When the entry is saved you will need,
using code, add a record for each day of the visit to tbl_visitors. For this
you will need the date, determine the day of the week if it is Saturday or
Sunday do not add record.

For multiple entries you will need check boxes for each day of the week,
then use similar coding to check that the day of the date is the same as the
checked box so that the record can be added.

I have done something similar but not for ypur visitors scenario.

Allan

My office would like to keep track of visitor access into our building and
many of the visitors we have tend to visit over a period of days and
sometimes are recurring on a monthly or yearly basis. I have been asked to
help the office transition from a shared excel file to access database.

On my main table with visitor information I have [Arrival Date] and
[Departure Date] headings to keep track of the visitor's stay. For
example,
John Smith arrived on June 16 and will leave at the end of the week on
June
20. However, when I create the report for the guard at the main desk, John
Smith only shows up on June 16th's Visitor Access Report - and he needs to
show up on June 17, 18, 19 and 20th's Access Reports in order to enter the
building.

Do I need to create a separate table for multiple dates and how do I
encorporate that into my main table, the forms I created for employees in
the
building to submit their visitor's access request, and the reports for my
office and the guard at the main desk?

To throw in another wrench in to the multiple-days of entry situation, we
have several visitors who perform maintenance and need access every other
Monday. They all so need to show up on the access lists for those Mondays
and
I would hate to enter or update their visitor's arrival and departure
dates
in the table every other week.

Please let me know what additional information you need. Since I am not as
familiar with Access as I would like to be, I'd appreciate any help or
suggestions in trying to resolve this conundrum.

 

[Allan Murphy]

If you require some help with the code please contact me on this email
address
(e-mail address removed)

Allan

I understand creating a separate table for the dates the visitor will be in
the building - after reading many of the related posts I realized this is
something I hadn't included yet. However, I have no idea where to start
looking for code that will create records for each date based solely on
arrival and departure dates. And how to I link this table to the main one
I
have? My next concern is creating a query that will know to go to this
table
to check if this person belongs on the daily access list. And our building
is
open on Saturday and Sunday - some of our visitors need access during
those
days so I will not need to worry about removing the weekend dates out of
the
caluculations.

Thanks!

I assume your "main" table has a list of visitors with their start and
finish dates?

You need a table called visitors which I call tbl_visitors.

Your input form has the visitor's name with their arrival date and
departure
date, make this an unbound form. When the entry is saved you will need,
using code, add a record for each day of the visit to tbl_visitors. For
this
you will need the date, determine the day of the week if it is Saturday
or
Sunday do not add record.

For multiple entries you will need check boxes for each day of the week,
then use similar coding to check that the day of the date is the same as
the
checked box so that the record can be added.

I have done something similar but not for ypur visitors scenario.

Allan

My office would like to keep track of visitor access into our building
and
many of the visitors we have tend to visit over a period of days and
sometimes are recurring on a monthly or yearly basis. I have been asked
to
help the office transition from a shared excel file to access database.

On my main table with visitor information I have [Arrival Date] and
[Departure Date] headings to keep track of the visitor's stay. For
example,
John Smith arrived on June 16 and will leave at the end of the week on
June
20. However, when I create the report for the guard at the main desk,
John
Smith only shows up on June 16th's Visitor Access Report - and he needs
to
show up on June 17, 18, 19 and 20th's Access Reports in order to enter
the
building.

Do I need to create a separate table for multiple dates and how do I
encorporate that into my main table, the forms I created for employees
in
the
building to submit their visitor's access request, and the reports for
my
office and the guard at the main desk?

To throw in another wrench in to the multiple-days of entry situation,
we
have several visitors who perform maintenance and need access every
other
Monday. They all so need to show up on the access lists for those
Mondays
and
I would hate to enter or update their visitor's arrival and departure
dates
in the table every other week.

Please let me know what additional information you need. Since I am not
as
familiar with Access as I would like to be, I'd appreciate any help or
suggestions in trying to resolve this conundrum.

 

[Larry Daugherty]

It is much better that issues are posted here and resolved here for
the benefit of the hundreds of lurkers who learn from the questions
and answers. That's the purpose of these newsgroups.

OK to take things off line if it could be lengthy and tedious but
these Access newsgroups should not be used for soliciting business nor
soliciting for paid consultants. There are many job/work venues for
that. As of the moment there is only one persistent troll in the
Access newsgroups who continually preys on folks with offers of
service.

I recommend reading "Netiquette" and many other topics on

www.mvps.org/access

For getting started with Access a couple of newsgroups to lurk:

microsoft.public.access.tablesdesign and
microsoft.public.access.gettingstarted

HTH
--
-Larry-
--

If you require some help with the code please contact me on this email
address
(e-mail address removed)

Allan

I understand creating a separate table for the dates the visitor will be in
the building - after reading many of the related posts I realized this is
something I hadn't included yet. However, I have no idea where to start
looking for code that will create records for each date based solely on
arrival and departure dates. And how to I link this table to the main one
I
have? My next concern is creating a query that will know to go to this
table
to check if this person belongs on the daily access list. And our building
is
open on Saturday and Sunday - some of our visitors need access during
those
days so I will not need to worry about removing the weekend dates out of
the
caluculations.

Thanks!

I assume your "main" table has a list of visitors with their start and
finish dates?

You need a table called visitors which I call tbl_visitors.

Your input form has the visitor's name with their arrival date and
departure
date, make this an unbound form. When the entry is saved you will need,
using code, add a record for each day of the visit to tbl_visitors. For
this
you will need the date, determine the day of the week if it is Saturday
or
Sunday do not add record.

For multiple entries you will need check boxes for each day of the week,
then use similar coding to check that the day of the date is the same as
the
checked box so that the record can be added.

I have done something similar but not for ypur visitors scenario.

Allan


My office would like to keep track of visitor access into our building
and
many of the visitors we have tend to visit over a period of days and
sometimes are recurring on a monthly or yearly basis. I have been asked
to
help the office transition from a shared excel file to access database.

On my main table with visitor information I have [Arrival Date] and
[Departure Date] headings to keep track of the visitor's stay. For
example,
John Smith arrived on June 16 and will leave at the end of the week on
June
20. However, when I create the report for the guard at the main desk,
John
Smith only shows up on June 16th's Visitor Access Report - and he needs
to
show up on June 17, 18, 19 and 20th's Access Reports in order to enter
the
building.

Do I need to create a separate table for multiple dates and how do I
encorporate that into my main table, the forms I created for employees
in
the
building to submit their visitor's access request, and the reports for
my
office and the guard at the main desk?

To throw in another wrench in to the multiple-days of entry situation,
we
have several visitors who perform maintenance and need access every
other
Monday. They all so need to show up on the access lists for those
Mondays
and
I would hate to enter or update their visitor's arrival and departure
dates
in the table every other week.

Please let me know what additional information you need. Since I am not
as
familiar with Access as I would like to be, I'd appreciate any help or
suggestions in trying to resolve this conundrum.

 

[Allan Murphy]

Larry

The reason that I am taking it off line is I deem it to be lengthy and
tedious. I am not one of those "lurkers".
If you prefer I will post my responses to the newsgroup there will be
LENGTHY coding and explanation.
Maybe you would like to add a solution to this request.

Allan

It is much better that issues are posted here and resolved here for
the benefit of the hundreds of lurkers who learn from the questions
and answers. That's the purpose of these newsgroups.

OK to take things off line if it could be lengthy and tedious but
these Access newsgroups should not be used for soliciting business nor
soliciting for paid consultants. There are many job/work venues for
that. As of the moment there is only one persistent troll in the
Access newsgroups who continually preys on folks with offers of
service.

I recommend reading "Netiquette" and many other topics on

www.mvps.org/access

For getting started with Access a couple of newsgroups to lurk:

microsoft.public.access.tablesdesign and
microsoft.public.access.gettingstarted

HTH
--
-Larry-
--

If you require some help with the code please contact me on this email
address
(e-mail address removed)

Allan

I understand creating a separate table for the dates the visitor will be in
the building - after reading many of the related posts I realized this is
something I hadn't included yet. However, I have no idea where to start
looking for code that will create records for each date based solely on
arrival and departure dates. And how to I link this table to the main one
I
have? My next concern is creating a query that will know to go to this
table
to check if this person belongs on the daily access list. And our building
is
open on Saturday and Sunday - some of our visitors need access during
those
days so I will not need to worry about removing the weekend dates out of
the
caluculations.

Thanks!

:

I assume your "main" table has a list of visitors with their start and
finish dates?

You need a table called visitors which I call tbl_visitors.

Your input form has the visitor's name with their arrival date and
departure
date, make this an unbound form. When the entry is saved you will need,
using code, add a record for each day of the visit to tbl_visitors. For
this
you will need the date, determine the day of the week if it is Saturday
or
Sunday do not add record.

For multiple entries you will need check boxes for each day of the week,
then use similar coding to check that the day of the date is the same as
the
checked box so that the record can be added.

I have done something similar but not for ypur visitors scenario.

Allan


My office would like to keep track of visitor access into our building
and
many of the visitors we have tend to visit over a period of days and
sometimes are recurring on a monthly or yearly basis. I have been asked
to
help the office transition from a shared excel file to access database.

On my main table with visitor information I have [Arrival Date] and
[Departure Date] headings to keep track of the visitor's stay. For
example,
John Smith arrived on June 16 and will leave at the end of the week on
June
20. However, when I create the report for the guard at the main desk,
John
Smith only shows up on June 16th's Visitor Access Report - and he needs
to
show up on June 17, 18, 19 and 20th's Access Reports in order to enter
the
building.

Do I need to create a separate table for multiple dates and how do I
encorporate that into my main table, the forms I created for employees
in
the
building to submit their visitor's access request, and the reports for
my
office and the guard at the main desk?

To throw in another wrench in to the multiple-days of entry situation,
we
have several visitors who perform maintenance and need access every
other
Monday. They all so need to show up on the access lists for those
Mondays
and
I would hate to enter or update their visitor's arrival and departure
dates
in the table every other week.

Please let me know what additional information you need. Since I am not
as
familiar with Access as I would like to be, I'd appreciate any help or
suggestions in trying to resolve this conundrum.

 

[Larry Daugherty]

"Lurking" is a good thing. That means just hanging around and reading
the posts. Trolling for business is a different kettle of fish.

No, I don't intend to hold the hand of a budding developer as s/he
endeavors to develop the basic knowledge to develop an Access
application. That developer has to do the learning and has to learn
that there are resources available such as Help files, these
newsgroups, lots of web based resources, especially Microsoft's.

Let that budding developer first try to work things out using the
resources available. Only after strategies or solutions have been
considered or tried should help be sought here. Once they do ask for
help with a specific issue it should be addressed here for the benefit
of the hundreds of lurkers who are trying to learn and become self
sufficient. Others may have similar questions and learn from the
answers. I believe it was Dev Ashish, the provider of The Access Web
(www.mvps.org/access) who had in his signature line "You post 'em
here, we'll answer 'em here". These newsgroups exist for purposes of
peer support, not for free programming nor for acquiring business.

HTH
--
-Larry-
--

Larry

The reason that I am taking it off line is I deem it to be lengthy and
tedious. I am not one of those "lurkers".
If you prefer I will post my responses to the newsgroup there will be
LENGTHY coding and explanation.
Maybe you would like to add a solution to this request.

Allan

It is much better that issues are posted here and resolved here for
the benefit of the hundreds of lurkers who learn from the questions
and answers. That's the purpose of these newsgroups.

OK to take things off line if it could be lengthy and tedious but
these Access newsgroups should not be used for soliciting business nor
soliciting for paid consultants. There are many job/work venues for
that. As of the moment there is only one persistent troll in the
Access newsgroups who continually preys on folks with offers of
service.

I recommend reading "Netiquette" and many other topics on

www.mvps.org/access

For getting started with Access a couple of newsgroups to lurk:

microsoft.public.access.tablesdesign and
microsoft.public.access.gettingstarted

HTH
--
-Larry-
--

If you require some help with the code please contact me on this email
address
(e-mail address removed)

Allan

I understand creating a separate table for the dates the visitor will be in
the building - after reading many of the related posts I

realized
this is

something I hadn't included yet. However, I have no idea where

to
start

looking for code that will create records for each date based solely on
arrival and departure dates. And how to I link this table to

the
main one

I
have? My next concern is creating a query that will know to go

to
this

table
to check if this person belongs on the daily access list. And

our
building

is
open on Saturday and Sunday - some of our visitors need access during
those
days so I will not need to worry about removing the weekend

dates
out of

the
caluculations.

Thanks!

:

I assume your "main" table has a list of visitors with their start and
finish dates?

You need a table called visitors which I call tbl_visitors.

Your input form has the visitor's name with their arrival date and
departure
date, make this an unbound form. When the entry is saved you

will
need,

using code, add a record for each day of the visit to tbl_visitors. For
this
you will need the date, determine the day of the week if it is Saturday
or
Sunday do not add record.

For multiple entries you will need check boxes for each day of the week,
then use similar coding to check that the day of the date is

the
same as

the
checked box so that the record can be added.

I have done something similar but not for ypur visitors scenario.

Allan


"AgentCopyKat" <[email protected]> wrote

in
message

My office would like to keep track of visitor access into

our
building

and
many of the visitors we have tend to visit over a period of days and
sometimes are recurring on a monthly or yearly basis. I have been asked
to
help the office transition from a shared excel file to

access
database.

On my main table with visitor information I have [Arrival

Date]
and

[Departure Date] headings to keep track of the visitor's

stay.
For

example,
John Smith arrived on June 16 and will leave at the end of

the
week on

June
20. However, when I create the report for the guard at the

main
desk,

John
Smith only shows up on June 16th's Visitor Access Report -

and
he needs

to
show up on June 17, 18, 19 and 20th's Access Reports in

order
to enter

the
building.

Do I need to create a separate table for multiple dates and

how
do I

encorporate that into my main table, the forms I created for employees
in
the
building to submit their visitor's access request, and the reports for
my
office and the guard at the main desk?

To throw in another wrench in to the multiple-days of entry situation,
we
have several visitors who perform maintenance and need

access
every

other
Monday. They all so need to show up on the access lists for those
Mondays
and
I would hate to enter or update their visitor's arrival and departure
dates
in the table every other week.

Please let me know what additional information you need.

Since
I am not

as
familiar with Access as I would like to be, I'd appreciate

any
help or

suggestions in trying to resolve this conundrum.