What tables do I need

  • Thread starter Thread starter Guest
  • Start date Start date
G

Guest

Good evening all!
I have a very simple database comprising of two tables:

tbl-AddNew has RequestID (primary key- AutoNumber) - Request and Solution
fields

tbl-RequestsandSolutions has TopicID as Primary key-AutoNumber)

here is my problem!

I want to make this database searchable by Topic.
Topic being Printer-Monitor-Scanner-Hardware-Software etc....

So I may add new Requests and Solutions to my database but assign them to
the topic they relate to. EG: This example would need to be stored in the
Printer topic

Request is 'My printer is not printing'
Solution 'Check the power supply'

If this was to be the case then when I wish to search the database, I may do
so by looking up Printers as in the example

Naturally I have set up forms which is where both the Adding of new data and
the searching of existing data will be carried out from.


I am sure this is so simple that I will feel like a fool but I am buggered
if I can work out how to establish the correct tables to do this.

All guidence appreciated
Cheers Ohbe
 
I realize that you've already invested time in your current application, but
your table structure appears to be inadequate; the "tblAddNew" is not needed
(most every data table has records added to it over time) and your other
table needs to be normalized to at least three components.

Look at the built-in "Service Call Management" database template that Access
has available for guidance.

To answer your question specifically, you have to have a "topic" field in
your table, so that there are values to search on. Create a lookup table of
typical topic values. Then create a topic field in your current table (
though I really recommend re-designing this whole thing) and add a combo-box
on your forms. Make the comboxbox control bound to the topic field, and
make the tblTopics (that you added above) the datasource of that combobox.
Good luck
-Ed
 
Hi Ed,
Thank you for your time.
I am more than happy to start from scatch as you reccommend.

Am I right to understand you as saying I should have two tables?

Table One for recording the Requests and Solutions and also for adding new
records
Table Two for the topics--I estimate there will be approximately 30 topics

and then I should create a form based on Table one but add a combo box to it
that looks up the list of topics from Table Two

Is that correct??
 
Yes, on the basic structure. Without knowing your specific tasks to
accomplish, here are the tables and relationships that I would start with:
================================
tblRequests
RequestID -autonumber (primary key)
CallerID - integer (foreign key -linked 1-many to tblCaller)
RequestDate
TopicID -integer (foreign key -linked 1-many to tbTopic)
ProblemDescription
etc.

tblCaller
CallerID -autonumber (primary key)
Lastname
Fisrtname
Dept
etc.

tblTopic
TopicID -autonumber (primary key)
TopicDescription
==================================

I would suggest additional lookup type tables any other values that will
frequently repeat, like CallerDept, CallerCity,
EquipManufacturer, etc.. Those could be used to load combo-boxes to select
common values. I probably would also have a table for Solutions, as they
would also be repeated with values like "Restore power", "Refer to
Warranty", etc.

Use the combo-box wizard to create a lookup control on your form to find
specific records. An unbound combo-box that chooses a topic would then
select and display just the records that have that topic value in your form.

Again, the templates are a good starting place for beginners. Select the
template closest to your task, select the fields needed, then modify the
tables/forms/reports to your specific needs.
-Ed
 
Hello Ed!

My specific task here is to create a searchable knowledge base (searchable
by Topic) of existing data which comprises of of Callers Requests and the
Solutions provided for them by an IT help desk call centre operators.

There is no need for any callers details or dates etc...

Only requests and solutions are required,

however,

I would like my call centre operators to be able to access this knowledge
base to search (by Topic) for data that may be usefull in answering future
client requests.

So to this end I have created a new database that is made up of two tables
tbl-Requests
RequestID (Primary key) Autonumber
Topic(Lookup to Tbl-Topics) Text
SELECT [tbl-Topics].TopicID, [tbl-Topics].Topic
FROM [tbl-Topics];
Request(memo)
Solution(Memo)

