General Question on a database setup.

C

Chad Cameron

Hi All Again,

I am stumped on the setup of my new database. Refer to my post on Sept 3/09
@ 4:04PM

I have a Company Table with fields:
CompanyID, CompanyName, CompanyAddress

I have a Contractor Table with fields:
ContractorID, ContractorName

I have a Procedures Table with fields:
ProcedureID, ProcedureName

I have a Main Table with fields:
MainID, CompanyID, ContractorID, ProcedureID, Date

I have the following forms:
Add Companies to the Company Table
Add Contractors to the Contractor Table
Add Procedures to the Procedure Table
Add Company, Contractor, Procedure to the Main Table.****

****This form has you select the Company from a combobox, which populates my
Listbox of Contractors. I select the contractor and it shows me what
procedures he has. I can now add procedures to the contractor as they are
signed off.

My problem is, because of the way the form is filtered, when I create a new
contractor, he/she is not associated to a company, therefore, when I select
the company from my 'last mentioned' form, that contractor doesn't appear.
How can I associate a company to my contractor with my current setup?

As always, your help is greatly appreciated,
Chad

PS: Sorry for all the posts, it should be clear that I am not an access
expert, yet ;)
 
C

Chad Cameron

OK, getting closer, but not quite there.

I have done the recommend changes. I am sure this is a simple change but I
cannot make the connection. Before, when I selected the company from the
CBox, it filtered because it was looking at the companyID in my
tblContractorProcedure. Now I have moved the CompanyID to my tblContractor
table, so now when I look at my tblContractorProcedure I have to look
through my tblContractor to associate with the CompanyID.

I guess a better way to say this is: How can I filter the detail section of
my form by the CompanyID

Thanks again Steve,
Chad
 
C

Chad Cameron

Also,
Because my filter is based on the ContractorProcedure Table, when I create a
new contractor and assign a company to him/her, it still doesn't show up in
the form, because he/she doesn't have a procedure assigned to him/her yet.

Somehow, I need to add the contractor to my contractorprocedure table and
just have the procedure & date field blank (or zero)
 
C

Chad Cameron

Whichever is the easiest. The contractor should only be with 1 company at
any given time. If he/she switches I can manually override the table. I am
doing this at work, so, I will not be back until Tuesday anyways and I am
done for today.

Thanks again and have a good weekend,
Chad
 
J

John... Visio MVP

Careful, these newsgroups are for FREE peer to peer support and some
unscrupulous posters in these newsgroups are known to lead people on with
some right answers and then claim that you are over your head and you need
to hire them to get your problems resolved.

From what you have posted, you are on the right track and the regulars here
can guide you easily over the rough patches. If you do go the paid help
route, make sure you fully check them out.

John... Visio MVP
 
C

Chad Cameron

Hmmm, that's wierd I sent a response, but it never showed up.

So, sorry for the delay. At this time, contractor will only be associated
to 1 company. If they switch, then I will manually change it in the tables.
 
C

Chad Cameron

I got your question about the contractor and the company. I responded to
that on the 15th. And I got your latest response on the 15th. I only show 1
response from you on the 11th saying you are away on Sat.
 
C

Chad Cameron

I now have all the posts. I only download the headers and it was set to
300. So you don't need to repost.
 
C

Chad Cameron

duly noted.
Thanks

John... Visio MVP said:
Careful, these newsgroups are for FREE peer to peer support and some
unscrupulous posters in these newsgroups are known to lead people on with
some right answers and then claim that you are over your head and you need
to hire them to get your problems resolved.

From what you have posted, you are on the right track and the regulars
here can guide you easily over the rough patches. If you do go the paid
help route, make sure you fully check them out.

John... Visio MVP
Click to expand...
 
C

Chad Cameron

OK, I don't really care what company a contractor works for at any given
time. If a contractor comes in and says he has switched companies, I don't
want to have to re-enter the procedures he is signed off on. I would rather
just change the company name associated to that contractor.
 
J

John... Visio MVP

So stevie, you disagree with the advice about checking out references before
hiring someone?

If you are going to drop this thread, can I suggest that you make the extra
step of dropping your pimping in these newsgroups?

John... Visio MVP
 
C

Chad Cameron

Steve, you did an excellent job. I am sure, if I had more experience, I
wouldn't have had to make you jump through so many hoops. My problem is
that I don't know enough to know what I am doing. Basically if something
goes wrong, I start over from the beginning. I appriciate your time you
gave me.

Thanks Gina,
Chad
 
G

Gina Whipp

Chad,

tblCompany
cCompanyID (PK)
cCompanyName
cCompanyAddress

tblContractorProfile
cpContractorID (PK)
cpContractorName

tblContractorCompany
ccContractorCompanyID
ccCompanyID (FK-tblCompanyID)
ccContractorID (FK-tblContractorProfile)
ccStartDate
ccEndDate

tblProcedures
pProcedureID
pProcedureName

tblMain ***This is the only table I don't get...
mContractorCompanyID (FK-tblContractorCompany)
mSignOffDate

