Sales Offer Summary Database

[debb66]

I want to build a simple database….

All tables will be linked to a Commercial Manager

There will be three tables

Commercial Manager containing:
CommercialManager ID (Auto-number) Primary Key
Commercial Manager Name

Project Table containing:
ProjectNameID (Auto-number) Primary Key
CommercialManager ID
ProjectName
Region

Project Detail Table containing:
ProjectDetailID (Auto-number) Primary Key
CommercialManager ID
Scope
Price
Cost
Date Approved
Approved By

My issue – I link all the tables together but I want everything to be linked
to the commercial managers name so when I create my form it will pull up only
those Projects and Details that are related to the Commercial Manager Name.

I can’t get this to work and I’m sure it is a simple event I’m missing.

Thanks to anyone that can help!

 

[Ken Snell \(MVP\)]

Use a form with subforms to show the data the way you want..... don't try to
do it in a table.

 

[debb66]

Forgive my ignorance but what will I build my form off of if I don't have
tables? I tried creating a query based off of expressions and I keep getting
a circular error.

 

[Evi]

Just one thing I'd like to check about in your structure (obviously, I don't
know exactly what you are doing.

.. You've got Commercial Manager ID in the ProjectDetail table as well as the
Project Table.
Does that mean that one manager can be in charge of the Project while
another can be in charge of a detail?
If not, then ManagerID should not be in the ProjectDetail table
Also, shouldn't ProjectNameID be a Foreign Key field in the the
ProjectDetail table?

The structure I expected to see is
Commercial Manager containing:

CommercialManager ID (Auto-number) Primary Key
Commercial Manager Name

Project Table containing:
ProjectNameID (Auto-number) Primary Key
CommercialManager ID
ProjectName
Region

Project Detail Table containing:
ProjectDetailID (Auto-number) Primary Key ProjectNameID
CommercialManager ID (only if a different managers can be in charge of

different details for any one project)

Scope
Price
Cost
Date Approved
Approved By

Even without the ManagerID in the ProjectDetail table, you will still be
able to access the Commercial Manager because he is connected to the Project
table which is itself connected to the ProjectDetail table.

You may have this perfectly correct and I may be misunderstanding what you
are trying to achieve but it is worth checking now rather than when you have
created your forms and subforms.
Nice clear names, btw, but if you miss out the spaces, you will find it
easier when you create queries and write code. You can still have the full
names in your forms' labels ('and you WILL be inputting data via forms and
NOT directly into tables' she growls threateningly)

Evi

 

[Evi]

This is really confusing Debb. You started off by saying that you have some
tables and even named their fields. Now you are saying that you don't have
tables???
Evi

..

 

[Evi]

Is this like Mrs Beaton's recipe for Hare Soup which starts off with the
misleadingly simple instruction 'First catch Your hare'? Al is assuming (as
I did) that you have created your tables and are now trying to input the
data.

Start off by creating the tables. Make sure that you have the structure
right.
Use the Design View, not that silly Datasheet View. Creating the tables
means putting the field names there, deciding the types of data you will
enter there (date, number, text, Yes/No etc then linking the tables in the
Relationships window. You will now have a blank table on which to base your
form.

Only when you have created the table structure, do you think about creating
the forms so that you can input data.



Evi

 

[debb66]

I do have the tables built - however Ken implied not to use tables when I
build my forms. See where my confusion comes in?

My issue is - tables have been created - I create my form use "Commercial
Manager" as parent then I create my subform using my query I built for the
Project Details. I want to be able to click on the Commercial Manager's name
and their projects pop up.

 

[Evi]

Nooo! What Ken meant was don't input your data into tables, even those with
lookups and subdatasheets, use proper forms and subforms. But you say that
is what you are doing already.

Normally, you would just add a second subform or have one subform filtering
another but have you read my post on your database structure? If it isn't
correct that this won't work.

Do you have different managers managing the details to those managing the
projects or is it the same manager?



evi

 

[debb66]

Evi:

Thank you so much for your help. Managers can be reassigned to a project
that someone else has worked on. I want to be able to keep a history of
prices given out by what manager.

 

[Evi]

In that case, can't you just associate the manager to the Project Details
rather than to the Project itself? You can use the DateApproved date to find
out who was given the project first. You can have the managerID in both
tables if you have to but you'll have to do stuff like adding the manager's
table twice to your queries so that you don't get a circular reference.
Evi

 

[Ken Snell \(MVP\)]

Nooo! What Ken meant was don't input your data into tables, even those
with
lookups and subdatasheets, use proper forms and subforms.

That is correct. Thanks, Evi.