A Microsoft Office (Excel, Word) forum. OfficeFrustration

If this is your first visit, be sure to check out the FAQ by clicking the link above. You may have to register before you can post: click the register link above to proceed. To start viewing messages, select the forum that you want to visit from the selection below.

Go Back   Home » OfficeFrustration forum » Microsoft Access » Setting Up & Running Reports
Site Map Home Register Authors List Search Today's Posts Mark Forums Read  

Report is not showing data that is automatically generated.



 
 
Thread Tools Display Modes
  #1  
Old January 5th, 2010, 05:36 PM posted to microsoft.public.access.reports
Nancy via AccessMonster.com
external usenet poster
 
Posts: 27
Default Report is not showing data that is automatically generated.

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

  #2  
Old January 5th, 2010, 05:55 PM posted to microsoft.public.access.reports
Marshall Barton
external usenet poster
 
Posts: 5,361
Default Report is not showing data that is automatically generated.

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]
  #3  
Old January 5th, 2010, 10:03 PM posted to microsoft.public.access.reports
nlewallen via AccessMonster.com
external usenet poster
 
Posts: 2
Default Report is not showing data that is automatically generated.

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/For...ports/201001/1

  #4  
Old January 6th, 2010, 12:49 AM posted to microsoft.public.access.reports
Marshall Barton
external usenet poster
 
Posts: 5,361
Default Report is not showing data that is automatically generated.

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.

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.

  #5  
Old January 6th, 2010, 05:44 PM posted to microsoft.public.access.reports
Nancy via AccessMonster.com
external usenet poster
 
Posts: 27
Default Report is not showing data that is automatically generated.

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.


--
Message posted via AccessMonster.com
http://www.accessmonster.com/Uwe/For...ports/201001/1

  #6  
Old January 6th, 2010, 09:27 PM posted to microsoft.public.access.reports
Duane Hookom
external usenet poster
 
Posts: 7,177
Default Report is not showing data that is automatically generated.

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:

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.


--
Message posted via AccessMonster.com
http://www.accessmonster.com/Uwe/For...ports/201001/1

.

  #7  
Old January 6th, 2010, 09:58 PM posted to microsoft.public.access.reports
Nancy via AccessMonster.com
external usenet poster
 
Posts: 27
Default Report is not showing data that is automatically generated.

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

  #8  
Old January 6th, 2010, 11:38 PM posted to microsoft.public.access.reports
Duane Hookom
external usenet poster
 
Posts: 7,177
Default Report is not showing data that is automatically generated.

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:

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

.

 




Thread Tools
Display Modes

Posting Rules
You may not post new threads
You may not post replies
You may not post attachments
You may not edit your posts

vB code is On
Smilies are On
[IMG] code is Off
HTML code is Off
Forum Jump


All times are GMT +1. The time now is 02:03 AM.


Powered by vBulletin® Version 3.6.4
Copyright ©2000 - 2024, Jelsoft Enterprises Ltd.
Copyright ©2004-2024 OfficeFrustration.
The comments are property of their posters.