I see alot of talk about table set-up but no talk about what you are doing.
So that I can insure the tables are properly designed... please explain:
What are you trying to track? What are Procedures and when are they
assigned? What is the purpose of mSignOffDate?

--
Gina Whipp

"I feel I have been denied critical, need to know, information!" - Tremors
II

http://www.regina-whipp.com/index_files/TipList.htm
 
C

Chad Cameron

I am sorry for everyone that has helped, but I am way out of my league. I
am dropping this project.

Thanks again, the knowledge I have gained was great, but I am missing the
basics and my structure and form is hurting because of it. I will come back
once I learn some more basics.

Sorry
Chad
 
G

Gina Whipp

Chad,

A little help for the basics...

Jeff Conrad's resources page:
http://www.accessmvp.com/JConrad/accessjunkie/resources.html

The Access Web resources page:
http://www.mvps.org/access/resources/index.html

A free tutorial written by Crystal (MS Access MVP):
http://allenbrowne.com/casu-22.html

MVP Allen Browne's tutorials:
http://allenbrowne.com/links.html#Tutorials


http://www.databasedev.co.uk/table-of-contents.html

--
Gina Whipp

"I feel I have been denied critical, need to know, information!" - Tremors
II

http://www.regina-whipp.com/index_files/TipList.htm
 
J

John... Visio MVP

Steve said:
Chad,

I provide help with Access, Excel and Word applications for a small fee.
Let me build this database for you for a very modest fee and then since
you have already put a lot of time into it; you will see how it is done
and understand it. Not only will you have a functional database but it
will be a great learning tool as you pursue learning more basics. If you
are interested, contact me.

Steve
(e-mail address removed)
Click to expand...



These newsgroups are provided by Microsoft for FREE peer to peer support.
There are many highly qualified individuals who gladly help for free. Stevie
is not one of them, but he is the only one who just does not get the idea of
"FREE" support. He offers questionable results at unreasonable prices. If he
was any good, the "thousands" of people he claims to have helped would be
flooding him with work, but there appears to be a continuous drought and he
needs to constantly grovel for work.

A few gems gleaned from the Word New User newsgroup over the past year to
show Stevie's "expertise".


Dec 17, 2008 7:47 pm

Word 2007 ..........
In older versions of Word you could highlght some text then go to Format -
Change Case and change the case of the hoghloghted text. Is this still
available in Word 2007? Where?
Thanks! Steve


Dec 22, 2008 8:22 pm

I am designing a series of paystubs for a client. I start in landscape and
draw a table then add columns and rows to setup labels and their
corresponding value. This all works fine. After a landscape version is
completed, I next need to design a portrait version. Rather than strating
from scratch, I'd like to be able to cut and paste from the landscape
version and design the portrait version.
Steve


Dec 24, 2008, 1:12 PM

How do you protect the document for filling in forms?
Steve


One of my favourites:
Dec 30, 2008 8:07 PM - a reply to stevie
(The original poster asked how to sort a list and stevie offered to create
the OP an Access database)
Yes, you are right but a database is the correct tool to use not a
spreadsheet.
Click to expand...


Not at all. If it's just a simple list then a spreadsheet is perfectly
adequate...


Sept 10, 2009
(In respose to a perfectly adequate GENERIC solution stevie wrote)

This function is specific to the example but not generic for any amount paid
out.

Steve



Sept 9, 2009
Steve said:
you can then return all the characters in front of it with the Left()
fumction. Would look like:
Left("YourString",Instr("YourString","VbCr" Or "VbLf") - 1)

Steve
Click to expand...

No, it would not look like

Left("YourString",Instr("YourString","VbCr" Or "VbLf") - 1)

First of all, the constants are vbCr and vbLf: no quotes around them. With
the quotes, you're looking for the literal strings.

Second, you can't Or together character constants like that. Even if you
could, Or'ing them together in the InStr function like that makes no sense
at all.



John... Visio MVP
 
C

Chad Cameron

Hi Gina, I know I said I am walking away from this, but the OCD just kicked
in, so I will continue tinkering away at it.

My tables are close to your suggestion. I don't have a
tbleContractorCompany. Instead I have a FK to the tblCompany in my
tblContractorPrfile table. I am not worried about a contractor jumping
between companies. If they do switch I will change it manually.

My tblMain was the table that stored the ContractorID, ProcedureID, and
SignedDate (which is the date that the contractor completed the procedure).
Steve mentioned that Main is not a good name, so I changed it to
tblContractorProcedure.

The ultimate idea is as follows:
At my company, before a contractor can come on site, they must
read/understand certain procedures we have in place. ex. Orientation &
Personal Protection Equipment.

We have somewhere between 100-300 active contractors on site, across many
companies. So I want a database that allows me to select a company from a
combo box (Done). Once that is done, it will automatically populate a
listbox with all the contractors from that company (Done). Then when I
click on a contractor it will show me all the procedures that he/she has
done and the date they completed the procedure. (Contractors must redo
procedures anually).

