dan

  • Thread starter Thread starter dan
  • Start date Start date
D

dan

Can you have more than one SQL statement in an Access
query?

I was trying to create a temporary table and populate it -
using a couple of insert into's - then select * on the
temp table, followed by a delete of the temp table.

e.g.

create table ...
insert into ...
select * ...
delete table ...
;

However, Access complains and says that the first line is
invalid, but if I only have the first line in, it accepts
it. That's no good to me. I kind of want the
functionality that a stored procedure would give. Any
suggestions??

Also, without using forms I want the following:
I have n tables:
People
Clubs
ClubMembers
ClubMembersAttendance

ClubMembersAttendance is going to be populated with dates
and times of members' attendance.
Now, when someone populates this table I have a combo box
for the name of the person and the name of the club.
These are directly related to the People and Clubs. I
have create a lookup on these fields so that you can
simply select valid values for the two fields. However,
what I really want - without using forms - is, when the
user has selected the name, then the clubs field will only
show clubs that that person is a member of. At the moment
it shows all clubs in the Clubs table.

Can this be done without using forms and event
procedures. It's a long story as to why I don't want a
form based solution but it's a good reason.

Hope you can help,

Dan.
 
Sorry, no. Not in JET SQL.

However, you can create a VBA Procedure to run as many SQL Strings as you
want.
 
Can you have more than one SQL statement in an Access
query?

-- No. You would need to use a SQL Server stored
procedure to implement this. You can implement this with
code of course, but it would not act like a SQL Server
#tblTemporaryTable.

Can this be done without using forms and event
procedures. It's a long story as to why I don't want a
form based solution but it's a good reason.

--No. For this to work properly, you would need the
ability to force a requery of the control (and then it
would have to be a single-item list not requiring the
entry to be in the list [because if it did, you'd have to
have all values chosen earlier in that list too, or else
they'd disappear from the screen for the previous
entries]). Oh the joys of datasheets.

Thinking aloud -- I suppose the lookup could be based upon
a function, with a field calling a function that resets
the values being fed to the control. But again, I'm not
convinced that (1) the control would effect a requery of
itself (i.e., ask the function for the value it should be
displaying), nor (2) the control reinitializes the list's
record count.

IF you can have a function as the lookup, and IF the
control consistently asks for the data to display in each
row, and IF you initialize the control with
enough "blanks," and IF it was just a single-field list --
it might work.

--------------------

Can I interest you in trying datasheet forms again? The
form *can* dynamically add the fields found in your query
if that is the issue. It essentially is the same thing as
you query, just with events!

David Atkins, MCP
 
Thanks. I thought that would be the case. The reason why
I am trying to avoid forms is that I am producing a
database for a charity and they want it to be as easy as
possible for them to add their own fields. With a simple
query and table datasheet implementation they don't have
to worry about Forms or complex (or even simple) VBA.
That way they don't need to bring in an IT pro every time
they need to add a field.

I think I'll go the forms route though...

Cheers,

Dan.
 
Back
Top