Table design problem?

On your question "are the sort fields the type of bills for each audit
type.Yes. and each sort fields are on by which source the correspondence came
in which would be received. Example below:

File Room- Phone Calls-TP/Corres Petitions-AG E-Mail-Walk-ins-TPS-Other
Agents-Bankruptcy E-Mail-Tax Appeal-By Fax-ChecksRec'd

There is also a sort on each type of Audit Type on what was completed. So
each Audit type sort or bill would have both of these sorts.

Example below.

Pending Bill/- Billing/- Assessment/- Certified Assmt

Thanks again for all your help. I have been trying to get this together for
months now and my designs are not cutting it.












"Ken Snell [MVP]" wrote:

My error -- I was designing this form in my head, and overlooked some
things.

Yes, you'll want to use a subform instead of the Detail section for entering
the CorrID and WorkQuantity values. This subform's RecordSource will be the
EmpCorrWork table. The subform also should have textboxes bound to the EmpID
and WorkDate fields, but set the Visibility property of these textboxes to
No.

Keep the combo box for EmpID selection and the textbox for WorkDate entry in
the main form's ReportHeader section. The main form should have no
RecordSource at all.

The linking fields from main form to the subform will be these (use real
names):
LinkChildFields: EmpID,WorkDate
LinkMasterFields: EmpIDComboBox, WorkDateTextBox

This should fix the data entry problem you were having.

