Table design problem?

[Melissa]

I am building an employee production db at work. The problem I am having is
with the relationships and maybe my table design. I have one table for
employee, Correspondence Received and Correspondence Completed. These
employees receive and complete correspondence from taxpayers each day. There
are 50 different audit types the department would like to track. With each
audit type, there are 15 fields that may are may not apply. So, what I have
currently design, the employee can have multiple entries into the database
"in a day" if working on more than one audit type. What I need is for them to
be able to logg any corresp rec'd or completed (that they did for that day)
into one form. Putting it all together is confusing me. hope this makes
sense. I have been trying to resolve this issue for months and cannot seem to
come up with a solution. Any help would be truly appreciated. I have posted
this question 10/20 but the solution hasn't helped. My tables are as follows:

Table: Employees
EmployeeName- Text
Supervisor-Text
EmployeeID-Autonumber= PrimaryKey

Table: TaxType/AuditType
TaxType/AuditType- Text

Table: Correspondence Received/Assigned
EmployeeID=AutoNumber PrimaryKey
EmployeeName-Text
DateReceived-Date/Time
TaxAuditType- Text
FilesReceived- Number
PhoneCalls-Number
FaxRecd-Number
AdditionalCorrep-Number
Correspfrom TP/Petitions- Number
Checks-Number
ServCtr/Email/Phone-Number
OtherEmployees-Number
DayEndingInventory-Number

Table: Correspondence Completed
EmployeeID-AutoNumber- Primary Key
EmployeeName-Text
DateCompleted-DateTime
TaxAuditType-Text
InventoryPending-Number
PendingBill-Number
Billing-Number
PendingAsmt-Number
Assessment-Number
CertifiedAsmt-Number
Worked/Not Completed-Number

My Relationships are
One to many= Employees(1) to CorrespondenceReceived/Assigned(many)
Related fields are: EmployeeName
One to many= Employee(1) to CorrespondenceCompleted(many)
TaxType/AuditType= one to many on both CorrespondenceRecd and Correspondence
Completed (not sure this is necessary because I use TaxType/audit type for
combo box on forms)

Thank you

 

[PC Datasheet]

It's all very confusing as you have presented it! The Correspondence
Received/Assigned table looks like a list of activities and you want to
record how many times each activity was done. What does this have to do with
correspondence? What are the 15 fields you want to record for each audit
type? In the Correspondence Completed table it looks like you have
correspondence that is either Billing or Assessment and your trying to
record how many of each have been completed??? What is DateCompleted? Is it
the date you completed a certain correspondence or the recording date for
the count of correspondence you completed. The basis of your database
appears to be pretty complex to where you need to write much more detail to
get any help!

--
PC Datasheet
Your Resource For Help With Access, Excel And Word Applications
(e-mail address removed)
www.pcdatasheet.com

If you don't get the help you need in the newsgroup, I can help you for a
very reasonable fee.
Over 1000 Access users have come to me for help.
Remember that a lone man built the Ark. A large group of professionals built
the Titanic.

 

[Melissa]

I am creating this database for work. This is to record each piece of
correspondence a employee works (resolves) in a day. To try to simplify this,
when you receive a bill and do not agree or have a different explaination,
and respond to this bill, this is correspondence received. The correspondence
we are working is just that. The department would like to track how many are
received and completed by an employee each day. We are currently recording
this information into a excel spreadsheet, but with 47 employees and the many
types of audit types, the workbooks are becoming huge. I hope this simplifies
things.

 

[Ken Snell [MVP]]

Melissa -

It appears that you want the employee to enter each correspondence into a
form, and to let the employee select the type of "audit" that is associated
with each corresondence that is done? Is this essentially what you seek to
do?

Or is the "audit" type already associated with each correspondence type, and
you just want to let the employee select the correspondence type, entering
each one separately that the employee did?

