Report is not showing data that is automatically generated.

[Nancy via AccessMonster.com]

I've created a database for a project at work that will track production and
piece work. I've started with a table that I have imported from Excel that
contains the employee's clock number and their name. I have created a form
that allows supervisors to enter in daily production data for each employee.
They enter in the employee's clock number and it automatically generates the
employee's name. (This is the code I used in the employee's name text box: =
[Clock Number].Column(1)

Now I am trying to generate a report from this data. However, when it shows
the information that I entered, it only shows the employee's clock number and
not the name on the report. I'm assuming this is because there is actually
no data entered into that box, it is just generated. How do I get around this?
I actually need to see the employee's name and not their clock number on my
report. Any help would be greatly appreciated. Thanks in advance!

--
Message posted via http://www.accessmonster.com

[Marshall Barton]

Nancy via AccessMonster.com wrote:

>I've created a database for a project at work that will track production and
>piece work. I've started with a table that I have imported from Excel that
>contains the employee's clock number and their name. I have created a form
>that allows supervisors to enter in daily production data for each employee.
>They enter in the employee's clock number and it automatically generates the
>employee's name. (This is the code I used in the employee's name text box: =
>[Clock Number].Column(1)
>
>Now I am trying to generate a report from this data. However, when it shows
>the information that I entered, it only shows the employee's clock number and
>not the name on the report. I'm assuming this is because there is actually
>no data entered into that box, it is just generated. How do I get around this?
>I actually need to see the employee's name and not their clock number on my
>report.

Change the report's record source to a query that includes
the table used in the combo box's row source. Join the two
tables on the clock id field and drag the name field from
the second table down to the query's field list.

--
Marsh
MVP [MS Access]

[nlewallen via AccessMonster.com]

I am so sorry but I'm must not be following you all the way. I'm still a
little rusty at Access (its been around 3 years since I've really messed with
it). Is there any way you can "dumb" that down for me a little or go into
more detail. What I did didn't work. Thanks!

Marshall Barton wrote:
>>I've created a database for a project at work that will track production and
>>piece work. I've started with a table that I have imported from Excel that

>[quoted text clipped - 10 lines]

>>I actually need to see the employee's name and not their clock number on my
>>report.
>
>Change the report's record source to a query that includes
>the table used in the combo box's row source. Join the two
>tables on the clock id field and drag the name field from
>the second table down to the query's field list.
>

--
Message posted via AccessMonster.com
http://www.accessmonster.com/Uwe/Forums.aspx/access-reports/201001/1

[Marshall Barton]

Create a new query add the table you used as the form's
Record Source and add the table that you used as the combo
box's Row Source. Drag from the ID field one table to the
corres[onding field in the other table to link them.

Drag the fields needed by the report from both table doen to

the query's field list.

Save the query and set the report's Record Source to the
query.

--
Marsh
MVP [MS Access]

nlewallen via AccessMonster.com wrote:
>I am so sorry but I'm must not be following you all the way. I'm still a
>little rusty at Access (its been around 3 years since I've really messed with
>it). Is there any way you can "dumb" that down for me a little or go into
>more detail. What I did didn't work.
>

[Nancy via AccessMonster.com]

Ok, I did what you said but it is not showing any data that was entered into
the form when I pull the report. This is the SQL view of my record souce (the
query that I created):

SELECT [Employee Data].[Clock Number] AS [Employee Data_Clock Number],
[Employee Data].Employee AS [Employee Data_Employee], [Employee Data].Rate AS
[Employee Data_Rate], [Employee Data].Bonus AS [Employee Data_Bonus],
[Employee Data].[Current Pay Rate] AS [Employee Data_Current Pay Rate],
[Weekly Data].Supervisor, [Weekly Data].Date, [Weekly Data].[Clock Number] AS
[Weekly Data_Clock Number], [Weekly Data].Employee AS [Weekly Data_Employee],
[Weekly Data].[Hourly Rate], [Weekly Data].Bonus AS [Weekly Data_Bonus],
[Weekly Data].[Current Pay Rate] AS [Weekly Data_Current Pay Rate], [Weekly
Data].[Op Number], [Weekly Data].Description, [Weekly Data].Code, [Weekly
Data].Contract, [Weekly Data].Rate AS [Weekly Data_Rate], [Weekly Data].
Quantity, [Weekly Data].[On Std Hours], [Weekly Data].[Off Std Hours],
[Weekly Data].Day
FROM [Employee Data] INNER JOIN [Weekly Data] ON ([Employee Data].[Current
Pay Rate]=[Weekly Data].[Current Pay Rate]) AND ([Employee Data].[Clock
Number]=[Weekly Data].[Clock Number]) And ([Employee Data].Employee=[Weekly
Data].Employee);

No data at all is pulling up in a report when I base it on this query. It
just shows the page headers. However, when I based the report on the form it
was pulling up everything but the employee's name (which was automatically
generated.) I just want the report to pull up all the data that is showing in
the datasheet view of this form that I have created. Thanks for your help so
far. Maybe you have another suggestion for me?