I am not understanding how CorrID (which you're using as the type of audit)
is related to the various sort fields. Are the sort fields the "type of
bills" for each audit type? I need you to fully spell out the entities
involved in your data model. It appears that you may need an additional
field in the EmpCorrWork table, or that you may need another table. I just
cannot tell yet without a better picture of your data model.

--

Ken Snell
MS ACCESS MVP


"Melissa" wrote in message
...
I related the EmpID and CorrID to the parent tables. I then made a combo
box
on the form for both of these fields. The Correspondence Name consists of
all
50 different types of correspondence(audit types) received which will be
chosen from a drop down list. Now grant it, no one employee will do 50
different audit types in a day (or work the correp from all 50) but, they
will at the very minimun do 3 different types. And each of those types
usually comes in through different avenues. A audit type would be for
example; ST NR (sales tax no remit) or Corporate DQ (delinquency) etc.
Each
tax type can have different types of bills. That is why there is so many.

In the third table EmpCorrWork I did not include the EmpName or CorrName
since the EmpID and CorrId is already linked to these tables. In my form I
placed the EmpName and Date in the FormHeader and the detail section I put
the different sorts of how they would receive the files. Phone, FileRm
etc.
The form would not allow me to tab down to any of my fields within the
detail
section after I enter the date. Should I use a subform for the detail
section?

Hope I answered your question and thanks again for all your help.

"Ken Snell [MVP]" wrote:

I need to better understand the relationship of the "sort" fields to the
CorrID entity (which I am using as the identity of a type of
correspondence
from your earlier list). What are these fields, how are they used, what
type
of data entry is needed for them, etc.?

--

Ken Snell
MS ACCESS MVP

"Melissa" wrote in message
...
Thanks for your help Ken. I have completed doing the suggestions you
have
offered. But have a couple questions on the tblEmpCorrWork.
On the third table you suggested only to put the 4 fields into the
table,
then to build the form based on a qry of those fields. In this table,
there
would be more than 4 fields because each audit type can have up to 9
different sort fields. Would these fields go into a different table? I
put
these fields into the table 3 with the EmpID and CorrID being the
foreign
keys back to the parent. What is happening is it is associating each
agent
with a particular audit code when each agent may work many different
types
of
audits. Thanks again for your help



snipped

OK so we'll need one additional table to hold the "manner in which the audit
type was received/came in". Let's call this tblHowReceived. This table
should have these two fields at least:
HowReceivedID (primary key)
HowReceivedName

You then would add one more field to EmpCorrWork table -- it would be the
HowReceivedID field, which would be a foreign key back to tblHowReceived
table. On your subform, you'd then put another combo box to let the user
select the HowReceived item and store it in the table.

Now, depending upon your database desires, you may want another junction
table to relate the HowReceived record to a specific audit type (CorrID). Do
you want to limit the choices of HowReceived based on which CorrID is
selected? If yes, then you'd need cascading combo boxes for the CorrID and
HowReceivedID selections. But let's hold off on this for the moment unless
you really want to do it -- there are a few "tricks" to do this on a
continuous forms view -- not difficult, but it would mean that the
HowReceived combo box would have a different RowSource query for each
record, and a previous record on the subform may have a value for
HowReceived that isn't in the combo box's Row Source at the moment.

--

Ken Snell
MS ACCESS MVP


"Melissa" wrote in message
...
On your question "are the sort fields the type of bills for each audit
type.Yes. and each sort fields are on by which source the correspondence
came
in which would be received. Example below:

File Room- Phone Calls-TP/Corres Petitions-AG E-Mail-Walk-ins-TPS-Other
Agents-Bankruptcy E-Mail-Tax Appeal-By Fax-ChecksRec'd

There is also a sort on each type of Audit Type on what was completed. So
each Audit type sort or bill would have both of these sorts.

Example below.

Pending Bill/- Billing/- Assessment/- Certified Assmt

Thanks again for all your help. I have been trying to get this together
for
months now and my designs are not cutting it.












"Ken Snell [MVP]" wrote:

My error -- I was designing this form in my head, and overlooked some
things.

Yes, you'll want to use a subform instead of the Detail section for
entering
the CorrID and WorkQuantity values. This subform's RecordSource will be
the
EmpCorrWork table. The subform also should have textboxes bound to the
EmpID
and WorkDate fields, but set the Visibility property of these textboxes
to
No.

Keep the combo box for EmpID selection and the textbox for WorkDate entry
in
the main form's ReportHeader section. The main form should have no
RecordSource at all.

The linking fields from main form to the subform will be these (use real
names):
LinkChildFields: EmpID,WorkDate
LinkMasterFields: EmpIDComboBox, WorkDateTextBox

This should fix the data entry problem you were having.

I am not understanding how CorrID (which you're using as the type of
audit)
is related to the various sort fields. Are the sort fields the "type of
bills" for each audit type? I need you to fully spell out the entities
involved in your data model. It appears that you may need an additional
field in the EmpCorrWork table, or that you may need another table. I
just
cannot tell yet without a better picture of your data model.

--

Ken Snell
MS ACCESS MVP


"Melissa" wrote in message
...
I related the EmpID and CorrID to the parent tables. I then made a combo
box
on the form for both of these fields. The Correspondence Name consists
of
all
50 different types of correspondence(audit types) received which will
be
chosen from a drop down list. Now grant it, no one employee will do 50
different audit types in a day (or work the correp from all 50) but,
they
will at the very minimun do 3 different types. And each of those types
usually comes in through different avenues. A audit type would be for
example; ST NR (sales tax no remit) or Corporate DQ (delinquency) etc.
Each
tax type can have different types of bills. That is why there is so
many.

In the third table EmpCorrWork I did not include the EmpName or
CorrName
since the EmpID and CorrId is already linked to these tables. In my
form I
placed the EmpName and Date in the FormHeader and the detail section I
put
the different sorts of how they would receive the files. Phone, FileRm
etc.
The form would not allow me to tab down to any of my fields within the
detail
section after I enter the date. Should I use a subform for the detail
section?

Hope I answered your question and thanks again for all your help.

"Ken Snell [MVP]" wrote:

I need to better understand the relationship of the "sort" fields to
the
CorrID entity (which I am using as the identity of a type of
correspondence
from your earlier list). What are these fields, how are they used,
what
type
of data entry is needed for them, etc.?

--

Ken Snell
MS ACCESS MVP

"Melissa" wrote in message
...
Thanks for your help Ken. I have completed doing the suggestions you
have
offered. But have a couple questions on the tblEmpCorrWork.
On the third table you suggested only to put the 4 fields into the
table,
then to build the form based on a qry of those fields. In this
table,
there
would be more than 4 fields because each audit type can have up to 9
different sort fields. Would these fields go into a different table?
I
put
these fields into the table 3 with the EmpID and CorrID being the
foreign
keys back to the parent. What is happening is it is associating each
agent
with a particular audit code when each agent may work many different
types
of
audits. Thanks again for your help



snipped

On this response: The linking fields from main form to the subform will be
these (use real
names):
LinkChildFields: EmpID,WorkDate
LinkMasterFields: EmpIDComboBox, WorkDateTextBox

I am having a problem linking these fields within the form. I then went to
the table
to link the child field to master and was having trouble with this as well.

Also, on the 10/25 suggestion on the How Received combo box, there could be
more than one how received in a day per audit type.

Just a question, do you think that this database that I am trying to build
is to technical for a intermediate user such as myself. I used to be pretty
good at building these but have lost my abililty to get the tables sorted. I
feel if I could get my tables and relationships in order, I could go from
there.

Thanks again.

"Ken Snell [MVP]" wrote:

OK so we'll need one additional table to hold the "manner in which the audit
type was received/came in". Let's call this tblHowReceived. This table
should have these two fields at least:
HowReceivedID (primary key)
HowReceivedName

You then would add one more field to EmpCorrWork table -- it would be the
HowReceivedID field, which would be a foreign key back to tblHowReceived
table. On your subform, you'd then put another combo box to let the user
select the HowReceived item and store it in the table.

Now, depending upon your database desires, you may want another junction
table to relate the HowReceived record to a specific audit type (CorrID). Do
you want to limit the choices of HowReceived based on which CorrID is
selected? If yes, then you'd need cascading combo boxes for the CorrID and
HowReceivedID selections. But let's hold off on this for the moment unless
you really want to do it -- there are a few "tricks" to do this on a
continuous forms view -- not difficult, but it would mean that the
HowReceived combo box would have a different RowSource query for each
record, and a previous record on the subform may have a value for
HowReceived that isn't in the combo box's Row Source at the moment.

--

Ken Snell
MS ACCESS MVP


"Melissa" wrote in message
...
On your question "are the sort fields the type of bills for each audit
type.Yes. and each sort fields are on by which source the correspondence
came
in which would be received. Example below:

File Room- Phone Calls-TP/Corres Petitions-AG E-Mail-Walk-ins-TPS-Other
Agents-Bankruptcy E-Mail-Tax Appeal-By Fax-ChecksRec'd

There is also a sort on each type of Audit Type on what was completed. So
each Audit type sort or bill would have both of these sorts.

Example below.

Pending Bill/- Billing/- Assessment/- Certified Assmt

Thanks again for all your help. I have been trying to get this together
for
months now and my designs are not cutting it.












"Ken Snell [MVP]" wrote:

My error -- I was designing this form in my head, and overlooked some
things.

Yes, you'll want to use a subform instead of the Detail section for
entering
the CorrID and WorkQuantity values. This subform's RecordSource will be
the
EmpCorrWork table. The subform also should have textboxes bound to the
EmpID
and WorkDate fields, but set the Visibility property of these textboxes
to
No.

Keep the combo box for EmpID selection and the textbox for WorkDate entry
in
the main form's ReportHeader section. The main form should have no
RecordSource at all.

The linking fields from main form to the subform will be these (use real
names):
LinkChildFields: EmpID,WorkDate
LinkMasterFields: EmpIDComboBox, WorkDateTextBox

This should fix the data entry problem you were having.

I am not understanding how CorrID (which you're using as the type of
audit)
is related to the various sort fields. Are the sort fields the "type of
bills" for each audit type? I need you to fully spell out the entities
involved in your data model. It appears that you may need an additional
field in the EmpCorrWork table, or that you may need another table. I
just
cannot tell yet without a better picture of your data model.

--

Ken Snell
MS ACCESS MVP


"Melissa" wrote in message
...
I related the EmpID and CorrID to the parent tables. I then made a combo
box
on the form for both of these fields. The Correspondence Name consists
of
all
50 different types of correspondence(audit types) received which will
be
chosen from a drop down list. Now grant it, no one employee will do 50
different audit types in a day (or work the correp from all 50) but,
they
will at the very minimun do 3 different types. And each of those types
usually comes in through different avenues. A audit type would be for
example; ST NR (sales tax no remit) or Corporate DQ (delinquency) etc.
Each
tax type can have different types of bills. That is why there is so
many.

In the third table EmpCorrWork I did not include the EmpName or
CorrName
since the EmpID and CorrId is already linked to these tables. In my
form I
placed the EmpName and Date in the FormHeader and the detail section I
put
the different sorts of how they would receive the files. Phone, FileRm
etc.
The form would not allow me to tab down to any of my fields within the
detail
section after I enter the date. Should I use a subform for the detail
section?

Hope I answered your question and thanks again for all your help.

"Ken Snell [MVP]" wrote:

I need to better understand the relationship of the "sort" fields to
the
CorrID entity (which I am using as the identity of a type of
correspondence
from your earlier list). What are these fields, how are they used,
what
type
of data entry is needed for them, etc.?

--

Ken Snell
MS ACCESS MVP

"Melissa" wrote in message
...
Thanks for your help Ken. I have completed doing the suggestions you
have
offered. But have a couple questions on the tblEmpCorrWork.
On the third table you suggested only to put the 4 fields into the
table,
then to build the form based on a qry of those fields. In this
table,
there
would be more than 4 fields because each audit type can have up to 9
different sort fields. Would these fields go into a different table?
I
put
these fields into the table 3 with the EmpID and CorrID being the
foreign
keys back to the parent. What is happening is it is associating each
agent
with a particular audit code when each agent may work many different
types
of
audits. Thanks again for your help



snipped

You don't need to link the fields in the tables, although aving Referential
Integrity for links between the primary key fields in the parent tables and
the corresponding foreign key fields in the children tables is usually a
good thing to do.

The "link" that I'm talking of are the properties mentioned for the subform
control (the control that actually holds the subform object). If you look at
the Properties (Data tab) for the subform control, you'll see those
properties listed there.

If HowReceived can be different for the same EmpID / CorrID / WorkDate
combination, then you'll need to add HowReceived to the composite primary
key in EmpCorrWork table -- making the primary key a composite of four
fields. That will let you enter the different HowReceived choices for a
single CorrID value.

What you're proposing is a low-medium complexity for ACCESS -- doesn't sound
like it's that far above the skill level that you had before. Just keep
taking it one step at a time, and it'll "click into place" for you. This
structure that we're discussing is just a level up from relating two
tables -- you're still relating one table to another, but that child
(junction) table just has more than one table related to it, that's all.
--

Ken Snell
MS ACCESS MVP



"Melissa" wrote in message
...
On this response: The linking fields from main form to the subform will be
these (use real
names):
LinkChildFields: EmpID,WorkDate
LinkMasterFields: EmpIDComboBox, WorkDateTextBox

I am having a problem linking these fields within the form. I then went to
the table
to link the child field to master and was having trouble with this as
well.

Also, on the 10/25 suggestion on the How Received combo box, there could
be
more than one how received in a day per audit type.

Just a question, do you think that this database that I am trying to build
is to technical for a intermediate user such as myself. I used to be
pretty
good at building these but have lost my abililty to get the tables sorted.
I
feel if I could get my tables and relationships in order, I could go from
there.

Thanks again.

"Ken Snell [MVP]" wrote:

OK so we'll need one additional table to hold the "manner in which the
audit
type was received/came in". Let's call this tblHowReceived. This table
should have these two fields at least:
HowReceivedID (primary key)
HowReceivedName

You then would add one more field to EmpCorrWork table -- it would be the
HowReceivedID field, which would be a foreign key back to tblHowReceived
table. On your subform, you'd then put another combo box to let the user
select the HowReceived item and store it in the table.

Now, depending upon your database desires, you may want another junction
table to relate the HowReceived record to a specific audit type (CorrID).
Do
you want to limit the choices of HowReceived based on which CorrID is
selected? If yes, then you'd need cascading combo boxes for the CorrID
and
HowReceivedID selections. But let's hold off on this for the moment
unless
you really want to do it -- there are a few "tricks" to do this on a
continuous forms view -- not difficult, but it would mean that the
HowReceived combo box would have a different RowSource query for each
record, and a previous record on the subform may have a value for
HowReceived that isn't in the combo box's Row Source at the moment.

--

Ken Snell
MS ACCESS MVP


"Melissa" wrote in message
...
On your question "are the sort fields the type of bills for each audit
type.Yes. and each sort fields are on by which source the
correspondence
came
in which would be received. Example below:

File Room- Phone Calls-TP/Corres Petitions-AG E-Mail-Walk-ins-TPS-Other
Agents-Bankruptcy E-Mail-Tax Appeal-By Fax-ChecksRec'd

There is also a sort on each type of Audit Type on what was completed.
So
each Audit type sort or bill would have both of these sorts.

Example below.

Pending Bill/- Billing/- Assessment/- Certified Assmt

Thanks again for all your help. I have been trying to get this together
for
months now and my designs are not cutting it.












"Ken Snell [MVP]" wrote:

My error -- I was designing this form in my head, and overlooked some
things.

Yes, you'll want to use a subform instead of the Detail section for
entering
the CorrID and WorkQuantity values. This subform's RecordSource will
be
the
EmpCorrWork table. The subform also should have textboxes bound to the
EmpID
and WorkDate fields, but set the Visibility property of these
textboxes
to
No.

Keep the combo box for EmpID selection and the textbox for WorkDate
entry
in
the main form's ReportHeader section. The main form should have no
RecordSource at all.

The linking fields from main form to the subform will be these (use
real
names):
LinkChildFields: EmpID,WorkDate
LinkMasterFields: EmpIDComboBox, WorkDateTextBox

This should fix the data entry problem you were having.

I am not understanding how CorrID (which you're using as the type of
audit)
is related to the various sort fields. Are the sort fields the "type
of
bills" for each audit type? I need you to fully spell out the entities
involved in your data model. It appears that you may need an
additional
field in the EmpCorrWork table, or that you may need another table. I
just
cannot tell yet without a better picture of your data model.

--

Ken Snell
MS ACCESS MVP


"Melissa" wrote in message
...
I related the EmpID and CorrID to the parent tables. I then made a
combo
box
on the form for both of these fields. The Correspondence Name
consists
of
all
50 different types of correspondence(audit types) received which
will
be
chosen from a drop down list. Now grant it, no one employee will do
50
different audit types in a day (or work the correp from all 50) but,
they
will at the very minimun do 3 different types. And each of those
types
usually comes in through different avenues. A audit type would be
for
example; ST NR (sales tax no remit) or Corporate DQ (delinquency)
etc.
Each
tax type can have different types of bills. That is why there is so
many.

In the third table EmpCorrWork I did not include the EmpName or
CorrName
since the EmpID and CorrId is already linked to these tables. In my
form I
placed the EmpName and Date in the FormHeader and the detail section
I
put
the different sorts of how they would receive the files. Phone,
FileRm
etc.
The form would not allow me to tab down to any of my fields within
the
detail
section after I enter the date. Should I use a subform for the
detail
section?

Hope I answered your question and thanks again for all your help.

"Ken Snell [MVP]" wrote:

I need to better understand the relationship of the "sort" fields
to
the
CorrID entity (which I am using as the identity of a type of
correspondence
from your earlier list). What are these fields, how are they used,
what
type
of data entry is needed for them, etc.?

--

Ken Snell
MS ACCESS MVP

"Melissa" wrote in message
...
Thanks for your help Ken. I have completed doing the suggestions
you
have
offered. But have a couple questions on the tblEmpCorrWork.
On the third table you suggested only to put the 4 fields into
the
table,
then to build the form based on a qry of those fields. In this
table,
there
would be more than 4 fields because each audit type can have up
to 9
different sort fields. Would these fields go into a different
table?
I
put
these fields into the table 3 with the EmpID and CorrID being the
foreign
keys back to the parent. What is happening is it is associating
each
agent
with a particular audit code when each agent may work many
different
types
of
audits. Thanks again for your help



snipped

Hi Ken,

This is the table design I came up with. I was having to many problems with
the other ones. I have 3 tbls.

Corresp Recd/Assigned tbl
EmpID(autonumerPrimaryK) (many side of the relationship)
AuditTypeID
InventoryPending
PendingBill
etc..
(which consists of the 16 fields in which corresp can come in)


Emptbl
EmpID (one side of the relationship)
EmployeeName
Supervisor
DateCompleted
Hrsworkingcorresp
HrsPhoneDuty
HrsAbsent
etc....((which consists of the 14 fields in to track personal stuff, such as
training, attending classes etc.)

AuditTypetbl
AuditTypeID (one to many to corresp Recd tbl)
AuditType
(This tbl holds all the audit types)
this is used for the drop down list in the CorrespRecd/Assign.

Now I have created a main form from the employee tbl. Then I inserted a
subform
at the bottom from the corresp/recd table. My linked fields are the EmpID
field.

Ok, now I am having a small problem. Both Main and sub are set to DatyEntry.
When I enter my information in the Main form which is fine, it allows me to
tab down to my subform and enter my corresp type and how many I completed
which is fine. When I tab out of the last field. My focus goes back to the
Subform on my first field, when I want the focus to move to another record on
the Mainform. How can I make this happen? Basically, I want the Main and the
Sub to act like one Data Entry form.

Thanks again for all your help.




"Ken Snell [MVP]" wrote:

You don't need to link the fields in the tables, although aving Referential
Integrity for links between the primary key fields in the parent tables and
the corresponding foreign key fields in the children tables is usually a
good thing to do.

The "link" that I'm talking of are the properties mentioned for the subform
control (the control that actually holds the subform object). If you look at
the Properties (Data tab) for the subform control, you'll see those
properties listed there.

If HowReceived can be different for the same EmpID / CorrID / WorkDate
combination, then you'll need to add HowReceived to the composite primary
key in EmpCorrWork table -- making the primary key a composite of four
fields. That will let you enter the different HowReceived choices for a
single CorrID value.

What you're proposing is a low-medium complexity for ACCESS -- doesn't sound
like it's that far above the skill level that you had before. Just keep
taking it one step at a time, and it'll "click into place" for you. This
structure that we're discussing is just a level up from relating two
tables -- you're still relating one table to another, but that child
(junction) table just has more than one table related to it, that's all.
--

Ken Snell
MS ACCESS MVP



"Melissa" wrote in message
...
On this response: The linking fields from main form to the subform will be
these (use real
names):
LinkChildFields: EmpID,WorkDate
LinkMasterFields: EmpIDComboBox, WorkDateTextBox

I am having a problem linking these fields within the form. I then went to
the table
to link the child field to master and was having trouble with this as
well.

Also, on the 10/25 suggestion on the How Received combo box, there could
be
more than one how received in a day per audit type.

Just a question, do you think that this database that I am trying to build
is to technical for a intermediate user such as myself. I used to be
pretty
good at building these but have lost my abililty to get the tables sorted.
I
feel if I could get my tables and relationships in order, I could go from
there.

Thanks again.

"Ken Snell [MVP]" wrote:

OK so we'll need one additional table to hold the "manner in which the
audit
type was received/came in". Let's call this tblHowReceived. This table
should have these two fields at least:
HowReceivedID (primary key)
HowReceivedName

You then would add one more field to EmpCorrWork table -- it would be the
HowReceivedID field, which would be a foreign key back to tblHowReceived
table. On your subform, you'd then put another combo box to let the user
select the HowReceived item and store it in the table.

Now, depending upon your database desires, you may want another junction
table to relate the HowReceived record to a specific audit type (CorrID).
Do
you want to limit the choices of HowReceived based on which CorrID is
selected? If yes, then you'd need cascading combo boxes for the CorrID
and
HowReceivedID selections. But let's hold off on this for the moment
unless
you really want to do it -- there are a few "tricks" to do this on a
continuous forms view -- not difficult, but it would mean that the
HowReceived combo box would have a different RowSource query for each
record, and a previous record on the subform may have a value for
HowReceived that isn't in the combo box's Row Source at the moment.

--

Ken Snell
MS ACCESS MVP


"Melissa" wrote in message
...
On your question "are the sort fields the type of bills for each audit
type.Yes. and each sort fields are on by which source the
correspondence
came
in which would be received. Example below:

File Room- Phone Calls-TP/Corres Petitions-AG E-Mail-Walk-ins-TPS-Other
Agents-Bankruptcy E-Mail-Tax Appeal-By Fax-ChecksRec'd

There is also a sort on each type of Audit Type on what was completed.
So
each Audit type sort or bill would have both of these sorts.

Example below.

Pending Bill/- Billing/- Assessment/- Certified Assmt

Thanks again for all your help. I have been trying to get this together
for
months now and my designs are not cutting it.












"Ken Snell [MVP]" wrote:

My error -- I was designing this form in my head, and overlooked some
things.

Yes, you'll want to use a subform instead of the Detail section for
entering
the CorrID and WorkQuantity values. This subform's RecordSource will
be
the
EmpCorrWork table. The subform also should have textboxes bound to the
EmpID
and WorkDate fields, but set the Visibility property of these
textboxes
to
No.

Keep the combo box for EmpID selection and the textbox for WorkDate
entry
in
the main form's ReportHeader section. The main form should have no
RecordSource at all.

The linking fields from main form to the subform will be these (use
real
names):
LinkChildFields: EmpID,WorkDate
LinkMasterFields: EmpIDComboBox, WorkDateTextBox

This should fix the data entry problem you were having.

I am not understanding how CorrID (which you're using as the type of
audit)
is related to the various sort fields. Are the sort fields the "type
of
bills" for each audit type? I need you to fully spell out the entities
involved in your data model. It appears that you may need an
additional
field in the EmpCorrWork table, or that you may need another table. I
just
cannot tell yet without a better picture of your data model.

--

Ken Snell
MS ACCESS MVP


"Melissa" wrote in message
...
I related the EmpID and CorrID to the parent tables. I then made a
combo
box
on the form for both of these fields. The Correspondence Name
consists
of
all
50 different types of correspondence(audit types) received which
will
be
chosen from a drop down list. Now grant it, no one employee will do
50
different audit types in a day (or work the correp from all 50) but,
they
will at the very minimun do 3 different types. And each of those
types
usually comes in through different avenues. A audit type would be
for
example; ST NR (sales tax no remit) or Corporate DQ (delinquency)
etc.
Each
tax type can have different types of bills. That is why there is so
many.

In the third table EmpCorrWork I did not include the EmpName or
CorrName
since the EmpID and CorrId is already linked to these tables. In my
form I
placed the EmpName and Date in the FormHeader and the detail section
I
put
the different sorts of how they would receive the files. Phone,
FileRm
etc.
The form would not allow me to tab down to any of my fields within
the
detail
section after I enter the date. Should I use a subform for the
detail
section?

Hope I answered your question and thanks again for all your help.

"Ken Snell [MVP]" wrote:

I need to better understand the relationship of the "sort" fields
to
the
CorrID entity (which I am using as the identity of a type of
correspondence
from your earlier list). What are these fields, how are they used,
what
type
of data entry is needed for them, etc.?

--

Ken Snell
MS ACCESS MVP

"Melissa" wrote in message
...
Thanks for your help Ken. I have completed doing the suggestions
you
have
offered. But have a couple questions on the tblEmpCorrWork.
On the third table you suggested only to put the 4 fields into
the
table,
then to build the form based on a qry of those fields. In this
table,
there
would be more than 4 fields because each audit type can have up
to 9
different sort fields. Would these fields go into a different
table?
I
put
these fields into the table 3 with the EmpID and CorrID being the
foreign
keys back to the parent. What is happening is it is associating
each
agent
with a particular audit code when each agent may work many
different
types
of
audits. Thanks again for your help



snipped

Don't use / and other non-letter and non-number characters in table names.
If you forget to use [ ] characters to delimit the names all the time, you
may get results that you didn't expect.

What you want for the main form and subform together can be done only via
macro or VBA programming, and is doable that way. However, I caution you
about designing your form this way, because it will prevent you (or the
user) from being able to edit data or do things slightly differently.

You can use Ctrl+Tab to move the cursor from the subform to the main form.
From there, the user can then go to a new record for the main form.

If you really want to do it progammatically, you may want to do it via a
command button that moves the focus to the main form and then moves the main
form to a new record.

To do it via tabbing, the trick is that the subform won't "know" when the
last record has been entered (unless you're only entering a single record
via the subform). If you can give me more details about the subform's
setup/contents, I might be able to suggest a way to have the subform do the
"move focus to main form etc." just by tabbing out of the last control in
the last record.

--

Ken Snell
MS ACCESS MVP



"Melissa" wrote in message
...
Hi Ken,

This is the table design I came up with. I was having to many problems
with
the other ones. I have 3 tbls.

Corresp Recd/Assigned tbl
EmpID(autonumerPrimaryK) (many side of the relationship)
AuditTypeID
InventoryPending
PendingBill
etc..
(which consists of the 16 fields in which corresp can come in)


Emptbl
EmpID (one side of the relationship)
EmployeeName
Supervisor
DateCompleted
Hrsworkingcorresp
HrsPhoneDuty
HrsAbsent
etc....((which consists of the 14 fields in to track personal stuff, such
as
training, attending classes etc.)

AuditTypetbl
AuditTypeID (one to many to corresp Recd tbl)
AuditType
(This tbl holds all the audit types)
this is used for the drop down list in the CorrespRecd/Assign.

Now I have created a main form from the employee tbl. Then I inserted a
subform
at the bottom from the corresp/recd table. My linked fields are the EmpID
field.

Ok, now I am having a small problem. Both Main and sub are set to
DatyEntry.
When I enter my information in the Main form which is fine, it allows me
to
tab down to my subform and enter my corresp type and how many I completed
which is fine. When I tab out of the last field. My focus goes back to the
Subform on my first field, when I want the focus to move to another record
on
the Mainform. How can I make this happen? Basically, I want the Main and
the
Sub to act like one Data Entry form.

Thanks again for all your help.




"Ken Snell [MVP]" wrote:

You don't need to link the fields in the tables, although aving
Referential
Integrity for links between the primary key fields in the parent tables
and
the corresponding foreign key fields in the children tables is usually a
good thing to do.

The "link" that I'm talking of are the properties mentioned for the
subform
control (the control that actually holds the subform object). If you look
at
the Properties (Data tab) for the subform control, you'll see those
properties listed there.

If HowReceived can be different for the same EmpID / CorrID / WorkDate
combination, then you'll need to add HowReceived to the composite primary
key in EmpCorrWork table -- making the primary key a composite of four
fields. That will let you enter the different HowReceived choices for a
single CorrID value.

What you're proposing is a low-medium complexity for ACCESS -- doesn't
sound
like it's that far above the skill level that you had before. Just keep
taking it one step at a time, and it'll "click into place" for you. This
structure that we're discussing is just a level up from relating two
tables -- you're still relating one table to another, but that child
(junction) table just has more than one table related to it, that's all.
--

Ken Snell
MS ACCESS MVP



"Melissa" wrote in message
...
On this response: The linking fields from main form to the subform will
be
these (use real
names):
LinkChildFields: EmpID,WorkDate
LinkMasterFields: EmpIDComboBox, WorkDateTextBox

I am having a problem linking these fields within the form. I then went
to
the table
to link the child field to master and was having trouble with this as
well.

Also, on the 10/25 suggestion on the How Received combo box, there
could
be
more than one how received in a day per audit type.

Just a question, do you think that this database that I am trying to
build
is to technical for a intermediate user such as myself. I used to be
pretty
good at building these but have lost my abililty to get the tables
sorted.
I
feel if I could get my tables and relationships in order, I could go
from
there.

Thanks again.

"Ken Snell [MVP]" wrote:

OK so we'll need one additional table to hold the "manner in which the
audit
type was received/came in". Let's call this tblHowReceived. This table
should have these two fields at least:
HowReceivedID (primary key)
HowReceivedName

You then would add one more field to EmpCorrWork table -- it would be
the
HowReceivedID field, which would be a foreign key back to
tblHowReceived
table. On your subform, you'd then put another combo box to let the
user
select the HowReceived item and store it in the table.

Now, depending upon your database desires, you may want another
junction
table to relate the HowReceived record to a specific audit type
(CorrID).
Do
you want to limit the choices of HowReceived based on which CorrID is
selected? If yes, then you'd need cascading combo boxes for the CorrID
and
HowReceivedID selections. But let's hold off on this for the moment
unless
you really want to do it -- there are a few "tricks" to do this on a
continuous forms view -- not difficult, but it would mean that the
HowReceived combo box would have a different RowSource query for each
record, and a previous record on the subform may have a value for
HowReceived that isn't in the combo box's Row Source at the moment.

--

Ken Snell
MS ACCESS MVP


"Melissa" wrote in message
...
On your question "are the sort fields the type of bills for each
audit
type.Yes. and each sort fields are on by which source the
correspondence
came
in which would be received. Example below:

File Room- Phone Calls-TP/Corres Petitions-AG
E-Mail-Walk-ins-TPS-Other
Agents-Bankruptcy E-Mail-Tax Appeal-By Fax-ChecksRec'd

There is also a sort on each type of Audit Type on what was
completed.
So
each Audit type sort or bill would have both of these sorts.

Example below.

Pending Bill/- Billing/- Assessment/- Certified Assmt

Thanks again for all your help. I have been trying to get this
together
for
months now and my designs are not cutting it.












"Ken Snell [MVP]" wrote:

My error -- I was designing this form in my head, and overlooked
some
things.

Yes, you'll want to use a subform instead of the Detail section for
entering
the CorrID and WorkQuantity values. This subform's RecordSource
will
be
the
EmpCorrWork table. The subform also should have textboxes bound to
the
EmpID
and WorkDate fields, but set the Visibility property of these
textboxes
to
No.

Keep the combo box for EmpID selection and the textbox for WorkDate
entry
in
the main form's ReportHeader section. The main form should have no
RecordSource at all.

The linking fields from main form to the subform will be these (use
real
names):
LinkChildFields: EmpID,WorkDate
LinkMasterFields: EmpIDComboBox, WorkDateTextBox

This should fix the data entry problem you were having.

I am not understanding how CorrID (which you're using as the type
of
audit)
is related to the various sort fields. Are the sort fields the
"type
of
bills" for each audit type? I need you to fully spell out the
entities
involved in your data model. It appears that you may need an
additional
field in the EmpCorrWork table, or that you may need another table.
I
just
cannot tell yet without a better picture of your data model.

--

Ken Snell
MS ACCESS MVP


"Melissa" wrote in message
...
I related the EmpID and CorrID to the parent tables. I then made a
combo
box
on the form for both of these fields. The Correspondence Name
consists
of
all
50 different types of correspondence(audit types) received which
will
be
chosen from a drop down list. Now grant it, no one employee will
do
50
different audit types in a day (or work the correp from all 50)
but,
they
will at the very minimun do 3 different types. And each of those
types
usually comes in through different avenues. A audit type would be
for
example; ST NR (sales tax no remit) or Corporate DQ (delinquency)
etc.
Each
tax type can have different types of bills. That is why there is
so
many.

In the third table EmpCorrWork I did not include the EmpName or
CorrName
since the EmpID and CorrId is already linked to these tables. In
my
form I
placed the EmpName and Date in the FormHeader and the detail
section
I
put
the different sorts of how they would receive the files. Phone,
FileRm
etc.
The form would not allow me to tab down to any of my fields
within
the
detail
section after I enter the date. Should I use a subform for the
detail
section?

Hope I answered your question and thanks again for all your help.

"Ken Snell [MVP]" wrote:

I need to better understand the relationship of the "sort"
fields
to
the
CorrID entity (which I am using as the identity of a type of
correspondence
from your earlier list). What are these fields, how are they
used,
what
type
of data entry is needed for them, etc.?

--

Ken Snell
MS ACCESS MVP

"Melissa" wrote in message
...
Thanks for your help Ken. I have completed doing the
suggestions
you
have
offered. But have a couple questions on the tblEmpCorrWork.
On the third table you suggested only to put the 4 fields into
the
table,
then to build the form based on a qry of those fields. In this
table,
there
would be more than 4 fields because each audit type can have
up
to 9
different sort fields. Would these fields go into a different
table?
I
put
these fields into the table 3 with the EmpID and CorrID being
the
foreign
keys back to the parent. What is happening is it is
associating
each
agent
with a particular audit code when each agent may work many
different
types
of
audits. Thanks again for your help



snipped

Hi Ken,

I think I have everything in place. I have three tbls. I am only giving the
primary keys in the examples below.

EmployeeHrs
EmpID
DateCompleted (are composite keys)
etc.

Employee tbl
EmpID (primary key)

Correpondence Received Completed tbl
DateCompleted
AuditType
EmpID (these three fields are composite keys) Great advice on the composite
keys by the way.

My question is, I have my Employeetbl linked to EmployeeHrs (one to many)
and also the Employeetbl linked to Corresp Reced Completed (one to many).
Should I link the EmployeeHrs to the Corresp Reced Completed tbl (one to
many)? When I try to do this I get a relationship type of "Indeterminate",
what is that? I will begin to work on my reports next week and I do not want
to run into trouble.

Second Question:
I have my Data Entry form set up and is running great. If you recall, My
main form is Employee Hrs (which can only have one record per day per agent)
and the Subform is Correp Recd Completed (which can have multi entries per
day by the same agent but, not the same Audit type).

Based on that, if an employee wants to print their production that they
entered that day, how can I set this up with a button? If they print record
it comes up blank because of the form being set to data entry only. Also, if
that is change only the main form can be printed?

Thank you so much for all your guidance. You have been a great help to me.





"Ken Snell [MVP]" wrote:

Don't use / and other non-letter and non-number characters in table names.
If you forget to use [ ] characters to delimit the names all the time, you
may get results that you didn't expect.

What you want for the main form and subform together can be done only via
macro or VBA programming, and is doable that way. However, I caution you
about designing your form this way, because it will prevent you (or the
user) from being able to edit data or do things slightly differently.

You can use Ctrl+Tab to move the cursor from the subform to the main form.
From there, the user can then go to a new record for the main form.

If you really want to do it progammatically, you may want to do it via a
command button that moves the focus to the main form and then moves the main
form to a new record.

To do it via tabbing, the trick is that the subform won't "know" when the
last record has been entered (unless you're only entering a single record
via the subform). If you can give me more details about the subform's
setup/contents, I might be able to suggest a way to have the subform do the
"move focus to main form etc." just by tabbing out of the last control in
the last record.

--

Ken Snell
MS ACCESS MVP



"Melissa" wrote in message
...
Hi Ken,

This is the table design I came up with. I was having to many problems
with
the other ones. I have 3 tbls.

Corresp Recd/Assigned tbl
EmpID(autonumerPrimaryK) (many side of the relationship)
AuditTypeID
InventoryPending
PendingBill
etc..
(which consists of the 16 fields in which corresp can come in)


Emptbl
EmpID (one side of the relationship)
EmployeeName
Supervisor
DateCompleted
Hrsworkingcorresp
HrsPhoneDuty
HrsAbsent
etc....((which consists of the 14 fields in to track personal stuff, such
as
training, attending classes etc.)

AuditTypetbl
AuditTypeID (one to many to corresp Recd tbl)
AuditType
(This tbl holds all the audit types)
this is used for the drop down list in the CorrespRecd/Assign.

Now I have created a main form from the employee tbl. Then I inserted a
subform
at the bottom from the corresp/recd table. My linked fields are the EmpID
field.

Ok, now I am having a small problem. Both Main and sub are set to
DatyEntry.
When I enter my information in the Main form which is fine, it allows me
to
tab down to my subform and enter my corresp type and how many I completed
which is fine. When I tab out of the last field. My focus goes back to the
Subform on my first field, when I want the focus to move to another record
on
the Mainform. How can I make this happen? Basically, I want the Main and
the
Sub to act like one Data Entry form.

Thanks again for all your help.




"Ken Snell [MVP]" wrote:

You don't need to link the fields in the tables, although aving
Referential
Integrity for links between the primary key fields in the parent tables
and
the corresponding foreign key fields in the children tables is usually a
good thing to do.

The "link" that I'm talking of are the properties mentioned for the
subform
control (the control that actually holds the subform object). If you look
at
the Properties (Data tab) for the subform control, you'll see those
properties listed there.

If HowReceived can be different for the same EmpID / CorrID / WorkDate
combination, then you'll need to add HowReceived to the composite primary
key in EmpCorrWork table -- making the primary key a composite of four
fields. That will let you enter the different HowReceived choices for a
single CorrID value.

What you're proposing is a low-medium complexity for ACCESS -- doesn't
sound
like it's that far above the skill level that you had before. Just keep
taking it one step at a time, and it'll "click into place" for you. This
structure that we're discussing is just a level up from relating two
tables -- you're still relating one table to another, but that child
(junction) table just has more than one table related to it, that's all.
--

Ken Snell
MS ACCESS MVP



"Melissa" wrote in message
...
On this response: The linking fields from main form to the subform will
be
these (use real
names):
LinkChildFields: EmpID,WorkDate
LinkMasterFields: EmpIDComboBox, WorkDateTextBox

I am having a problem linking these fields within the form. I then went
to
the table
to link the child field to master and was having trouble with this as
well.

Also, on the 10/25 suggestion on the How Received combo box, there
could
be
more than one how received in a day per audit type.

Just a question, do you think that this database that I am trying to
build
is to technical for a intermediate user such as myself. I used to be
pretty
good at building these but have lost my abililty to get the tables
sorted.
I
feel if I could get my tables and relationships in order, I could go
from
there.

Thanks again.

"Ken Snell [MVP]" wrote:

OK so we'll need one additional table to hold the "manner in which the
audit
type was received/came in". Let's call this tblHowReceived. This table
should have these two fields at least:
HowReceivedID (primary key)
HowReceivedName

You then would add one more field to EmpCorrWork table -- it would be
the
HowReceivedID field, which would be a foreign key back to
tblHowReceived
table. On your subform, you'd then put another combo box to let the
user
select the HowReceived item and store it in the table.

Now, depending upon your database desires, you may want another
junction
table to relate the HowReceived record to a specific audit type
(CorrID).
Do
you want to limit the choices of HowReceived based on which CorrID is
selected? If yes, then you'd need cascading combo boxes for the CorrID
and
HowReceivedID selections. But let's hold off on this for the moment
unless
you really want to do it -- there are a few "tricks" to do this on a
continuous forms view -- not difficult, but it would mean that the
HowReceived combo box would have a different RowSource query for each
record, and a previous record on the subform may have a value for
HowReceived that isn't in the combo box's Row Source at the moment.

--

Ken Snell
MS ACCESS MVP


"Melissa" wrote in message
...
On your question "are the sort fields the type of bills for each
audit
type.Yes. and each sort fields are on by which source the
correspondence
came
in which would be received. Example below:

File Room- Phone Calls-TP/Corres Petitions-AG
E-Mail-Walk-ins-TPS-Other
Agents-Bankruptcy E-Mail-Tax Appeal-By Fax-ChecksRec'd

There is also a sort on each type of Audit Type on what was
completed.
So
each Audit type sort or bill would have both of these sorts.

Example below.

Pending Bill/- Billing/- Assessment/- Certified Assmt

Thanks again for all your help. I have been trying to get this
together
for
months now and my designs are not cutting it.












"Ken Snell [MVP]" wrote:

My error -- I was designing this form in my head, and overlooked
some
things.

Yes, you'll want to use a subform instead of the Detail section for
entering
the CorrID and WorkQuantity values. This subform's RecordSource
will
be
the
EmpCorrWork table. The subform also should have textboxes bound to
the
EmpID
and WorkDate fields, but set the Visibility property of these
textboxes
to
No.

Keep the combo box for EmpID selection and the textbox for WorkDate
entry
in
the main form's ReportHeader section. The main form should have no
RecordSource at all.

The linking fields from main form to the subform will be these (use
real
names):
LinkChildFields: EmpID,WorkDate
LinkMasterFields: EmpIDComboBox, WorkDateTextBox

This should fix the data entry problem you were having.

I am not understanding how CorrID (which you're using as the type
of
audit)
is related to the various sort fields. Are the sort fields the
"type
of
bills" for each audit type? I need you to fully spell out the
entities
involved in your data model. It appears that you may need an
additional
field in the EmpCorrWork table, or that you may need another table.
I
just
cannot tell yet without a better picture of your data model.

--

Ken Snell
MS ACCESS MVP


"Melissa" wrote in message
...
I related the EmpID and CorrID to the parent tables. I then made a
combo
box
on the form for both of these fields. The Correspondence Name
consists
of
all
50 different types of correspondence(audit types) received which
will
be

Comments inline...

--

Ken Snell
MS ACCESS MVP

"Melissa" wrote in message
...
Hi Ken,

I think I have everything in place. I have three tbls. I am only giving
the
primary keys in the examples below.

EmployeeHrs
EmpID
DateCompleted (are composite keys)
etc.

Employee tbl
EmpID (primary key)

Correpondence Received Completed tbl
DateCompleted
AuditType
EmpID (these three fields are composite keys) Great advice on the
composite
keys by the way.

I would think that you also would want a tblAuditTypes table to hold the
AuditType values, and its primary key would be the foreign key "AuditType"
in the Correpondence Received Completed tbl.



My question is, I have my Employeetbl linked to EmployeeHrs (one to many)
and also the Employeetbl linked to Corresp Reced Completed (one to many).
Should I link the EmployeeHrs to the Corresp Reced Completed tbl (one to
many)? When I try to do this I get a relationship type of "Indeterminate",
what is that? I will begin to work on my reports next week and I do not
want
to run into trouble.

No, there is no need to have any type of link between EmployeeHrs and
Correpondence Received Completed tbl. The EmployeeHrs contains no field that
would connect to the purpose of the Correpondence Received Completed tbl.
The reason for the "Indeterminate" error (when, I assume, you try to create
a relationship between EmpID field in both tables) is because neither table
uses EmpID as a primary key (by itself), so ACCESS would see this join as a
"many-to-many", which it does not support directly for a relationship
setting.



Second Question:
I have my Data Entry form set up and is running great. If you recall, My
main form is Employee Hrs (which can only have one record per day per
agent)
and the Subform is Correp Recd Completed (which can have multi entries per
day by the same agent but, not the same Audit type).

Based on that, if an employee wants to print their production that they
entered that day, how can I set this up with a button? If they print
record
it comes up blank because of the form being set to data entry only. Also,
if
that is change only the main form can be printed?

You'd want a report that is based on a query that uses the Employee tbl and
the Correpondence Received Completed tbl, joined by the EmpID fields. Then
you could code a button on the form that would open the report, which you
would filter based on the current date and the EmpID:


DoCmd.OpenReport "ReportName", , , _
"[EmpID] = " & Me.EmpID.Value & _
" And [DateCompleted] = #" & _
Format(Date(), "mm\/dd\/yyyy") & "#"



Thank you so much for all your guidance. You have been a great help to me.

You're welcome.





snipped

Ok, I understand your answer on both but, I have follow ups.
On how I have my relationships set up currently,
Employeetbl linked to EmployeeHrs (one to many)
and also the Employeetbl linked to Corresp Reced Completed (one to many).
Both by EmpID fields. Is this good? I just don't want to run into any
problems in the future.

Also, the button I could put on the form to run the report. Will the only
area I need to for the line of code is the Report Name?

Thanks again .

"Ken Snell [MVP]" wrote:

Comments inline...

--

Ken Snell
MS ACCESS MVP

"Melissa" wrote in message
...
Hi Ken,

I think I have everything in place. I have three tbls. I am only giving
the
primary keys in the examples below.

EmployeeHrs
EmpID
DateCompleted (are composite keys)
etc.

Employee tbl
EmpID (primary key)

Correpondence Received Completed tbl
DateCompleted
AuditType
EmpID (these three fields are composite keys) Great advice on the
composite
keys by the way.

I would think that you also would want a tblAuditTypes table to hold the
AuditType values, and its primary key would be the foreign key "AuditType"
in the Correpondence Received Completed tbl.



My question is, I have my Employeetbl linked to EmployeeHrs (one to many)
and also the Employeetbl linked to Corresp Reced Completed (one to many).
Should I link the EmployeeHrs to the Corresp Reced Completed tbl (one to
many)? When I try to do this I get a relationship type of "Indeterminate",
what is that? I will begin to work on my reports next week and I do not
want
to run into trouble.

No, there is no need to have any type of link between EmployeeHrs and
Correpondence Received Completed tbl. The EmployeeHrs contains no field that
would connect to the purpose of the Correpondence Received Completed tbl.
The reason for the "Indeterminate" error (when, I assume, you try to create
a relationship between EmpID field in both tables) is because neither table
uses EmpID as a primary key (by itself), so ACCESS would see this join as a
"many-to-many", which it does not support directly for a relationship
setting.



Second Question:
I have my Data Entry form set up and is running great. If you recall, My
main form is Employee Hrs (which can only have one record per day per
agent)
and the Subform is Correp Recd Completed (which can have multi entries per
day by the same agent but, not the same Audit type).

Based on that, if an employee wants to print their production that they
entered that day, how can I set this up with a button? If they print
record
it comes up blank because of the form being set to data entry only. Also,
if
that is change only the main form can be printed?

You'd want a report that is based on a query that uses the Employee tbl and
the Correpondence Received Completed tbl, joined by the EmpID fields. Then
you could code a button on the form that would open the report, which you
would filter based on the current date and the EmpID:


DoCmd.OpenReport "ReportName", , , _
"[EmpID] = " & Me.EmpID.Value & _
" And [DateCompleted] = #" & _
Format(Date(), "mm\/dd\/yyyy") & "#"



Thank you so much for all your guidance. You have been a great help to me.

You're welcome.





snipped

Yes, you have properly related the Employeetbl to EmployeeHrs, and
Employeetbl linked to Corresp Reced Completed through the EmpID fields.

Yes, the code I posted would be used "as is" except change ReportName to the
actual name of the report.
--

Ken Snell
MS ACCESS MVP



"Melissa" wrote in message
...
Ok, I understand your answer on both but, I have follow ups.
On how I have my relationships set up currently,
Employeetbl linked to EmployeeHrs (one to many)
and also the Employeetbl linked to Corresp Reced Completed (one to many).
Both by EmpID fields. Is this good? I just don't want to run into any
problems in the future.

Also, the button I could put on the form to run the report. Will the only
area I need to for the line of code is the Report Name?

Thanks again .

"Ken Snell [MVP]" wrote:

Comments inline...

--

Ken Snell
MS ACCESS MVP

"Melissa" wrote in message
...
Hi Ken,

I think I have everything in place. I have three tbls. I am only giving
the
primary keys in the examples below.

EmployeeHrs
EmpID
DateCompleted (are composite keys)
etc.

Employee tbl
EmpID (primary key)

Correpondence Received Completed tbl
DateCompleted
AuditType
EmpID (these three fields are composite keys) Great advice on the
composite
keys by the way.

I would think that you also would want a tblAuditTypes table to hold the
AuditType values, and its primary key would be the foreign key
"AuditType"
in the Correpondence Received Completed tbl.



My question is, I have my Employeetbl linked to EmployeeHrs (one to
many)
and also the Employeetbl linked to Corresp Reced Completed (one to
many).
Should I link the EmployeeHrs to the Corresp Reced Completed tbl (one
to
many)? When I try to do this I get a relationship type of
"Indeterminate",
what is that? I will begin to work on my reports next week and I do not
want
to run into trouble.

No, there is no need to have any type of link between EmployeeHrs and
Correpondence Received Completed tbl. The EmployeeHrs contains no field
that
would connect to the purpose of the Correpondence Received Completed tbl.
The reason for the "Indeterminate" error (when, I assume, you try to
create
a relationship between EmpID field in both tables) is because neither
table
uses EmpID as a primary key (by itself), so ACCESS would see this join as
a
"many-to-many", which it does not support directly for a relationship
setting.



Second Question:
I have my Data Entry form set up and is running great. If you recall,
My
main form is Employee Hrs (which can only have one record per day per
agent)
and the Subform is Correp Recd Completed (which can have multi entries
per
day by the same agent but, not the same Audit type).

Based on that, if an employee wants to print their production that they
entered that day, how can I set this up with a button? If they print
record
it comes up blank because of the form being set to data entry only.
Also,
if
that is change only the main form can be printed?

You'd want a report that is based on a query that uses the Employee tbl
and
the Correpondence Received Completed tbl, joined by the EmpID fields.
Then
you could code a button on the form that would open the report, which you
would filter based on the current date and the EmpID:


DoCmd.OpenReport "ReportName", , , _
"[EmpID] = " & Me.EmpID.Value & _
" And [DateCompleted] = #" & _
Format(Date(), "mm\/dd\/yyyy") & "#"



Thank you so much for all your guidance. You have been a great help to
me.

You're welcome.





snipped