tbl-Topics
TopicsID (Primary Key)
Topic (Text)
Topic

Am I on the right track yet?

How to I make myself emailable without exposing myself to spam and pranksters?

Once again thank you for your interest and sorry I am so thick.

Cheers Sherry
 
What you have should be OK, however if you want to create a good
knowledge-base, you'll need to categorize your solutions, requests types,
topics, etc. Having the [request] and [solution] as memo fields does not
preclude searching, but you'd need to run a string/text search on each memo
in each record, which takes some time.
-Ed

Sherry said:
Hello Ed!

My specific task here is to create a searchable knowledge base (searchable
by Topic) of existing data which comprises of of Callers Requests and the
Solutions provided for them by an IT help desk call centre operators.

There is no need for any callers details or dates etc...

Only requests and solutions are required,

however,

I would like my call centre operators to be able to access this knowledge
base to search (by Topic) for data that may be usefull in answering future
client requests.

So to this end I have created a new database that is made up of two tables
tbl-Requests
RequestID (Primary key) Autonumber
Topic(Lookup to Tbl-Topics) Text
SELECT [tbl-Topics].TopicID, [tbl-Topics].Topic
FROM [tbl-Topics];
Request(memo)
Solution(Memo)

tbl-Topics
TopicsID (Primary Key)
Topic (Text)
Topic

Am I on the right track yet?

How to I make myself emailable without exposing myself to spam and
pranksters?

Once again thank you for your interest and sorry I am so thick.

Cheers Sherry



Ed Robichaud said:
Yes, on the basic structure. Without knowing your specific tasks to
accomplish, here are the tables and relationships that I would start
with:
================================
tblRequests
RequestID -autonumber (primary key)
CallerID - integer (foreign key -linked 1-many to tblCaller)
RequestDate
TopicID -integer (foreign key -linked 1-many to tbTopic)
ProblemDescription
etc.

tblCaller
CallerID -autonumber (primary key)
Lastname
Fisrtname
Dept
etc.

tblTopic
TopicID -autonumber (primary key)
TopicDescription
==================================

I would suggest additional lookup type tables any other values that will
frequently repeat, like CallerDept, CallerCity,
EquipManufacturer, etc.. Those could be used to load combo-boxes to
select
common values. I probably would also have a table for Solutions, as they
would also be repeated with values like "Restore power", "Refer to
Warranty", etc.

Use the combo-box wizard to create a lookup control on your form to find
specific records. An unbound combo-box that chooses a topic would then
select and display just the records that have that topic value in your
form.

Again, the templates are a good starting place for beginners. Select the
template closest to your task, select the fields needed, then modify the
tables/forms/reports to your specific needs.
-Ed
Click to expand...
Click to expand...
 
Ed
Thank you. I will do as you have suggested

If I may make one point here regarding your advise to use the Service call
management sample database in Access.

This too is way over my head despite my efforts to understand it. It would
be nice to find some simple databases for the learner.

Databases that only has a couple of tables /forms queries etc.. not these
complex examples that only serve to confuse this learner with their
complexity.

Although I appreciate that they will be valuable in the future--they are of
no real value for me at my current learning stage.

I must confess to having a working database with no real understanding of
how and why it works.

But... none the less definately grateful to have this particular project
behind me.

Thank you again and I am sure I will be back here many more times in the
future.

Ed Robichaud said:
What you have should be OK, however if you want to create a good
knowledge-base, you'll need to categorize your solutions, requests types,
topics, etc. Having the [request] and [solution] as memo fields does not
preclude searching, but you'd need to run a string/text search on each memo
in each record, which takes some time.
-Ed

Sherry said:
Hello Ed!

My specific task here is to create a searchable knowledge base (searchable
by Topic) of existing data which comprises of of Callers Requests and the
Solutions provided for them by an IT help desk call centre operators.

There is no need for any callers details or dates etc...

Only requests and solutions are required,

however,

