T
Ty
Okay, this a multi-tiered problem and I am not sure how much background
you need or want. I will just go through the whole story.
We have about 200 associates that have to keep their daily time
documented on a hard copy. We have a master form that was made in excel.
I made a separate excel file that had the list of associate names and
associate number. I had an vba macro that would print out each name and
associate number. It works fine, however it is very slow, it takes about
30-45 seconds per associate and it has to poll each name, populate the
field, then print.
So I converted the master form into word format and worked on a
mailmerge method. It works great and is a lot faster. Right now, the
secretary manually updates the dates on the master form every week. So I
had worked on another excel spreadsheet that has all of the dates for
fiscal and calendar year. That part worked great too, she could filter
the dates and it would populate. Then after all that I find out that
mailmerge will not work with multiple sources. You can only have one
source at a time. As I was researching this, I found out there was a
workaround. You could have as many sources as you want, if you link them
in access and create a query that shows each of them at the same time.
Then in mailmerge, you'd point to that query.
I do not know access very well at all and I can't seem to get it to
filter right.
To summarize, in access I have two linked excel spreadsheets. One is
names.xls and the other is calendar.xls. All I want is to create a query
that displays both of them, side by side. When I do the link to the
calendar, it doesn't have an option to do the one already filtered, so I
will have to do it within access.
I am in Office 2010. I go to "Create - Query Design" I add both of the
tables and double click on all of the fields I will need to use:
calendar table:
day of month - (no filter)
cal year - (2011)
cal day of week - (7)
fisc pd - (03)
month abbrev - (no filter)
names:
name - (no filter)
I believe one problem maybe that the numbers are really text, so maybe
that is why it is not filtering.
Here is a screenshot of what I have in design view and what I have set
as the criteria:
http://i56.tinypic.com/2s1x1k7.png
When I run the query, it appears that the only one that actually filters
is the day of week. I am getting multiple years and multiple periods.
When I apply the filters in excel, this is what it looks like:
http://i56.tinypic.com/m9ceqf.png
Sorry if I made this post overly complicated or wordy. Just trying to
get it all out there.
If I can get it to look right in access, I am sure I can handle the
parts in word/mail merge myself.
you need or want. I will just go through the whole story.
We have about 200 associates that have to keep their daily time
documented on a hard copy. We have a master form that was made in excel.
I made a separate excel file that had the list of associate names and
associate number. I had an vba macro that would print out each name and
associate number. It works fine, however it is very slow, it takes about
30-45 seconds per associate and it has to poll each name, populate the
field, then print.
So I converted the master form into word format and worked on a
mailmerge method. It works great and is a lot faster. Right now, the
secretary manually updates the dates on the master form every week. So I
had worked on another excel spreadsheet that has all of the dates for
fiscal and calendar year. That part worked great too, she could filter
the dates and it would populate. Then after all that I find out that
mailmerge will not work with multiple sources. You can only have one
source at a time. As I was researching this, I found out there was a
workaround. You could have as many sources as you want, if you link them
in access and create a query that shows each of them at the same time.
Then in mailmerge, you'd point to that query.
I do not know access very well at all and I can't seem to get it to
filter right.
To summarize, in access I have two linked excel spreadsheets. One is
names.xls and the other is calendar.xls. All I want is to create a query
that displays both of them, side by side. When I do the link to the
calendar, it doesn't have an option to do the one already filtered, so I
will have to do it within access.
I am in Office 2010. I go to "Create - Query Design" I add both of the
tables and double click on all of the fields I will need to use:
calendar table:
day of month - (no filter)
cal year - (2011)
cal day of week - (7)
fisc pd - (03)
month abbrev - (no filter)
names:
name - (no filter)
I believe one problem maybe that the numbers are really text, so maybe
that is why it is not filtering.
Here is a screenshot of what I have in design view and what I have set
as the criteria:
http://i56.tinypic.com/2s1x1k7.png
When I run the query, it appears that the only one that actually filters
is the day of week. I am getting multiple years and multiple periods.
When I apply the filters in excel, this is what it looks like:
http://i56.tinypic.com/m9ceqf.png
Sorry if I made this post overly complicated or wordy. Just trying to
get it all out there.
If I can get it to look right in access, I am sure I can handle the
parts in word/mail merge myself.