Marshall Barton wrote:
>Create a new query add the table you used as the form's
>Record Source and add the table that you used as the combo
>box's Row Source. Drag from the ID field one table to the
>corres[onding field in the other table to link them.
>
>Drag the fields needed by the report from both table doen to
>the query's field list.
>
>Save the query and set the report's Record Source to the
>query.

>>I am so sorry but I'm must not be following you all the way. I'm still a
>>little rusty at Access (its been around 3 years since I've really messed with

>[quoted text clipped - 11 lines]

>>>tables on the clock id field and drag the name field from
>>>the second table down to the query's field list.

--

[Duane Hookom]

I am confused by your table structures. I would have thought the only related
field would be an employee ID field. I assume this is the Employee field.

You have to get the query correct first. If you don't see anything in the
data sheet view of the record source, nothing will appear in your report.

Can you explain your tables and their relationship?

--
Duane Hookom
Microsoft Access MVP

"Nancy via AccessMonster.com" wrote:

> .
>

[Nancy via AccessMonster.com]

Sure. I have two tables: Employee Data and Weekly Data. The Employee Data
table contains two fields: Clock Number and Employee. The Weekly Data table
is the table that my form is dumping all of the data into. These fields
contained within it are: Date, Clock Number, Employee (which is automatically
formulated), Quantity, On Std, Off Std, and Day. The last four are basic text
boxes that allow the users to enter data for recording. These two tables are
linked by Clock Number and Employee. All of these show up on my report except
for the Employee. Does this have something to do with being automatically
generated?

Duane Hookom wrote:
>I am confused by your table structures. I would have thought the only related
>field would be an employee ID field. I assume this is the Employee field.
>
>You have to get the query correct first. If you don't see anything in the
>data sheet view of the record source, nothing will appear in your report.
>
>Can you explain your tables and their relationship?
>

>> Ok, I did what you said but it is not showing any data that was entered into
>> the form when I pull the report. This is the SQL view of my record souce (the

>[quoted text clipped - 39 lines]

>> >>>tables on the clock id field and drag the name field from
>> >>>the second table down to the query's field list.

--
Message posted via http://www.accessmonster.com

[Duane Hookom]

Is Employee the primary key in Employee Data or can an employee have more
than one clock number? You stated Employee Data has two fields but your
previous SQL displayed 5 fields with 3 of them used in the join. What's up
with that?

You stated the employee field is "automatically formulated" in the Weekly
Data table. How about the other 2 fields you used to join the tables in the
query?

Is there only one record per employee in the Employee Data table? If so, try
the SQL:

SELECT [Employee Data].[Clock Number] AS [Employee Data_Clock Number],
[Employee Data].Employee AS [Employee Data_Employee],
[Employee Data].Rate AS [Employee Data_Rate],
[Employee Data].Bonus AS [Employee Data_Bonus],
[Employee Data].[Current Pay Rate] AS [Employee Data_Current Pay Rate],
[Weekly Data].Supervisor,
[Weekly Data].Date,
[Weekly Data].[Clock Number] AS [Weekly Data_Clock Number],
[Weekly Data].Employee AS [Weekly Data_Employee],
[Weekly Data].[Hourly Rate],
[Weekly Data].Bonus AS [Weekly Data_Bonus],
[Weekly Data].[Current Pay Rate] AS [Weekly Data_Current Pay Rate],
[Weekly Data].[Op Number],
[Weekly Data].Description,
[Weekly Data].Code,
[Weekly Data].Contract,
[Weekly Data].Rate AS [Weekly Data_Rate],
[Weekly Data].Quantity,
[Weekly Data].[On Std Hours],
[Weekly Data].[Off Std Hours],
[Weekly Data].Day
FROM [Employee Data] INNER JOIN [Weekly Data] ON

([Employee Data].Employee=[Weekly Data].Employee);

Make sure this query returns the same number of records as are present in
the Weekly Data table.

--
Duane Hookom
Microsoft Access MVP

"Nancy via AccessMonster.com" wrote:

> .
>