Anyway to show Parameter in "matched" side of Unmatched Query?

[Hillary]

I am working in our main ERP Database. The permissions in this database default to yes but can be changed to no or read. The table USER_PGM_AUTHORITY only stores no and read permissions.

I have two queries, qryPrograms and Components and qryPrograms and Components by User. The first query has all the Programs and Components the second query has all the no and read permissions for a user. An Unmatched Query between the two returns the yes permissions for a user.

I want to be able to return User_ID which is the parameter for qryPrograms and Components by User but the the nature of the Unmatched Query won't return any records where User_ID exists.

Is there any way to bring a parameter value from the "matched" side of an Unmatched Query?

Thanks,

Hillary

 

[cmmrfrds]

Can you give an example of the fields that you are using to join on between the 2 tables.

 

[Hillary]

Unmatched Side
Program_ID
Program_Component
Menu_String

Matched Side
Program_ID
Program_Component
Menu_String
User_ID

The links are between Program_ID, Program_Component and Menu_String

Hillary

 

[cmmrfrds]

I assume you have a user table. If you add one more table called UserProgram with userid and programid then it will be easier to report on what you need.

 

[Hillary]

No, there is no user table. User_ID sits on table USER_PGM_AUTHORITY.

Linking another table in won't give me what I need.

I am entering the Parameter info in when this Unmatched Query runs. Is there any way to bring in the Parameter info?

Hillary

 

[SkipVought]

Hillary,

Which User_ID for each Program_ID, Program_Component, Menu_String??? Hmmmmmmm???

I assume that there are SCADS of 'em!

Skip,

Want to get great answers to your Tek-Tips questions? Have a look at faq222-2244

 

[Hillary]

I want to return the User_ID from the Parameter.

Program_ID is like a Department. So think Accounting, Purchasing, etc...

Program_Component is like a Function. So think Costing Utilities, Payables, Receiveables (for Accounting) and Purchase Order Entry, Quotes (for Purchasing)

Menu_String is part of the Function...the purpose is to permission part of the Form. For example a user may be able to change a Part description but might not be able to change the unit of measure. Both are on the same form but each is permissionable.

There are a possible 152 permissions. The User_ID does not reside here. For me, I have 83 permissions either No or Read. This is where the User_ID sits. (Ther are a total of 14,807 records on this table but only 83 are for my User_ID) When I run the Unmatched Query it returns 69 (152-83) of the 152 records. User_ID doen't sit on the 152 record table it sits on the 83 record table. Since I am asking only to return the differece of the two I can't pull in the User_ID field.

So, is there anyway to return the value (User_ID) I entered in the Parameter???

I hope this is more clear.

Thanks

Hillary

 

[PHV]

Why not simply add the parameter in the select list ?
SELECT field1, field2, [Enter User_ID] As prmUserID
FROM theTable
WHERE fieldX = [Enter User_ID]

Hope This Helps, PH.
Want to get great answers to your Tek-Tips questions? Have a look at FAQ219-2884 or FAQ222-2244

 

[Hillary]

PHV,

I'm not following you. How do I get the parameter to show?

Thanks,

Hillary

 

[PHV]

Can you please post the SQL code of the query you want the User_ID returned ?

Hope This Helps, PH.
Want to get great answers to your Tek-Tips questions? Have a look at FAQ219-2884 or FAQ222-2244

 

[Hillary]

Here is the SQL for the unmatched query...

SELECT [qryPrograms and Components].[PROGRAM_ID], [qryPrograms and Components].[PROGRAM_COMPONENT], [qryPrograms and Components].[MENU_STRING], "Yes" AS Yes
FROM [qryPrograms and Components] LEFT JOIN [qryPrograms and Components by User] ON ([qryPrograms and Components].[MENU_STRING]=[qryPrograms and Components by User].[MENU_STRING]) AND ([qryPrograms and Components].[PROGRAM_COMPONENT]=[qryPrograms and Components by User].[PROGRAM_COMPONENT]) AND ([qryPrograms and Components].[PROGRAM_ID]=[qryPrograms and Components by User].[PROGRAM_ID])
WHERE ((([qryPrograms and Components by User].[PROGRAM_ID]) Is Null));

Hillary

 

[JonFer]

If you put the same parameter name you are using in the matched query (e.g. [Enter User ID]) in the select list for the unmatched query, it should display there without prompting you a second time for a user ID.

The other way to do this is do make a query from "Programs and Components" where you add the user ID from a parameter. Here is an example of the SQL.

Select [Enter User ID] as UserID,
[qryPrograms and Components].*
From [qryPrograms and Components]

Use this new query in your Left Join to the "Programs and Components by User".

 

[PHV]

I am entering the Parameter info in when this Unmatched Query runs
I don't see any parameter in the posted query.

Hope This Helps, PH.
Want to get great answers to your Tek-Tips questions? Have a look at FAQ219-2884 or FAQ222-2244

 

[JonFer]

The unmatched query is based on the matched query which has the parameter. Running the unmatched query therefore causes the parameter prompt to display.

 

[PHV]

JonFer, my note was for the Hillary's query.

 

[JonFer]

PHV - I know. I took a liberty and answered for Hillary.

 

[Hillary]

JonFer,

I was out of the office late last week. Thanks for answering for me, your better than the Office Assistant

I am being prompted for the parameter, but I was hoping there was some way to report a parameter value in the returned query. You see, I want to create a report based on this query. I can't have a report of permissions without a name (the name is the parameter). But what I think I can do is have this come in as a subreport and show the name in the main report.

Thanks to everyone for your responses...

Hillary

 

[PHV]

I am being prompted for the parameter
How ? Where is a the question coming from ?

Hope This Helps, PH.
Want to get great answers to your Tek-Tips questions? Have a look at FAQ219-2884 or FAQ222-2244

 

[Hillary]

The parameter is coming from qryPrograms and Components by User but [red]nothing[/red] will return from this query becuase this is the matched side of the unmatched query.

Hillary

 

[JonFer]

Hillary, did you see my post on July 7th? I think that answers your question.