Re: Query on Queries

Tech Tip: Click here to run a free scan for Windows Errors and optimize PC performance

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@xxxxxxxxx> wrote in message
news:1148467385.445738.61060@xxxxxxxxxxxxxxxxxxxxxxxxxxxxxxx
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



.

Relevant Pages

  • Re: Query on Queries
    ... Think of the select query as a temporary table. ... FROM OSRM_Table AS OT INNER JOIN ISSM_Table AS IT ON (OT.[Prod Mfg SKU ... Just to give a background the "Prod Mfg SKU ... Cd" would be a subset of PIN. ...
    (microsoft.public.access.queries)
  • Query on Queries
    ... I generated a SQL query using Wizard and got the following ... I wanted to add another condition to my Query and also modify the ... Just to give a background the "Prod Mfg SKU ... Cd" would be a subset of PIN. ...
    (microsoft.public.access.queries)
  • Re: Query on Queries
    ... Presently the code I have within "Specialized Query" file is ... FROM OSRM_Table AS OT INNER JOIN ISSM_Table AS IT ON OT.[Prod Mfg SKU ... SQL code also to be in VBA. ...
    (microsoft.public.access.queries)
  • Re: Query on Queries
    ... As far as I am aware, DoCmd.TransferXXX only accepts saved query, or table ... it does not like an SQL statement. ... FROM OSRM_Table AS OT INNER JOIN ISSM_Table AS IT ON OT.[Prod Mfg SKU ... SQL code also to be in VBA. ...
    (microsoft.public.access.queries)