feeding a value list, not a field, to sql st

[delikedi]

My problem in simplified form is as follows:

Table 1: Objects (2 records)
Cube
Sphere

Table2: Object Properties (2 records)
Volume
SurfaceArea

SELECT Table1.Objects, Table2.ObjectProperties FROM Table1, Table2

I get a cross join,
Table3: Objects and Properties
Cube | Volume
Cube | Surface Area
Sphere | Volume
Sphere | Surface Area

Which is the result I want. I use it as an append query to populate a temp table.

My problem is, I actually don't have the source tables. These values differ from case to case. I solve the problem by creating temporary tables, which is extra work.

I would like to know if there's a way to provide a value list as a record source for SQL interpreter to process. I can do it with a single value, i.e this works:

SELECT Table1.Objects, "Volume" AS Exp1 FROM Table1

but this doesn't work:

SELECT Table1.Objects, "Volume", "Surface" AS Exp1 FROM Table1

it is treated as if another field was introduced. Access converts the statement into

SELECT Table1.Objects, "Volume" AS Exp1, "Surface" AS Exp2 FROM Table1

I'd like a way to pass these two values as if they were the record values, as opposed to field names, from an actual table.
Thx in advance.

 

[jdraw]

In plain, simple terms what exactly are you trying to do?

This structure would appear to handle a sphere, cube, tetrahedron... (any 3d shape)
ObjectId
ObjectShape
ObjectVolume
ObjectSurfaceArea

 

[delikedi]

I am actually more interested in learning if there's a way to do something rather than actually doing it. I'm trying to learn if there's a way to feed some values to a SQL string without having to retrieve that set of values from a table or query. We kind of do it in append queries where we supply values with VALUES clause. I am after something similar.

Consider that Jack and Jill both received a cube and a sphere. If one had to report the properties of objects distributed to these people, one could form three tables, link them with PK's and FK's and generate a query whose fields are Owner, Object and ObjectProperty. This query would contain 8 records: 2 people * 2 objects to both * 2 properties for each object.
One can generate the same result set by performing a cross join on three temp tables that contain these values. Which is how I do it at the moment.

I was curious if there was a way to do this without even using the tables.

 

[jdraw]

Not sure where you're going with this, but data base is about tables and relationships.
Then there is user interface/ presentation.

For your example (tables)

Jack and Jill are people, so you have a People table with fields
PersonId (PK)
PersonName

Data (sample)
1,Jack
2, Jill
and an Object_3D Table

ObjectId (PK)
ObjectName
ObjectShape
ObjectSurfaceArea

Data: (sample)
1,Cube_1,Cube,96
2, Sphere_100,Sphere, 110
3, Cube_5, Cube, 137
4,Sphere_Md23,Sphere, 58

Now you have many People and many Objects_3D, so to resolve the M:M issue, you
- create a Junction Table. let's call it PersonReceivedObject. And it has fields
PersObjId PK
PersonId FK to People table
ObjectId FK to Objects_3D table

and you would make a Unique compound index on PersonId and ObjectId in order to prevent duplicates.


Data for PersonReceivedObject (Sample data)
1,1,1 Jack got Cube_1
2,1,4 Jack got Sphere_Md23
3,2,3 Jill got Cube_5
4,2,2 Jill got Sphere_100

You do not need temp tables.

If you want info on value list etc, you could look at
http://allenbrowne.com/ser-27.html

Perhaps you could tell us more about your value list request using these sample data and tables.

 

[delikedi]

I am as grateful for your message as much as I am frustrated at my disability to explain my problem.

I hope these real life problems of mine will shed some insight:
My database functions well for its intended purposes. However there are times that some data, which is not worth recording, needs to be processed. One example is a "recipient list" that a user builds for her intended memo. The list gets built by concatenating the contact ID's of the recipients. Currently, when she presses "send", a loop in VBA code begins, and a record is inserted into the notes table for every recipient of the message. So the problem is not unsolved, I just seek a better way. If I had a way to pass the recipientID list to the SQL interpereter, only one SQL statement would execute, and the messages would be mass-produced.

another example involves Autocad. I offer users the functionality to input their drawing file, and my code examines the layers and object types of their drawing. Layers and object types are recorded into temp tables and a cross-join is executed, producing a combination of records, along with a boolean field. The result is recorded into a third temp table, and shown to the user. The user clicks the boolean fields of the rows he likes, thus showing that he "wants the length of all lines on layer X" to be measured. The code executes accordingly.

In essence, I am seeking a way to feed some values to the SQL interpreter, as a substitute of an actual table of actual records.

 

[spikepl]

I do not understand what you want either. Your focus is still on the mechanics of doing whatever it is that you are doing, but from the examples it is difficult to deduce (for me, at least) what it is that you want.

Make one very simple example, shaved down to the bare minimum required to illustrate

1. the situation requiring the code
2. the code/SQL that you can currently use to handle your requirement
3. what it is that you are unhappy about in that solution
4. and a description of what it is that you'd like instead.

 

[delikedi]

Very well. Say I have a table, tblNames, with one field:
fldName
Jack
Jill

I want to insert the names John and Jane to this table. I do this in two ways.
1. I make the user type these names into a temporary table, namely tempNames. then I execute

INSERT INTO tblNames(fldName) SELECT * FROM tempNames

2. I store the names into an array named astrNames(0 to 1)
Then I execute

For counter=0 to 1
INSERT INTO tblNames(fldName) VALUES (""" & astrNames(counter) & """)"
Next counter

I find the first method clean and efficient, however it requires a temporary table. I already feel I have too many tables, temporary or otherwise. The second method requires more code and executes one by one, not en-masse.

I would like to do this operation using this statement:

INSERT INTO tblNames(fldName) MAGICALKEYWORD(" & magicalArrayOrRecordsetName & ")"

After finding nothing on the matter, I hoped someone here would know a trick method. Now I'm all but certain that I want too much

 

[jdraw]

I'm as confused now as I was back at my first post.

Forget the temp tables and array, give us a simple, clear explanation/description of the underlying issue, then with data to show us an example of what you want to do.

We are not understanding the issue you are having and your examples thus far are not helping.

Make it very simple. Until we understand WHAT you want, we can not help you with HOW to do it.....
Stick to one example/analogy until we all get on to the same topic.

My examples in post 4 were based on your posts. How did we get from there to recipient lists?