Interactive job sheet

[myersphil@gmail.com]

Hi, new to this group and new to Access.

I work for a water filtration company here in South Australia, our
customers are in MS Access.

Each day I am faxed a "Job Sheet" for the following days work with the
customers: Name: Address: Phone No.: and the Filter System they have.

Now my question:

Can the "Job Sheet" be generated by
1/ Going into the customers record
2/ Clicking on the Phone No (Its the only unique field)
And the record goes onto a "Job Sheet"
Then, the process is repeated until the "Job Sheet" is populated,
whether its with 2 jobs or 10 jobs?

[Allen Browne]

Re: Interactive job sheet

So you receive a file in some consistent format (same columns each day, in
CSV or XLS or something?)

You could set up a temporary table with the right columns (same names as any
heading in the file, Text type so everything can be imported.) Clear out
yesterday's data (delete query), and import today's (append query.)

You can then run a series of test to make sure the data is valid, e.g. all
required fields have a value, the phone numbers actually match existing
customers, and the fields contain the correct data types. You probably want
to create a form where the user can edit the records that aren't right.

Once the checking is all Go, you can JOIN the temp table to your customer
table, to get the CustomerID, and then Append the results to the real table.

--
Allen Browne - Microsoft MVP. Perth, Western Australia
Tips for Access users - http://allenbrowne.com/tips.html
Reply to group, rather than allenbrowne at mvps dot org.

<myersphil@gmai l.comwrote in message
news:4d3de371-6a88-4223-b903-8e64cd285d21@u1 0g2000prn.googl egroups.com...

Hi, new to this group and new to Access.
>
I work for a water filtration company here in South Australia, our
customers are in MS Access.
>
Each day I am faxed a "Job Sheet" for the following days work with the
customers: Name: Address: Phone No.: and the Filter System they have.
>
Now my question:
>
Can the "Job Sheet" be generated by
1/ Going into the customers record
2/ Clicking on the Phone No (Its the only unique field)
And the record goes onto a "Job Sheet"
Then, the process is repeated until the "Job Sheet" is populated,
whether its with 2 jobs or 10 jobs?

[David]

Re: Interactive job sheet

On Feb 29, 9:23 am, "myersp...@gmai l.com" <myersp...@gmai l.comwrote:

Hi, new to this group and new to Access.
>
I work for a water filtration company here in South Australia, our
customers are in MS Access.
>
Each day I am faxed a "Job Sheet" for the following days work with the
customers: Name: Address: Phone No.: and the Filter System they have.
>
Now my question:
>
Can the "Job Sheet" be generated by
1/ Going into the customers record
2/ Clicking on the Phone No (Its the only unique field)
      And the record goes onto a  "Job Sheet"
Then, the process is repeated until the "Job Sheet" is populated,
whether its with 2 jobs or 10 jobs?

Hi

You need to create a couple of tables in access. I would create a
table called something like tblCustomers with all the fields needed
for the details of the jobsheet ie Name, address, filter system name
etc- this acts as s permanent customer data table. I would set an
Autonumber field as a unique ID, and not recommend a telephone field.
Next a couple of related tables to hold the jobsheet records-
something like tblJobSheet, with an autonumber unique ID field
JobSheetID and then a field to mark the date of the jobsheet and
fields for any other relevant details. Then create another table-
tblJobSheetCust omers, which really mimics the tblCustomers fields, and
has the JobSheetID field- to be able to relate the two tables
together- 1 to many. Then basically you need to have a function on a
form that raises a new jobsheet record and selects the range of
customers to be incuded in the job- using the tblCustomers reference,
once selected the customers are copied across from the tblCustomers
into the tblJobSheetCust omers table. I would suggest doing this
through a list box control and using VBA and an add new sql statement
or call a recordset routine, or if that is a bit unfamiliar for you-
queries and macros would do it!

Good luck

David