Is this could be done using the Access ??

[Guest]

Hello everyone.

I have table in the Access Database, which is updated almost everyday. Lets
name this table as Table I

What I want to do is to create a form, which will following fields

PART I

ID Number > ########### ( 12 Digit Number ) ( Primary key )
Name > Text
Date > Date
Sent To > Text
Leader / Manager > Text

PART II
Date Called > Date
Caller > Text
Tries > Number
Product 1 > Check Box
Product 2 > Check Box
Product 3 > Check Box
Product 4 > Check Box
Product 5 > Check Box
Product 6 > Check Box
Product 7 > Check Box
Product 8 > Check Box
Product 9 > Check Box

Part I, is the fields, where I want to fill in the Id Number, and want the
other fields of PART I to be auto populate as per the record, associated with
it in the Table I from the Database .

Part II fields are filled in after the data is searched from the Table I
using this form. Once both Part I and Part II fields are filled, this
information should be saved into the Table II, which is the new table and
going to be filled with the data of both Part I and Part II of the form.


Please let me know if this could be done using the Microsoft Access 2002.

Any help will be appreciated.

Thanks

noble

 

[Bill Mosca, MS Access MVP]

Yes, it can be done in Access (any version), but you need to understand how
a relational database should be designed.

Just going by what you posted, you have a field for each product when you
should have 2 tables: one for the product and one that contains a field with
the product ID and other fields to store whatever data you want to relate to
that product.

Take a look at the Northwind sample database that ships with Access. While
it is not perfect, it should give you a better idea of how to accomplish
your task.

 

[Guest]

Thanks Bill for your reply,

I will go in a bit more detail on this, which might gave better view of the
database structure and possible ways to resolve the problem.

The Table I contains the following columns

Project ID > "##########" 12 Digit Number
Project Name
Project Date
Valuation
Project City
Owner
Owner City
Our District > These are the imaginary areas defined by the District
names
Sub Branch > These are the branches under the Districts above
Sent To > Notification sent to
Project Leader > Project leader responsible for this project
Sent On > Date
Proposed Product 1 > Check box
Proposed Product 2 > Check Box
..
..

Proposed Product 6 > Check Box

The Proposed Products are listed as the check boxes, right now the person
first enter the data into an excell sheet, and then these excell sheets from
4 people is hand overed to the guy who is responsible for adding the
information into the database, all by copy pasting the fields, and checking
up the check boxes for the associated products listed with the Project ID.

The table 1 is filled with the proposed data of a project, and the table 2
will have the fields, that will contain the data of the products which were
actually sold to the customer, ( This could include the proposed products,
but may not all of them )

Hence there is a need of a new database table, table 2 whose job is to have
the entries

ID Number > ########### ( 12 Digit Number ) ( Primary key )
Name > Text
Date > Date
Sent To > Text
Leader / Manager > Text
--------This is to track the
process---------

Date Called > Date
Called whom ? > Text
number of Tries > Number

--------Actual Products sold
-----------
Project 1 > Check Box
Project 2 > Check Box
..
..
Project 6 > Check Box

The table 2, is needed to get filled up using the form, which will search
for the Project ID from table 1 and autopopulate the fields which are
associated with it, as described by the PART 1 of the Form, and then show the
other Actual products sold, as the check boxes, which are filled by the
operator while contacting the owner. Then the last step will be adding all
the information filled up on the form to table 2.

I do understand that there might be different way to design this. The
previous database was not designed by me. what are the possible ways to
better construct the database and solve the problem I am facing.

Thanks again for your time.

noble