Query on Queries

[excel_hari]

Hi,

This is my introduction to Access and even databases (I have some
basic grounding in SQL through Oracle/SAS).

I generated a SQL query using Wizard and got the following

SELECT ISSM_Table.PIN, ISSM_Table.[ISSD Special], OSRM_Table.[Prod Mfg
SKU Cd]
FROM ISSM_Table INNER JOIN OSRM_Table ON ISSM_Table.PIN =
OSRM_Table.[Prod Mfg SKU Cd];

I wanted to add another condition to my Query and also modify the
columns being selected and store the results in to a new table, for
which I wrote the following query.

SELECT OT.* INTO SpecialSKU
FROM OSRM_Table AS OT INNER JOIN ISSM_Table AS IT ON (OT.[Prod Mfg SKU
Cd] = IT.PIN) and on (IT.[ISSD Special] = "X");

When I try to execte it, I get the error message

Invalid Use of '.', '!', or '()'. in query expression".

Please point out the mistake in the above.

Also, I want to add as to how I generated the 2 tables in my database.
I had 2 excel files which I imported in to Access using File--Get
External Data Import. While choosing the variou options for import :-

a) I chose PIN to be indexed in ISSM_Table while "Prod Mfg SKU Cd" to
be indexed in OSRM_Table. Just to give a background the "Prod Mfg SKU
Cd" would be a subset of PIN.

b) I selected that no primary key should be created for both the
tables. Thats because PIN and Prod Mfg SKU Cd would have duplicate
values.

Once the above was done I went to relationships (I want to define a
foreign ke relationship)and by keeping ISSM table on left hand side, I
dragged PIN to "Prod Mfg SKU Cd" in OSRM table. I got a window called
edit relationships in which I tried to check on Referential Integrity
but get a message saying that "No index found for the referenced field
of the primary table" Why is this happening? Also, If I drag PIN to
"Prod Mfg SKU Cd", then does PIN become the foreign key to "Prod Mfg
SKU Cd" or is it vice-versa?

Regards,
HP
India

 

[John Spencer]

Try the following

SELECT OT.* INTO SpecialSKU
FROM OSRM_Table AS OT INNER JOIN ISSM_Table AS IT
ON (OT.[Prod Mfg SKU Cd] = IT.PIN)
WHERE (IT.[ISSD Special] = "X");

That said, in Access it is usually better to just use the query and not
populate a separate table. Think of the select query as a temporary table.

Part two of your post:
If you don't have a primary key (you should always have one) then you cannot
set up relationships and enforce data integrity. Access will look for one
of the fields in the relationship to have an index that only allows unique
values and uses that to determine which table is the one table and which
table is the many table. So, no unique index equals no data integrity.

 

[Michel Walsh]

Hi,


the syntax is not "and on", just "and":

SELECT OT.* INTO SpecialSKU
FROM OSRM_Table AS OT INNER JOIN ISSM_Table AS IT ON (OT.[Prod Mfg SKU
Cd] = IT.PIN) and (IT.[ISSD Special] = "X");



Hoping it may help,
Vanderghast, Access MVP

 

[excel_hari]

John and Michel,

Thanx for your help. Iam able to run my query correctly.

I have now 2 queries:-

SELECT OT.* INTO SpecialSKU
FROM OSRM_Table AS OT INNER JOIN ISSM_Table AS IT
ON (OT.[Prod Mfg SKU Cd] = IT.PIN)
WHERE (IT.[ISSD Special] = "X");

and

SELECT OT.* INTO NotSpecialSKU
FROM OSRM_Table AS OT INNER JOIN ISSM_Table AS IT
ON (OT.[Prod Mfg SKU Cd] = IT.PIN)
WHERE (IT.[ISSD Special] is null);

What I want to do is to stack the 2 tables SpecialSKU and NotSpecialSKU
(in a new table called NewOT) one below the other and while doing this
I want to create a new column called Status which has the value
"Special" for all rows coming from Table SpecialSKU and has value "Not
Special" for all row coming from NotSpecialSKU table. How can that be
done (Essentially I have to create a new column).

Also, I wuld like to know as to whether there is an easier way for
accomplishing what am doing above

(Im basically using Access to perform vlookup kind of operation as the
number of rows would very soon cross the excel limit and more
importantly it takes a long time in Excel)

Also, is it not possible to have 2 queries within a single file ( i
tried to paste the above 2 in a single file and run but got an error
message).

regards,
HP
India

 

[Michel Walsh]

Hi,


You can merge vertically two set of data with a UNION ALL:
====================

SELECT "Special" As Category, ot.*
FROM OSMR_Table AS ot INNER JOIN
....
WHERE it.[issd special]="X"

UNION ALL

SELECT "Not Special", ot.*
FROM OSMR_Table As ot INNER JOIN
....
WHERE it.[issd.special] IS NULL
====================


