Forms - Text & Combo Boxes

  • Thread starter Thread starter hansjhamm
  • Start date Start date
H

hansjhamm

This is something I think I understand, but just cannot get a handle
on...

I have 3 tables from where I want to pull data from and send to another
table through a form...
The 1st thing that the user would select is store # in a combo box
(CBOStoreNum); the data comes from Table "DMStore"
I then have (at this time) 3 Text Boxes that would auto-fill with the
corrresponding information;
R2Time (TXTR2) comes from Table "DMStore"
DM Name (TXTDMName) comes from Table "Department"
RM Name (TXTRMName) comes from Table "Branch"
(I would like to have these to fill in at the selection of store #)

Then there is additional data that the user will have to fill in before
they save it to Table "Open Positions"

I have relationships built From..To Branch,Department, DMStore, Open
Psoitions

I just cannot seem to get started on this and get it to work...

Any and ALL help will be more than appreciated!!

Hans
 
Hans

You did say "any and ALL help"...

When you use Access, you are using a relational database. It is rarely
necessary to copy data from one table to another, like you might if you were
using a spreadsheet.

And using a form to pass data from one location to another is much more work
than you need to do, if your business situation actually requires two copies
of the same information.

If you provide a bit more description of the underlying business need for
two copies, the newsgroup readers will probably be able to help.

If you decide you are using two copies to accomplish something, but don't
actually need two copies, explain a bit more about what you are trying to
accomplish. Again, the newsgroup readers will probably be able to offer an
alternate approach.

Regards

Jeff Boyce
Microsoft Office/Access MVP
 
Jeff,

That is fair enough...your 1st paragraph basically pointed out from
where I am coming from, about everything we have done is Excel based.
I have taken on the "JOB" and it is a job to learn Access/Databasing
and convert EVERYTHING we can!
I think there is alot of db'ing I understand but do not have the
insight and working knowledge of the "proper" way of doing things. So
it may take a few tries to understand what you are requesting to be
able to assist me

At this time I have 11 fields on the form:
Tracking: a new field (auto-numbered) to be entered in Open Position
table
Regional: pulled from Branch table
RSQ Hrs: pulled from DMStore table
Store Number: pulled from StoreLoad table
Recruit Start: a new field to be entered in Open Position table
Position: a new field to be entered in Open Position table
Recruit End: a new field to be entered in Open Position table
Time: A calculation, that I need to have shown on the form
Background Start: a new field to be entered in Open Position table
Turn Around: a new field to be entered in Open Position table

The form will be utilized to enter data, of course, but also a quick
view of individual records...the last step would be to set all of these
fields and more ( I am sure) to export into excel for distribution. If
I understand your point this would be the only time I would "bring" all
these together, but still not in redundancy, i.e. another table...which
makes sense as this would only complicated and make the DB even bigger
( bigger is not always better, huh?)

So, based on this information, how would you/the best way to approach
this. I would rather learn db'ing the right way and not to RE-LEARN!
That is the hard way of doing things. If there is additional info you
need to help, I will provide whatever is necessary

Thanks Alot

Hans
 
Hans

see comments in-line below

Regards

Jeff Boyce
Microsoft Office/Access MVP

Jeff,

That is fair enough...your 1st paragraph basically pointed out from
where I am coming from, about everything we have done is Excel based.
I have taken on the "JOB" and it is a job to learn Access/Databasing
and convert EVERYTHING we can!
I think there is alot of db'ing I understand but do not have the
insight and working knowledge of the "proper" way of doing things. So
Click to expand...

There are any number of ways to get things done in Access. Some of them
work better (i.e., less work for both you and Access), and some of them are
counter-productive (but could still, with much effort, get the job done).
About the only "improper" way I can imagine is something that doesn't work.
it may take a few tries to understand what you are requesting to be
able to assist me

At this time I have 11 fields on the form:
Click to expand...

My first suggestion is to step back from the form and consider your
underlying data structure. If your background and experience is with Excel,
there's a good chance that you've re-created an Excel-like data structure in
Access. While this can be made to work, it will cause you and Access no end
of headaches!

If you want to pursue this angle further, look into relational database
design and normalization. Jeff Conrad offers an excellent set of resources
at:

http://home.bendbroadband.com/conradsystems/accessjunkie/resources.html

Tracking: a new field (auto-numbered) to be entered in Open Position
table
Regional: pulled from Branch table
RSQ Hrs: pulled from DMStore table
Store Number: pulled from StoreLoad table
Recruit Start: a new field to be entered in Open Position table
Position: a new field to be entered in Open Position table
Recruit End: a new field to be entered in Open Position table
Time: A calculation, that I need to have shown on the form
Background Start: a new field to be entered in Open Position table
Turn Around: a new field to be entered in Open Position table
Click to expand...

It seems like you are using this form to enter data into the [Open Position]
table. What about the "pulled from" items -- are you trying to enter those
into [Open Position] as well?

By the way, if any of your table names or field names have spaces, you'll
need to use the square brackets, as I have, so Access doesn't try to find
two (or more) separate objects.
The form will be utilized to enter data, of course, but also a quick
view of individual records...the last step would be to set all of these
fields and more ( I am sure) to export into excel for distribution. If
Click to expand...

To gather fields from tables for export to Excel, use a query. First create
a query that returns all the data you wish. Then, from the database window,
highlight that query and use File | Export to export it to ... (say,
Excel!). Or you can create a macro (or code) that does the same thing.
 
Jeff,

I do appreciate the time your giving....
Currently I have the tables/relationships set up this way...
TBLDivision
PK-DivisionNum

TBLBranch
PK-BranchNum
FK-DivisionNum

TBLDepartment
PK-DepartmentNum
FK-BranchNum

These tables basically will not change very often...
The following tables are always in flux...
TBLCAEmployees
PK-ID
FK-DepartmentNum

DMStore
PK-StoreNum
FK-BranchNum

Now this table relates back to the Branch Table by the FK but also
relates to the CAEmployees via ID and last but not least a new
TBLStoreLoad via STR_NBR

The storeload and CAEmployees table comes from outside sources that I
import into the database. I think that I have everything pretty much
normalized except for DMStore. I have the storeRSQ hours here....I
believe from reading what you posted this should be moved into a table
by itself and relating back via the store numbers etc...

By the brief description that I have given you, does this sound like I
am doing it all correct??


Thanks!


Hans


Jeff said:
Hans

see comments in-line below

Regards

Jeff Boyce
Microsoft Office/Access MVP

Jeff,

That is fair enough...your 1st paragraph basically pointed out from
where I am coming from, about everything we have done is Excel based.
I have taken on the "JOB" and it is a job to learn Access/Databasing
and convert EVERYTHING we can!
I think there is alot of db'ing I understand but do not have the
insight and working knowledge of the "proper" way of doing things. So
Click to expand...

There are any number of ways to get things done in Access. Some of them
work better (i.e., less work for both you and Access), and some of them are
counter-productive (but could still, with much effort, get the job done).
About the only "improper" way I can imagine is something that doesn't work.
it may take a few tries to understand what you are requesting to be
able to assist me

At this time I have 11 fields on the form:
Click to expand...

My first suggestion is to step back from the form and consider your
underlying data structure. If your background and experience is with Excel,
there's a good chance that you've re-created an Excel-like data structure in
Access. While this can be made to work, it will cause you and Access no end
of headaches!

If you want to pursue this angle further, look into relational database
design and normalization. Jeff Conrad offers an excellent set of resources
at:

http://home.bendbroadband.com/conradsystems/accessjunkie/resources.html

Tracking: a new field (auto-numbered) to be entered in Open Position
table
Regional: pulled from Branch table
RSQ Hrs: pulled from DMStore table
Store Number: pulled from StoreLoad table
Recruit Start: a new field to be entered in Open Position table
Position: a new field to be entered in Open Position table
Recruit End: a new field to be entered in Open Position table
Time: A calculation, that I need to have shown on the form
Background Start: a new field to be entered in Open Position table
Turn Around: a new field to be entered in Open Position table
Click to expand...

It seems like you are using this form to enter data into the [Open Position]
table. What about the "pulled from" items -- are you trying to enter those
into [Open Position] as well?

By the way, if any of your table names or field names have spaces, you'll
need to use the square brackets, as I have, so Access doesn't try to find
two (or more) separate objects.
The form will be utilized to enter data, of course, but also a quick
view of individual records...the last step would be to set all of these
fields and more ( I am sure) to export into excel for distribution. If
Click to expand...

To gather fields from tables for export to Excel, use a query. First create
a query that returns all the data you wish. Then, from the database window,
highlight that query and use File | Export to export it to ... (say,
Excel!). Or you can create a macro (or code) that does the same thing.
I understand your point this would be the only time I would "bring" all
these together, but still not in redundancy, i.e. another table...which
makes sense as this would only complicated and make the DB even bigger
( bigger is not always better, huh?)

So, based on this information, how would you/the best way to approach
this. I would rather learn db'ing the right way and not to RE-LEARN!
That is the hard way of doing things. If there is additional info you
need to help, I will provide whatever is necessary

Thanks Alot

Hans
Click to expand...
Click to expand...
 
Hans

Based on what I imagine from your description, it sounds like you have it.

Regards

Jeff Boyce
Microsoft Office/Access MVP

Jeff,

I do appreciate the time your giving....
Currently I have the tables/relationships set up this way...
TBLDivision
PK-DivisionNum

TBLBranch
PK-BranchNum
FK-DivisionNum

TBLDepartment
PK-DepartmentNum
FK-BranchNum

These tables basically will not change very often...
The following tables are always in flux...
TBLCAEmployees
PK-ID
FK-DepartmentNum

DMStore
PK-StoreNum
FK-BranchNum

Now this table relates back to the Branch Table by the FK but also
relates to the CAEmployees via ID and last but not least a new
TBLStoreLoad via STR_NBR

The storeload and CAEmployees table comes from outside sources that I
import into the database. I think that I have everything pretty much
normalized except for DMStore. I have the storeRSQ hours here....I
believe from reading what you posted this should be moved into a table
by itself and relating back via the store numbers etc...

By the brief description that I have given you, does this sound like I
am doing it all correct??


Thanks!


Hans


Jeff said:
Hans

see comments in-line below

Regards

Jeff Boyce
Microsoft Office/Access MVP

Jeff,

That is fair enough...your 1st paragraph basically pointed out from
where I am coming from, about everything we have done is Excel based.
I have taken on the "JOB" and it is a job to learn Access/Databasing
and convert EVERYTHING we can!
I think there is alot of db'ing I understand but do not have the
insight and working knowledge of the "proper" way of doing things. So
Click to expand...

There are any number of ways to get things done in Access. Some of them
work better (i.e., less work for both you and Access), and some of them
are
counter-productive (but could still, with much effort, get the job done).
About the only "improper" way I can imagine is something that doesn't
work.
it may take a few tries to understand what you are requesting to be
able to assist me

At this time I have 11 fields on the form:
Click to expand...

My first suggestion is to step back from the form and consider your
underlying data structure. If your background and experience is with
Excel,
there's a good chance that you've re-created an Excel-like data structure
in
Access. While this can be made to work, it will cause you and Access no
end
of headaches!

If you want to pursue this angle further, look into relational database
design and normalization. Jeff Conrad offers an excellent set of
resources
at:

http://home.bendbroadband.com/conradsystems/accessjunkie/resources.html

Tracking: a new field (auto-numbered) to be entered in Open Position
table
Regional: pulled from Branch table
RSQ Hrs: pulled from DMStore table
Store Number: pulled from StoreLoad table
Recruit Start: a new field to be entered in Open Position table
Position: a new field to be entered in Open Position table
Recruit End: a new field to be entered in Open Position table
Time: A calculation, that I need to have shown on the form
Background Start: a new field to be entered in Open Position table
Turn Around: a new field to be entered in Open Position table
Click to expand...

It seems like you are using this form to enter data into the [Open
Position]
table. What about the "pulled from" items -- are you trying to enter
those
into [Open Position] as well?

By the way, if any of your table names or field names have spaces, you'll
need to use the square brackets, as I have, so Access doesn't try to find
two (or more) separate objects.
The form will be utilized to enter data, of course, but also a quick
view of individual records...the last step would be to set all of these
fields and more ( I am sure) to export into excel for distribution. If
Click to expand...

To gather fields from tables for export to Excel, use a query. First
create
a query that returns all the data you wish. Then, from the database
window,
highlight that query and use File | Export to export it to ... (say,
Excel!). Or you can create a macro (or code) that does the same thing.
I understand your point this would be the only time I would "bring" all
these together, but still not in redundancy, i.e. another table...which
makes sense as this would only complicated and make the DB even bigger
( bigger is not always better, huh?)

So, based on this information, how would you/the best way to approach
this. I would rather learn db'ing the right way and not to RE-LEARN!
That is the hard way of doing things. If there is additional info you
need to help, I will provide whatever is necessary

Thanks Alot

Hans

Jeff Boyce wrote:
Hans

You did say "any and ALL help"...

When you use Access, you are using a relational database. It is
rarely
necessary to copy data from one table to another, like you might if
you
were
using a spreadsheet.

And using a form to pass data from one location to another is much
more
work
than you need to do, if your business situation actually requires two
copies
of the same information.

If you provide a bit more description of the underlying business need
for
two copies, the newsgroup readers will probably be able to help.

If you decide you are using two copies to accomplish something, but
don't
actually need two copies, explain a bit more about what you are trying
to
accomplish. Again, the newsgroup readers will probably be able to
offer
an
alternate approach.

Regards

Jeff Boyce
Microsoft Office/Access MVP


This is something I think I understand, but just cannot get a handle
on...

I have 3 tables from where I want to pull data from and send to
another
table through a form...
The 1st thing that the user would select is store # in a combo box
(CBOStoreNum); the data comes from Table "DMStore"
I then have (at this time) 3 Text Boxes that would auto-fill with
the
corrresponding information;
R2Time (TXTR2) comes from Table "DMStore"
DM Name (TXTDMName) comes from Table "Department"
RM Name (TXTRMName) comes from Table "Branch"
(I would like to have these to fill in at the selection of store #)

Then there is additional data that the user will have to fill in
before
they save it to Table "Open Positions"

I have relationships built From..To Branch,Department, DMStore, Open
Psoitions

I just cannot seem to get started on this and get it to work...

Any and ALL help will be more than appreciated!!

Hans
Click to expand...
Click to expand...
Click to expand...
 
OK... so far so good. Now that I have things related correctly and I
WILL NOT create a duplicated table.
Here is my next question. I have two textboxs TXTRecruitStart and
TXTRecruitEnd. These two are setup where the user "mouse over" a
calendar pops up, they select a date and the date is placed into the
appropriate text box.
I have tried and looked for this answer; How do I pass the number of
days that have lapsed between the two and place that data in a new
textbox TXTTime? All I do know is that I have to use the DateDiff...and
from my understanding it would be better to only "show" this on the
form and not actually record the calculation in the table. i.e.
calculate when the report is run.

Thanks again

Hans

Jeff said:
Hans

Based on what I imagine from your description, it sounds like you have it.

Regards

Jeff Boyce
Microsoft Office/Access MVP

Jeff,

I do appreciate the time your giving....
Currently I have the tables/relationships set up this way...
TBLDivision
PK-DivisionNum

TBLBranch
PK-BranchNum
FK-DivisionNum

TBLDepartment
PK-DepartmentNum
FK-BranchNum

These tables basically will not change very often...
The following tables are always in flux...
TBLCAEmployees
PK-ID
FK-DepartmentNum

DMStore
PK-StoreNum
FK-BranchNum

Now this table relates back to the Branch Table by the FK but also
relates to the CAEmployees via ID and last but not least a new
TBLStoreLoad via STR_NBR

The storeload and CAEmployees table comes from outside sources that I
import into the database. I think that I have everything pretty much
normalized except for DMStore. I have the storeRSQ hours here....I
believe from reading what you posted this should be moved into a table
by itself and relating back via the store numbers etc...

By the brief description that I have given you, does this sound like I
am doing it all correct??


Thanks!


Hans


Jeff said:
Hans

see comments in-line below

Regards

Jeff Boyce
Microsoft Office/Access MVP

Jeff,

That is fair enough...your 1st paragraph basically pointed out from
where I am coming from, about everything we have done is Excel based.
I have taken on the "JOB" and it is a job to learn Access/Databasing
and convert EVERYTHING we can!
I think there is alot of db'ing I understand but do not have the
insight and working knowledge of the "proper" way of doing things. So

There are any number of ways to get things done in Access. Some of them
work better (i.e., less work for both you and Access), and some of them
are
counter-productive (but could still, with much effort, get the job done).
About the only "improper" way I can imagine is something that doesn't
work.

it may take a few tries to understand what you are requesting to be
able to assist me

At this time I have 11 fields on the form:

My first suggestion is to step back from the form and consider your
underlying data structure. If your background and experience is with
Excel,
there's a good chance that you've re-created an Excel-like data structure
in
Access. While this can be made to work, it will cause you and Access no
end
of headaches!

If you want to pursue this angle further, look into relational database
design and normalization. Jeff Conrad offers an excellent set of
resources
at:

http://home.bendbroadband.com/conradsystems/accessjunkie/resources.html


Tracking: a new field (auto-numbered) to be entered in Open Position
table
Regional: pulled from Branch table
RSQ Hrs: pulled from DMStore table
Store Number: pulled from StoreLoad table
Recruit Start: a new field to be entered in Open Position table
Position: a new field to be entered in Open Position table
Recruit End: a new field to be entered in Open Position table
Time: A calculation, that I need to have shown on the form
Background Start: a new field to be entered in Open Position table
Turn Around: a new field to be entered in Open Position table

It seems like you are using this form to enter data into the [Open
Position]
table. What about the "pulled from" items -- are you trying to enter
those
into [Open Position] as well?

By the way, if any of your table names or field names have spaces, you'll
need to use the square brackets, as I have, so Access doesn't try to find
two (or more) separate objects.

The form will be utilized to enter data, of course, but also a quick
view of individual records...the last step would be to set all of these
fields and more ( I am sure) to export into excel for distribution. If

To gather fields from tables for export to Excel, use a query. First
create
a query that returns all the data you wish. Then, from the database
window,
highlight that query and use File | Export to export it to ... (say,
Excel!). Or you can create a macro (or code) that does the same thing.

I understand your point this would be the only time I would "bring" all
these together, but still not in redundancy, i.e. another table...which
makes sense as this would only complicated and make the DB even bigger
( bigger is not always better, huh?)

So, based on this information, how would you/the best way to approach
this. I would rather learn db'ing the right way and not to RE-LEARN!
That is the hard way of doing things. If there is additional info you
need to help, I will provide whatever is necessary

Thanks Alot

Hans

Jeff Boyce wrote:
Hans

You did say "any and ALL help"...

When you use Access, you are using a relational database. It is
rarely
necessary to copy data from one table to another, like you might if
you
were
using a spreadsheet.

And using a form to pass data from one location to another is much
more
work
than you need to do, if your business situation actually requires two
copies
of the same information.

If you provide a bit more description of the underlying business need
for
two copies, the newsgroup readers will probably be able to help.

If you decide you are using two copies to accomplish something, but
don't
actually need two copies, explain a bit more about what you are trying
to
accomplish. Again, the newsgroup readers will probably be able to
offer
an
alternate approach.

Regards

Jeff Boyce
Microsoft Office/Access MVP


This is something I think I understand, but just cannot get a handle
on...

I have 3 tables from where I want to pull data from and send to
another
table through a form...
The 1st thing that the user would select is store # in a combo box
(CBOStoreNum); the data comes from Table "DMStore"
I then have (at this time) 3 Text Boxes that would auto-fill with
the
corrresponding information;
R2Time (TXTR2) comes from Table "DMStore"
DM Name (TXTDMName) comes from Table "Department"
RM Name (TXTRMName) comes from Table "Branch"
(I would like to have these to fill in at the selection of store #)

Then there is additional data that the user will have to fill in
before
they save it to Table "Open Positions"

I have relationships built From..To Branch,Department, DMStore, Open
Psoitions

I just cannot seem to get started on this and get it to work...

Any and ALL help will be more than appreciated!!

Hans
Click to expand...
Click to expand...
Click to expand...
 
Hans

I'll suggest that you repost this question, starting a new thread. That
way, you'll get a lot more eyes on it, and a lot better chance of someone
who's run across this issue before.

If you leave it here only, only folks who dive in and follow EVERY response
will see it.

Regards

Jeff Boyce
Microsoft Office/Access MVP

OK... so far so good. Now that I have things related correctly and I
WILL NOT create a duplicated table.
Here is my next question. I have two textboxs TXTRecruitStart and
TXTRecruitEnd. These two are setup where the user "mouse over" a
calendar pops up, they select a date and the date is placed into the
appropriate text box.
I have tried and looked for this answer; How do I pass the number of
days that have lapsed between the two and place that data in a new
textbox TXTTime? All I do know is that I have to use the DateDiff...and
from my understanding it would be better to only "show" this on the
form and not actually record the calculation in the table. i.e.
calculate when the report is run.

Thanks again

Hans

Jeff said:
Hans

Based on what I imagine from your description, it sounds like you have
it.

Regards

Jeff Boyce
Microsoft Office/Access MVP

Jeff,

I do appreciate the time your giving....
Currently I have the tables/relationships set up this way...
TBLDivision
PK-DivisionNum

TBLBranch
PK-BranchNum
FK-DivisionNum

TBLDepartment
PK-DepartmentNum
FK-BranchNum

These tables basically will not change very often...
The following tables are always in flux...
TBLCAEmployees
PK-ID
FK-DepartmentNum

DMStore
PK-StoreNum
FK-BranchNum

Now this table relates back to the Branch Table by the FK but also
relates to the CAEmployees via ID and last but not least a new
TBLStoreLoad via STR_NBR

The storeload and CAEmployees table comes from outside sources that I
import into the database. I think that I have everything pretty much
normalized except for DMStore. I have the storeRSQ hours here....I
believe from reading what you posted this should be moved into a table
by itself and relating back via the store numbers etc...

By the brief description that I have given you, does this sound like I
am doing it all correct??


Thanks!


Hans


Jeff Boyce wrote:
Hans

see comments in-line below

Regards

Jeff Boyce
Microsoft Office/Access MVP

Jeff,

That is fair enough...your 1st paragraph basically pointed out from
where I am coming from, about everything we have done is Excel
based.
I have taken on the "JOB" and it is a job to learn Access/Databasing
and convert EVERYTHING we can!
I think there is alot of db'ing I understand but do not have the
insight and working knowledge of the "proper" way of doing things.
So

There are any number of ways to get things done in Access. Some of
them
work better (i.e., less work for both you and Access), and some of
them
are
counter-productive (but could still, with much effort, get the job
done).
About the only "improper" way I can imagine is something that doesn't
work.

it may take a few tries to understand what you are requesting to be
able to assist me

At this time I have 11 fields on the form:

My first suggestion is to step back from the form and consider your
underlying data structure. If your background and experience is with
Excel,
there's a good chance that you've re-created an Excel-like data
structure
in
Access. While this can be made to work, it will cause you and Access
no
end
of headaches!

If you want to pursue this angle further, look into relational
database
design and normalization. Jeff Conrad offers an excellent set of
resources
at:


http://home.bendbroadband.com/conradsystems/accessjunkie/resources.html


Tracking: a new field (auto-numbered) to be entered in Open Position
table
Regional: pulled from Branch table
RSQ Hrs: pulled from DMStore table
Store Number: pulled from StoreLoad table
Recruit Start: a new field to be entered in Open Position table
Position: a new field to be entered in Open Position table
Recruit End: a new field to be entered in Open Position table
Time: A calculation, that I need to have shown on the form
Background Start: a new field to be entered in Open Position table
Turn Around: a new field to be entered in Open Position table

It seems like you are using this form to enter data into the [Open
Position]
table. What about the "pulled from" items -- are you trying to enter
those
into [Open Position] as well?

By the way, if any of your table names or field names have spaces,
you'll
need to use the square brackets, as I have, so Access doesn't try to
find
two (or more) separate objects.

The form will be utilized to enter data, of course, but also a quick
view of individual records...the last step would be to set all of
these
fields and more ( I am sure) to export into excel for distribution.
If

To gather fields from tables for export to Excel, use a query. First
create
a query that returns all the data you wish. Then, from the database
window,
highlight that query and use File | Export to export it to ... (say,
Excel!). Or you can create a macro (or code) that does the same
thing.

I understand your point this would be the only time I would "bring"
all
these together, but still not in redundancy, i.e. another
table...which
makes sense as this would only complicated and make the DB even
bigger
( bigger is not always better, huh?)

So, based on this information, how would you/the best way to
approach
this. I would rather learn db'ing the right way and not to RE-LEARN!
That is the hard way of doing things. If there is additional info
you
need to help, I will provide whatever is necessary

Thanks Alot

Hans

Jeff Boyce wrote:
Hans

You did say "any and ALL help"...

When you use Access, you are using a relational database. It is
rarely
necessary to copy data from one table to another, like you might if
you
were
using a spreadsheet.

And using a form to pass data from one location to another is much
more
work
than you need to do, if your business situation actually requires
two
copies
of the same information.

If you provide a bit more description of the underlying business
need
for
two copies, the newsgroup readers will probably be able to help.

If you decide you are using two copies to accomplish something, but
don't
actually need two copies, explain a bit more about what you are
trying
to
accomplish. Again, the newsgroup readers will probably be able to
offer
an
alternate approach.

Regards

Jeff Boyce
Microsoft Office/Access MVP


This is something I think I understand, but just cannot get a
handle
on...

I have 3 tables from where I want to pull data from and send to
another
table through a form...
The 1st thing that the user would select is store # in a combo
box
(CBOStoreNum); the data comes from Table "DMStore"
I then have (at this time) 3 Text Boxes that would auto-fill with
the
corrresponding information;
R2Time (TXTR2) comes from Table "DMStore"
DM Name (TXTDMName) comes from Table "Department"
RM Name (TXTRMName) comes from Table "Branch"
(I would like to have these to fill in at the selection of store
#)

Then there is additional data that the user will have to fill in
before
they save it to Table "Open Positions"

I have relationships built From..To Branch,Department, DMStore,
Open
Psoitions

I just cannot seem to get started on this and get it to work...

Any and ALL help will be more than appreciated!!

Hans
Click to expand...
Click to expand...
Click to expand...
 
Jeff,

Good point...Thanks for all of your help and guidance!

Hans

Jeff said:
Hans

I'll suggest that you repost this question, starting a new thread. That
way, you'll get a lot more eyes on it, and a lot better chance of someone
who's run across this issue before.

If you leave it here only, only folks who dive in and follow EVERY response
will see it.

Regards

Jeff Boyce
Microsoft Office/Access MVP

OK... so far so good. Now that I have things related correctly and I
WILL NOT create a duplicated table.
Here is my next question. I have two textboxs TXTRecruitStart and
TXTRecruitEnd. These two are setup where the user "mouse over" a
calendar pops up, they select a date and the date is placed into the
appropriate text box.
I have tried and looked for this answer; How do I pass the number of
days that have lapsed between the two and place that data in a new
textbox TXTTime? All I do know is that I have to use the DateDiff...and
from my understanding it would be better to only "show" this on the
form and not actually record the calculation in the table. i.e.
calculate when the report is run.

Thanks again

Hans

Jeff said:
Hans

Based on what I imagine from your description, it sounds like you have
it.

Regards

Jeff Boyce
Microsoft Office/Access MVP

Jeff,

I do appreciate the time your giving....
Currently I have the tables/relationships set up this way...
TBLDivision
PK-DivisionNum

TBLBranch
PK-BranchNum
FK-DivisionNum

TBLDepartment
PK-DepartmentNum
FK-BranchNum

These tables basically will not change very often...
The following tables are always in flux...
TBLCAEmployees
PK-ID
FK-DepartmentNum

DMStore
PK-StoreNum
FK-BranchNum

Now this table relates back to the Branch Table by the FK but also
relates to the CAEmployees via ID and last but not least a new
TBLStoreLoad via STR_NBR

The storeload and CAEmployees table comes from outside sources that I
import into the database. I think that I have everything pretty much
normalized except for DMStore. I have the storeRSQ hours here....I
believe from reading what you posted this should be moved into a table
by itself and relating back via the store numbers etc...

By the brief description that I have given you, does this sound like I
am doing it all correct??


Thanks!


Hans


Jeff Boyce wrote:
Hans

see comments in-line below

Regards

Jeff Boyce
Microsoft Office/Access MVP

Jeff,

That is fair enough...your 1st paragraph basically pointed out from
where I am coming from, about everything we have done is Excel
based.
I have taken on the "JOB" and it is a job to learn Access/Databasing
and convert EVERYTHING we can!
I think there is alot of db'ing I understand but do not have the
insight and working knowledge of the "proper" way of doing things.
So

There are any number of ways to get things done in Access. Some of
them
work better (i.e., less work for both you and Access), and some of
them
are
counter-productive (but could still, with much effort, get the job
done).
About the only "improper" way I can imagine is something that doesn't
work.

it may take a few tries to understand what you are requesting to be
able to assist me

At this time I have 11 fields on the form:

My first suggestion is to step back from the form and consider your
underlying data structure. If your background and experience is with
Excel,
there's a good chance that you've re-created an Excel-like data
structure
in
Access. While this can be made to work, it will cause you and Access
no
end
of headaches!

If you want to pursue this angle further, look into relational
database
design and normalization. Jeff Conrad offers an excellent set of
resources
at:


http://home.bendbroadband.com/conradsystems/accessjunkie/resources.html


Tracking: a new field (auto-numbered) to be entered in Open Position
table
Regional: pulled from Branch table
RSQ Hrs: pulled from DMStore table
Store Number: pulled from StoreLoad table
Recruit Start: a new field to be entered in Open Position table
Position: a new field to be entered in Open Position table
Recruit End: a new field to be entered in Open Position table
Time: A calculation, that I need to have shown on the form
Background Start: a new field to be entered in Open Position table
Turn Around: a new field to be entered in Open Position table

It seems like you are using this form to enter data into the [Open
Position]
table. What about the "pulled from" items -- are you trying to enter
those
into [Open Position] as well?

By the way, if any of your table names or field names have spaces,
you'll
need to use the square brackets, as I have, so Access doesn't try to
find
two (or more) separate objects.

The form will be utilized to enter data, of course, but also a quick
view of individual records...the last step would be to set all of
these
fields and more ( I am sure) to export into excel for distribution.
If

To gather fields from tables for export to Excel, use a query. First
create
a query that returns all the data you wish. Then, from the database
window,
highlight that query and use File | Export to export it to ... (say,
Excel!). Or you can create a macro (or code) that does the same
thing.

I understand your point this would be the only time I would "bring"
all
these together, but still not in redundancy, i.e. another
table...which
makes sense as this would only complicated and make the DB even
bigger
( bigger is not always better, huh?)

So, based on this information, how would you/the best way to
approach
this. I would rather learn db'ing the right way and not to RE-LEARN!
That is the hard way of doing things. If there is additional info
you
need to help, I will provide whatever is necessary

Thanks Alot

Hans

Jeff Boyce wrote:
Hans

You did say "any and ALL help"...

When you use Access, you are using a relational database. It is
rarely
necessary to copy data from one table to another, like you might if
you
were
using a spreadsheet.

And using a form to pass data from one location to another is much
more
work
than you need to do, if your business situation actually requires
two
copies
of the same information.

If you provide a bit more description of the underlying business
need
for
two copies, the newsgroup readers will probably be able to help.

If you decide you are using two copies to accomplish something, but
don't
actually need two copies, explain a bit more about what you are
trying
to
accomplish. Again, the newsgroup readers will probably be able to
offer
an
alternate approach.

Regards

Jeff Boyce
Microsoft Office/Access MVP


This is something I think I understand, but just cannot get a
handle
on...

I have 3 tables from where I want to pull data from and send to
another
table through a form...
The 1st thing that the user would select is store # in a combo
box
(CBOStoreNum); the data comes from Table "DMStore"
I then have (at this time) 3 Text Boxes that would auto-fill with
the
corrresponding information;
R2Time (TXTR2) comes from Table "DMStore"
DM Name (TXTDMName) comes from Table "Department"
RM Name (TXTRMName) comes from Table "Branch"
(I would like to have these to fill in at the selection of store
#)

Then there is additional data that the user will have to fill in
before
they save it to Table "Open Positions"

I have relationships built From..To Branch,Department, DMStore,
Open
Psoitions

I just cannot seem to get started on this and get it to work...

Any and ALL help will be more than appreciated!!

Hans
Click to expand...
Click to expand...
Click to expand...
 
Back
Top