It would be helpful if you could show one or two rows of data from the EXCEL
spreadsheet so that we can see the types of data you're capturing per row.
What data will the employee specifically have "in hand" when he/she is ready
to enter a correspondence that is done, and what will the employee need to
select or enter additionally while entering that "in hand" data?

 

[Melissa]

Ken, you get what I am saying and I so appreciate that. The employee would
select the type of Audit he/she is receiving/ and worked for each day. They
would complete their production each day.
The Audit type can be received by 8 different sources; Files Rec'd, a phone
call; email etc. The department has over 50 types of audit that the division
currently works. (This is a billing/assessment dept). Here is one row of
from the datasheet. It did not paste well but, it reads, AgentID 1 on
10/13/2005, (AuditType) Corp DQ Edit (number of files) 25 (same audit type) 5
(additional files recd via phone) then an addition 5 (referrals). We also
want to capture their ending inventory by audit type. Same example would also
apply to the Corresp completed table.

AgentID DateReceived TaxAuditType FilesReceived Phone
Calls FaxRecd AdditionalCorresp CorresfromTP/Petitions Checks ServCtr/Email/Phone AG
Email Other Agents Activity Notices Bankruptcy
Email TaxAppeal TPSReferrals DayEndingInventory
1 10/13/2005 CORP DQ EDITS 25 5 0 0 0 0 0 5 0


I am thinking in essense, that one employee can have multiple sales in one
day and those sales can be different products. (This is just for a
comparision)

Thanks for any help you can give me. I am pretty good at Access and have
built many databases for the dept, but this one has me stumped.

 

[Ken Snell [MVP]]

I can think of a few ways to do this... each with its own complications and
validation needs.

But, let's try something "easy" in terms of setup and design at the moment.

You'll want three tables:

tblEmployees
EmpID ( Primary Key )
EmpName
(etc.)

tblCorrespondenceTypes
CorrID ( Primary Key )
CorrName

tblEmpCorrWork
EmpID ( composite primary key with CorrID and WorkDate )
CorrID ( composite primary key with EmpID and WorkDate )
WorkDate ( composite primary key with EmpID and CorrID )
WorkQuantity

In the third table, EmpID and CorrID would be foreign keys back to the
"parent" tables.

Then, for data entry, I'd create a form. Set the form up as "Continuous
Forms" view. Also set its Data Entry property to Yes (we'll use this form
just for data entry right now). Make the form's Record Source be a query
that is based on tblEmpCorrWork table. All four fields from that table
should be in the query.

In the form's FormHeader section, I'd put a combo box and textbox. The combo
box would display all the employees so that the employee can select his/her
name; the combo box would be bound to the EmpID field in the form's
RecordSource. The textbox is where the work date goes -- can be
automatically entered or can be typed in by employee, or both; the textbox
would be bound to the WorkDate field in the form's RecordSource.

In the form's Detail section, I'd put a combo box that displays all the
correspondence types; this combo box would be bound to the CorrID field in
the form's RecordSource. I'd then put a textbox to allow entry of the number
of items for that specific correspondence type; this textbox would be bound
to the WorkQuantity field in the form's RecordSource.

This setup will not prohibit an employee from selecting the same
correspondence type twice for the same date, but the composite primary key
in the table will prevent the saving of any data where that is done for that
record.

But this should get you started.....
--

Ken Snell
<MS ACCESS MVP>

 

[Melissa]

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

 

[Ken Snell [MVP]]

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.?

 

[Melissa]

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]]

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>

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.

< snipped >

 

[Melissa]

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]]

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]

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]]

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]

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]]

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]

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.

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>

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]]

Comments inline...

--

Ken Snell
<MS ACCESS MVP>

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 >

 

[Melissa]

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 .

Comments inline...

--

Ken Snell
<MS ACCESS MVP>

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 >

 

[Ken Snell [MVP]]

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>

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 .

Comments inline...

--

Ken Snell
<MS ACCESS MVP>

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 >