Printing only selected records

[Bob Quintal]

Version: Access 2000

My company issues an updated telephone list monthly with
hundreds of employees.

So far I copy the original telephone list into a "clean" excel
sheet. Then I create a linked table in my database so that my
table is always updated. Table Name "TelephoneList"

Goal: Create and print a report for the employees that I want.
I don't need a 5 page telephone sheet. I want to be able to
just check a box besides the names that I want. And of course
every time the table is updated I don't want to recheck the
same employees

But I'm having trouble finding an easy solution to only print
the records that I want.

I could easily add a yes/no field in the table, but I can't
because it's linked.

Am I missing a simple solution???

Thanks

Dan

Simple as 1, 2, 3.....

First import the table to the database. Go into design view and
delete all the columns except the one that uniquely identifies
each employee. Add the checkbox column (a Yes/No type field)

1) Create a delete query that removes only the rows that exist
in the imported table.but not in the spreadsheet

2) Create an Insert query that adds the rows that exist in the
spreadsheet but not in the local table.

3) create a query that looks at the data in the spreadsheet plus
the yes/no field in the local table to use as your report's
source.

Now when yhe new spreadsheet comes out, just run the three
queries in order.

 

[kdaniel7979]

Version: Access 2000

My company issues an updated telephone list monthly with hundreds of
employees.

So far I copy the original telephone list into a "clean" excel sheet.
Then I create a linked table in my database so that my table is always
updated. Table Name "TelephoneList"

Goal: Create and print a report for the employees that I want. I don't
need a 5 page telephone sheet. I want to be able to just check a box
besides the names that I want. And of course every time the table is
updated I don't want to recheck the same employees

But I'm having trouble finding an easy solution to only print the
records that I want.

I could easily add a yes/no field in the table, but I can't because
it's linked.

Am I missing a simple solution???

Thanks

Dan

 

[krissco]

Dan,

You can store your choices within a table in Access and join with the
Excel file.

First, create a table in Access with fields that suit your needs.
Possibly: (Employee [Text], Include [Yes/No]). You may also want to
set the default value for the "Include" field.

Second, insert a record for each employee in your excel file - this
will only need to be done once, or as employees change. You may or may
not need to specify "distinct":

insert into {Name of Access table} (Employee)
select distinct {Name of employee field in Excel file}
from TelephoneList

Now open the Access table and check the boxes as you see fit.

For your report, change its recordsource to something like:
select TelephoneList.*
from TelephoneList, {Name of Access table}
where Telephonelist.{Name of employee field in Excel file} = {Name of
Access table}.Employee
and Include = Yes


-Kris

 

[Albert D. Kallal]

Am I missing a simple solution???

Is there any unique record id associated with each name in the excel sheet
issued to you?

As the other poster mentioned, you simply build a "list" of id (names) in
ms-access. So, when you link to this new sheet, you still have that list.
The only detail is what field or fields do you use to establish/identify a
unique record.

Using the name field likely would not work due to name correcting, and
duplicates.

 

[kdaniel7979]

Thanks for the start. I should mention that my skills in Access are
average.

First, create a table in Access with fields that suit your needs.
Possibly: (Employee [Text], Include [Yes/No]). You may also want to
set the default value for the "Include" field.

Ok Done

Second, insert a record for each employee in your excel file - this
will only need to be done once, or as employees change. You may or may
not need to specify "distinct":

insert into {Name of Access table} (Employee)
select distinct {Name of employee field in Excel file}
from TelephoneList

Need a hint here. How do I insert the records? Do I need to set up a
macro?

Thanks

 

[kdaniel7979]

Is there any unique record id associated with each name in the excel sheet
issued to you?

As the other poster mentioned, you simply build a "list" of id (names) in
ms-access. So, when you link to this new sheet, you still have that list.
The only detail is what field or fields do you use to establish/identify a
unique record.

Using the name field likely would not work due to name correcting, and
duplicates.

No there is no simle unique id. I was thinking of combining the first
and last name to get a unique ID.

 

[John W. Vinson]

No there is no simle unique id. I was thinking of combining the first
and last name to get a unique ID.

I know three gentlemen named Fred Brown.
I once worked with Dr. Lawrence David Wise, Ph.D. and his colleague, Dr.
Lawrence David Wise, Ph.D.

Names are NOT unique. Surely if this is a table of employees, your Human
Resources department has some sort of employee number?

You can append the records into a new table containing an Autonumber ID to
have Access assign one.

John W. Vinson [MVP]