You can use UNION, without ALL, to remove any duplicated row, but that
requires extra processing. As example, if you have two identical rows from
the first SELECT, a UNION, without ALL, would keep just one of these rows.

Clearly, to succeed, the number of columns in each SELECT must be the same.



Hoping it may help,
Vanderghast, Access MVP

 

[excel_hari]

Michel,

Your code has been extremely helpful. Thanks a lot. I would get back in
case I have more questions.

regards,
HP
India

 

[excel_hari]

Michel,

Could you pls tell me as to how I can use the above SQL within VBA.

Regards,
HP
India

 

[Michel Walsh]

Hi,


You make it in a query, a saved query, and then, use that saved query as if
it was a table, where you VBA application expects to see a table (except for
few exceptions).


Vanderghast, Access MVP

 

[excel_hari]

Michel,

Thanks for your response. I have saved the above code within a query
called Query1.

Iam not able to understand the meaning of "use that saved query as if
it was a table"

Basically, how do I call this query from VBA (Im looking for smething
like Call Query1)

Please guide me.

Regards,
HP
India

 

[Michel Walsh]

Hi,

What will you do if the data was in a table instead? Do exactly the same
thing, but using the query Q1 instead of the table T1.


As example, if you want "bind" the form on Q1, specify Q1 as RecordSource.
If you want see Q1 in a multi-column list, have Q1 as RowSource. If you open
a recordset on T1, just open a recordset on Q1, etc.


So, if you can do what you want to do when the data is in a table, the fact
that it is in a query is not different. You don't "Call Table1", so it won't
be "Call Query1". There is just so many ways about how to use a "table" that
I really cannot enumerate them all.



Vanderghast, Access MVP

 

[excel_hari]

Michel,

Thanks for your help. I tried what you said and it finally "dawned" on
me about using name of query rather than a table for doing what I
wanted to do.

Regards,
HP
India

 

[excel_hari]

Michel,

Please note, am able to achieve what I set to do (which is basically
Lookup).

With help of this group I have been able to assemble the following
code:-

Sub LookupData()

CurrentDb.Execute "Delete * from OSRM_Table "
CurrentDb.Execute "Delete * from ISSM_Table "

'Testit function pinched from Access MVPS site

DoCmd.TransferSpreadsheet acImport, acSpreadsheetTypeExcel8, _
"OSRM_Table", TestIt("OSRM"), True, "Report 1!"

DoCmd.TransferSpreadsheet acImport, acSpreadsheetTypeExcel8, _
"ISSM_Table", TestIt("ISSM"), True, "ItemMaster!"

'BrowseFolder function filched from Chip Pearson, Excel MVP site

DoCmd.TransferSpreadsheet acExport, , "Specialized Query",
BrowseFolder("Select a BASE Folder") _
& "\OSRM_Table_SpecialData", True

DoCmd.Quit

End Sub

But, I want to make this code a little "tighter". In the above code, I
have to create a Query file called Specialized Query based on which the
whole output gets created. I would like to mention the query condition
within VBA itself. That way, if some new user wants to run the lookup,
s/he can just create a new module in a new DB and paste the code and
press run (Presently they woud also have to separately paste the query
within a query file a small additional step.)

Regards,
HP
India

 

[Michel Walsh]

Hi,


With Jet, the SQL statements are made to be just one executable statement.
To glue together different statements, like two DELETE, you use VBA, like
you did, or a macro, and run the subroutine, but itself, or behind a macro
that will just call the subroutine, if end user has to use that function.
You can also built a starting form that present the operations to be
performed with a button click, as example. I am afraid we cannot built
multiple statements with one single Jet query/view/procedure, unless that
procedure call, itself, the VBA function, which is more a hack and problem
to maintain, than something I would suggest to do.


Hoping it may help,
Vanderghast, Access MVP

 

[excel_hari]

Michel,

Thanks for your response.

Presently the code I have within "Specialized Query" file is

SELECT "Special" As Category, OT.*
FROM OSRM_Table AS OT INNER JOIN ISSM_Table AS IT ON OT.[Prod Mfg SKU
Cd]=IT.PIN
WHERE (IT.[ISSD Special]="X")
UNION ALL SELECT "Not Special", OT.*
FROM OSRM_Table AS OT left JOIN ISSM_Table AS IT ON OT.[Prod Mfg SKU
Cd]=IT.PIN WHERE (((IT.[ISSD Special]) Is Null));


Now, I am using this query in

DoCmd.TransferSpreadsheet acExport, , "Specialized Query",
BrowseFolder("Select a BASE Folder") _
& "\OSRM_Table_SpecialData", True


Since, I want to keep all my "code" within VBA, thats why I wanted my
SQL code also to be in VBA.

If this would be problematic to maintain in future then, I wuld not
consider this solution.

regards,
HP
India

 

[Michel Walsh]

Hi,


As far as I am aware, DoCmd.TransferXXX only accepts saved query, or table
name, it does not like an SQL statement.


Vanderghast, Access MVP