What do I need?

[bigbore50]

Ok,

I don't really know how to accomplish what i want.

I have a query that returns results based on an audit.

The query returns results of the last three audits in a form Like this
AUDIT REP Primary # Date COMPLETED
Chuck 82690 9/22/2005
Susan 82690 10/19/2006
Bill 82690 9/22/2005

Audit Rep = The name of the auditor that did the audit
Primary # = is the number of the place where the audit took place
Date completed = When the audit was done

Now what i want to do is,
I want some code that makes variable1 = Chuck (auditrep of the
first record of that query)
variable2 = Susan
(auditrep of the Second record of that query)
variable3 = Bill
(auditrep of the third record of that query)

I need this because i need to send a new audit rep that has not been
there one of the
last 3 times. I want to then run another query That schudles a UNIQUE
audit rep that has not been used in one of the last 3 audits

Hence i will not be able to send Chuck, Susan or Bill to the place with
Primary # 82690

 

[bigbore50]

I guess what i am trying to say is how do you use a query just like you
would a table

i have the query but i only want to assign certain values to variables.


Lets says i have a query that looks like

Primary # Person Name Address
12345 Bill 001
tall oak
12345 Sue 111 big
road
12345 Rachael 123 broad
lane


Ok lets say that is how my query looks... and i want to know the value
that is in column b2,b3,b4 and assign those values to variables

Variable1 = bill
variable2 = Sue
Variable3 = Rachael

You know like in EXCEL you can look up a certain cell. I have a query
that returns my values i just need to know how to get the information
out of the query piece by piece and use it as criteria.

I am trying to make a scheduling database that looks at a lot of
variables.
Such as where the auditor lives and where the audit is located. etc.

I need to be able to assign variables to certain aspects of my queries
so i can use them later.

Thanks for your help

Maybe i am thinking about this totally wrong

 

[kingston via AccessMonster.com]

In order to get a list of the last three reps who have been at a location,
you'd have a query like this:

SELECT TOP 3 [AUDIT REP] FROM Table WHERE [Primary #]=82690 ORDER BY [Date
COMPLETED] DESC;

If you have a master list of available reps, you'd use the above statement as
a subquery with the criteria NOT IN:

SELECT [AUDIT REP] FROM tblAUDITORS WHERE [AUDIT REP] NOT IN (SELECT TOP 3 ...
);

Insert the first query statement in the set of parentheses in the last query
statement. HTH

 

[Marshall Barton]

I don't really know how to accomplish what i want.

I have a query that returns results based on an audit.

The query returns results of the last three audits in a form Like this
AUDIT REP Primary # Date COMPLETED
Chuck 82690 9/22/2005
Susan 82690 10/19/2006
Bill 82690 9/22/2005

Audit Rep = The name of the auditor that did the audit
Primary # = is the number of the place where the audit took place
Date completed = When the audit was done

Now what i want to do is,
I want some code that makes variable1 = Chuck (auditrep of the
first record of that query)
variable2 = Susan
(auditrep of the Second record of that query)
variable3 = Bill
(auditrep of the third record of that query)

I need this because i need to send a new audit rep that has not been
there one of the
last 3 times. I want to then run another query That schudles a UNIQUE
audit rep that has not been used in one of the last 3 audits

Hence i will not be able to send Chuck, Susan or Bill to the place with
Primary # 82690

Technically, "variables" are things in a VBA procedure. I
do not understand what you mean by your use of the word in
this context.

You can use one query in another query the same way you you
would use a table, so that's not an issue.

I think your question boils down to how to get a list of the
AUDIT REP names that are not in the query for the last three
audits for the Primary#. If that's what you want, then try
this kind of query:

SELECT Q.[Primary#], T.[AUDIT REP]
FROM tblAuditors As T LEFT JOIN yourquery As Q
ON T.[AUDIT REP] = Q.[AUDIT REP]
WHERE Q.[AUDIT REP] Is Null

 

[bigbore50]

Ken,

Thanks a lot
That is exactally what i want to do
and i know queries would be a better way to go but in order to
accomplish what i want i cant seem to get them to work properly.
This database has a lot of different variables that i need it to take
into consideration and i need variables assigned so i can use them in
loops later.

Thanks again

 

[bigbore50]

Yeah i do think you are correct in saying that i should use a array.

That is what i will do.
But thanks for the info, i may have to use it one day