Solved: Query to populate report

[nsajeff]

Hi, I am trying to do the following:

Prompt user for "ID1" and "ID2" from there I have two queries that run. Each query needs to generate a table. What I want is to populate a report with the table data.

Both queries are complete and I've got the layout of my report setup but I'm stuck at this point.

Any help/direction would be greatly appreciated.

Jeff

[nsajeff]

My table might look something like this(I'm not even sure if outputting to a separate table is the right method):

ID# Name ID#2 ID#3 etc...

My report needs to do something along the lines of:

Hello Mr. ID#
ID#3
ID#4
etc

"Yours Sincerely,
Name"

[Imdabaum]

You can use the query that drives your report and add parameters to it.

Add this line above your SQL statement:
PARAMETERS [prompt:] Text ( 255 ); <------Change 255 to a lower number if you want to restrict size.

Parameters are comma delimitered so if you need more parameters you just ...(255), [prompt2] Text (255), [prompt3] Text (255)... etc.

[nsajeff]

You can use the query that drives your report and add parameters to it.

Add this line above your SQL statement:
PARAMETERS [prompt:] Text ( 255 ); <------Change 255 to a lower number if you want to restrict size.

Parameters are comma delimitered so if you need more parameters you just ...(255), [prompt2] Text (255), [prompt3] Text (255)... etc.

Oh, that's MUCH easier. How do I incorporate the data generated by that query into my report?

Also, thanks for the help and prompt response! VERY much appreciated.

Jeff

[Imdabaum]

I think you can just add them in the Select statement

If you are using the query designer Expr1: [prompt1]

If you are writing your own SQL then SELECT [prompt] as Expr1 where Expr1 is obviously something more meaningful to you.

You should be able to do something like this.

PARAMETERS Name Text ( 255 ), DOB DateTime, Message Text ( 255 );
SELECT [Name] AS Name, [DOB] AS DOB, [Message] AS [Note];

Then bind your report controls to the data sources.

Enjoy.

[nsajeff]

I think you can just add them in the Select statement

If you are using the query designer Expr1: [prompt1]

If you are writing your own SQL then SELECT [prompt] as Expr1 where Expr1 is obviously something more meaningful to you.

You should be able to do something like this.

PARAMETERS Name Text ( 255 ), DOB DateTime, Message Text ( 255 );
SELECT [Name] AS Name, [DOB] AS DOB, [Message] AS [Note];
Then bind your report controls to the data sources.

Enjoy.
Thanks for your help. However, I'm still stuck so I'm going to share more information. I have two queries:

MadIDQuery(sql view):

SELECT DISTINCT mad_MadEntity.MadNumber, mad_MadEntity.MadName, dcs_Roles.RoleId, dcs_Users.UserId, dcs_Roles.RoleTypeId, mad_MadIndividual.IndividualFirstName, mad_MadIndividual.IndividualLastName, mad_MadIndividual.AddressLine1Text, mad_MadIndividual.AddressLine2Text, mad_MadIndividual.CityName, mad_MadIndividual.ProvinceStateText INTO MADIDQueryresults1
FROM ((mad_MadEntity LEFT JOIN dcs_Roles ON mad_MadEntity.MadNumber=dcs_Roles.MadNumber) LEFT JOIN dcs_Users ON dcs_Roles.UserId=dcs_Users.UserId) LEFT JOIN mad_MadIndividual ON dcs_Users.MadIndividualNumber=mad_MadIndividual.MadNumber
WHERE (((mad_MadEntity.MadNumber)=[Enter Mad ID]) AND ((dcs_Roles.RoleTypeId)=1));
and

ExchangeIDQuery:

SELECT dbo_Exchanges.Province, dbo_Exchanges.[Name edited], mad_MadEntity.MadName, dbo_Exchanges.ExchangeID
FROM dbo_Exchanges INNER JOIN mad_MadEntity ON dbo_Exchanges.[ILEC MAD] = mad_MadEntity.MadNumber
WHERE (((dbo_Exchanges.ExchangeID)=[Enter Exchange ID]));
My report is 'Generated Report'.

I need to prompt my user for two values(building a form for this, that part is done).

My MadIDQuery(and eventually my ExchangeIDQuery) does a Make-Table that outputs as 'MadIDQueryresults'.

I need values(like mad_MadEntity.MadName) to populate a a text box from 'MadIDQueryResults'(name text1 for simplicity) in my report. However, when I set the Control Source to, =[MADIDQueryresults1]![MadName] and view my report I simply see "#Name?"

I hope some of this made sense. I'm really quite stuck at this point.

Thanks for all your help so far.

[Imdabaum]

Let me see if I understand..

You have two queries. Each will be used to run a Make-Table query(output MadIDQueryResults and your ExchangeIDQueryResults)
Then you want a values from the (new tables? Or a new query?) to be the source of a textbox in your report?

Essentially, is the recordsource for your report MadIDQueryResults or another source?

[nsajeff]

Let me see if I understand..

You have two queries. Each will be used to run a Make-Table query(output MadIDQueryResults and your ExchangeIDQueryResults)
Then you want a values from the (new tables? Or a new query?) to be the source of a textbox in your report?

Essentially, is the recordsource for your report MadIDQueryResults or another source?

Just wanted to let you know I've solved this part of my problem. Thanks for the help, you're 'the baum'! I've moved on to another part of my problem.