The combobox looks at the tblCompany. The listbox looks at the tblContractor
'WHERE' the companyID = combobox.

This is where my lack of knowledge bites me in the butt. My Form's
RecordSource is the tblContractorProcedure. So in the detail of my form
(continuous) I list all (when unfiltered) the Contractors & their Procedures
& the date they completed it. I don't want to re-enter the company or the
contractor name, so I want to pull it from the combobox & listbox. They
only way I could think of doing it, is to have 2 hidden textboxes that
populate when the combobox & listbox is selected. So on update it puts the
hidden textbox data into the tblContractorProcedure, plus the date they
completed the procedure

Now, when I select a company from the combobox it only populates the
listbox, which is good. When I select the contractor from the listbox, it
filters the detail part of my form and only shows the procedures for that
contractor (almost). Sorry for the following description. If one of the
line items has the little black triangle beside it, when I click on a
contractor it filters the data, but where ever that triangle is, it changes
that line item to be associated with the selected contractor. I tried using
[DoCmd.GoToRecord , , acLast] but it still selects an active record and
changes it. So I used [DoCmd.GoToRecord , , acNewRec] but now it adds a row
item everytime I click on a contractor.

HTH
Chad
 
C

Chad Cameron

Got it to work

Chad Cameron said:
Hi Gina, I know I said I am walking away from this, but the OCD just
kicked in, so I will continue tinkering away at it.

My tables are close to your suggestion. I don't have a
tbleContractorCompany. Instead I have a FK to the tblCompany in my
tblContractorPrfile table. I am not worried about a contractor jumping
between companies. If they do switch I will change it manually.

My tblMain was the table that stored the ContractorID, ProcedureID, and
SignedDate (which is the date that the contractor completed the
procedure). Steve mentioned that Main is not a good name, so I changed it
to tblContractorProcedure.

The ultimate idea is as follows:
At my company, before a contractor can come on site, they must
read/understand certain procedures we have in place. ex. Orientation &
Personal Protection Equipment.

We have somewhere between 100-300 active contractors on site, across many
companies. So I want a database that allows me to select a company from a
combo box (Done). Once that is done, it will automatically populate a
listbox with all the contractors from that company (Done). Then when I
click on a contractor it will show me all the procedures that he/she has
done and the date they completed the procedure. (Contractors must redo
procedures anually).

The combobox looks at the tblCompany. The listbox looks at the
tblContractor 'WHERE' the companyID = combobox.

This is where my lack of knowledge bites me in the butt. My Form's
RecordSource is the tblContractorProcedure. So in the detail of my form
(continuous) I list all (when unfiltered) the Contractors & their
Procedures & the date they completed it. I don't want to re-enter the
company or the contractor name, so I want to pull it from the combobox &
listbox. They only way I could think of doing it, is to have 2 hidden
textboxes that populate when the combobox & listbox is selected. So on
update it puts the hidden textbox data into the tblContractorProcedure,
plus the date they completed the procedure

Now, when I select a company from the combobox it only populates the
listbox, which is good. When I select the contractor from the listbox, it
filters the detail part of my form and only shows the procedures for that
contractor (almost). Sorry for the following description. If one of the
line items has the little black triangle beside it, when I click on a
contractor it filters the data, but where ever that triangle is, it
changes that line item to be associated with the selected contractor. I
tried using [DoCmd.GoToRecord , , acLast] but it still selects an active
record and changes it. So I used [DoCmd.GoToRecord , , acNewRec] but now
it adds a row item everytime I click on a contractor.

HTH
Chad


Gina Whipp said:
Chad,

tblCompany
cCompanyID (PK)
cCompanyName
cCompanyAddress

tblContractorProfile
cpContractorID (PK)
cpContractorName

tblContractorCompany
ccContractorCompanyID
ccCompanyID (FK-tblCompanyID)
ccContractorID (FK-tblContractorProfile)
ccStartDate
ccEndDate

tblProcedures
pProcedureID
pProcedureName

tblMain ***This is the only table I don't get...
mContractorCompanyID (FK-tblContractorCompany)
mSignOffDate

I see alot of talk about table set-up but no talk about what you are
doing. So that I can insure the tables are properly designed... please
explain: What are you trying to track? What are Procedures and when are
they assigned? What is the purpose of mSignOffDate?

--
Gina Whipp

"I feel I have been denied critical, need to know, information!" -
Tremors II

http://www.regina-whipp.com/index_files/TipList.htm
Click to expand...
Click to expand...
 

Ask a Question

Want to reply to this thread or ask your own question?

You'll need to choose a username for the site, which only take a couple of moments. After that, you can post your question and our members will help you out.

Ask a Question

Similar Threads

Getting combobox/listbox data into a table 2
How to Shop Client delays in my ghantt chart so i can claim for delays 0
Combo Box, List Box & a Sub Form 4
Assigning an overarching owner to a task 0
Random Top 10 percent of groups 6
Complicated relationship between two tables 2
Conditonal Mail Merge statements 0
ComboBox Multiple Entries 4

Top