I would like my call centre operators to be able to access this knowledge
base to search (by Topic) for data that may be usefull in answering future
client requests.

So to this end I have created a new database that is made up of two tables
tbl-Requests
RequestID (Primary key) Autonumber
Topic(Lookup to Tbl-Topics) Text
SELECT [tbl-Topics].TopicID, [tbl-Topics].Topic
FROM [tbl-Topics];
Request(memo)
Solution(Memo)

tbl-Topics
TopicsID (Primary Key)
Topic (Text)
Topic

Am I on the right track yet?

How to I make myself emailable without exposing myself to spam and
pranksters?

Once again thank you for your interest and sorry I am so thick.

Cheers Sherry



Ed Robichaud said:
Yes, on the basic structure. Without knowing your specific tasks to
accomplish, here are the tables and relationships that I would start
with:
================================
tblRequests
RequestID -autonumber (primary key)
CallerID - integer (foreign key -linked 1-many to tblCaller)
RequestDate
TopicID -integer (foreign key -linked 1-many to tbTopic)
ProblemDescription
etc.

tblCaller
CallerID -autonumber (primary key)
Lastname
Fisrtname
Dept
etc.

tblTopic
TopicID -autonumber (primary key)
TopicDescription
==================================

I would suggest additional lookup type tables any other values that will
frequently repeat, like CallerDept, CallerCity,
EquipManufacturer, etc.. Those could be used to load combo-boxes to
select
common values. I probably would also have a table for Solutions, as they
would also be repeated with values like "Restore power", "Refer to
Warranty", etc.

Use the combo-box wizard to create a lookup control on your form to find
specific records. An unbound combo-box that chooses a topic would then
select and display just the records that have that topic value in your
form.

Again, the templates are a good starting place for beginners. Select the
template closest to your task, select the fields needed, then modify the
tables/forms/reports to your specific needs.
-Ed


Hi Ed,
Thank you for your time.
I am more than happy to start from scatch as you reccommend.

Am I right to understand you as saying I should have two tables?

Table One for recording the Requests and Solutions and also for adding
new
records
Table Two for the topics--I estimate there will be approximately 30
topics

and then I should create a form based on Table one but add a combo box
to
it
that looks up the list of topics from Table Two

Is that correct??

:

I realize that you've already invested time in your current
application,
but
your table structure appears to be inadequate; the "tblAddNew" is not
needed
(most every data table has records added to it over time) and your
other
table needs to be normalized to at least three components.

Look at the built-in "Service Call Management" database template that
Access
has available for guidance.

To answer your question specifically, you have to have a "topic" field
in
your table, so that there are values to search on. Create a lookup
table
of
typical topic values. Then create a topic field in your current table
(
though I really recommend re-designing this whole thing) and add a
combo-box
on your forms. Make the comboxbox control bound to the topic field,
and
make the tblTopics (that you added above) the datasource of that
combobox.
Good luck
-Ed


Good evening all!
I have a very simple database comprising of two tables:

tbl-AddNew has RequestID (primary key- AutoNumber) - Request and
Solution
fields

tbl-RequestsandSolutions has TopicID as Primary key-AutoNumber)

here is my problem!

I want to make this database searchable by Topic.
Topic being Printer-Monitor-Scanner-Hardware-Software etc....

So I may add new Requests and Solutions to my database but assign
them
to
the topic they relate to. EG: This example would need to be stored
in
the
Printer topic

Request is 'My printer is not printing'
Solution 'Check the power supply'

If this was to be the case then when I wish to search the database,
I
may
do
so by looking up Printers as in the example

Naturally I have set up forms which is where both the Adding of new
data
and
the searching of existing data will be carried out from.


I am sure this is so simple that I will feel like a fool but I am
buggered
if I can work out how to establish the correct tables to do this.

All guidence appreciated
Cheers Ohbe
Click to expand...
Click to expand...
Click to expand...
 
Back
Top