Real dificult SP ... is it even possible ?

From: Zwi2000 (zwi2000_at_hotmail.com)
Date: 07/20/04 

Date: Mon, 19 Jul 2004 23:53:40 -0400

Hi,

I have been thinking and thinking how to do this query, but just cant think
of anything. Ill try to explain in steps.

I have two pages:

1) Page one is a search page that will pass on parameters to page two. I
will focus on parameters for "UserId", it passes either one integer (2), or
many depending on the user selection (2,5,18,2).
2) Page two should display the results and here is where the query is
needed.

This is the design of the tables.

1) Table one has "cases", each case has a unique ID. It could be as simple
as:

CaseID - Integer (Key)
CaseNumber - varchar

2) Table two links users to cases (Users is another table, at this point
doesnt matter)

LinkId - Integer (Key)
UserId - Integer (This links Users here)
CaseId - Integer (This is the key from table 1)

I need to display on page two all cases in which the selected users are
passed on from page # 1 (Could be one or more).

If I link both tables I will get only one user for each case, and the case
may be repeated. How do I display all users linked to one case in just one
results (one line). so that when checked against the parameters I get the
needed results.

Something like:

CaseId, UserId1 (Linked), UserId2 (Linked), UserId 4 (Linked), etc ....

I tried:

Select * from cases
Inner join users on cases.userid = users.userid

Problem: I get results like

CaseId = 1221
UserId = 3

CaseId = 1221
Userid =5

And so forth .. one line each, what I need is:

CaseId = 1221
UserId = 3,5 ...

That way I can run the desired query.

Any idea how to accomplish this ?

Thanks !

Z

Relevant Pages

  • Re: control cant be edited it is bound to an unknown field
    ... UserId in the form's RecordSource. ... >from the query UserID is one of them. ... >turning Display Column Heads to Yes. ... >must be missing something. ...
    (microsoft.public.access.forms)
  • RE: Exporting to Excel
    ... GetUserId ... Static Function GetUserIdAs String ... 'Open the specific query with the data to be exported ... You can use this to drive a loop that will export a worksheet per userid. ...
    (microsoft.public.access.externaldata)
  • RE: Exporting to Excel
    ... I did make the Criteria for the patientid field as GetUserId() not GetUserId. ... Static Function GetUserIdAs String ... 'Open the specific query with the data to be exported ... You can use this to drive a loop that will export a worksheet per userid. ...
    (microsoft.public.access.externaldata)
  • RE: Conditional display of records ...
    ... So in my query, I will map 'A' and 'B' for flag='Y' and userid (with which ... users logon) with the userid stored in table 'B'. ... The trick is to create a function that calls the username function and pass ... Function CheckFlag(strFlag As String) As Boolean ...
    (microsoft.public.access.formscoding)
  • RE: Conditional display of records ...
    ... So in my query, I will map 'A' and 'B' for flag='Y' and userid (with which ... users logon) with the userid stored in table 'B'. ... The trick is to create a function that calls the username function and pass ... Function CheckFlag(strFlag As String) As Boolean ...
    (microsoft.public.access.formscoding)