Access: Select Query

  • Thread starter Thread starter Pharoh
  • Start date Start date
P

Pharoh

I want to create a query that displays ONLY records where the two
joined fields are equal. The catch is that I only want the first 4
numbers in one field to be used otherwise it won't work.

Field 1 Field 2
===== =====

1234 123456
4321 432100

So basically....I only want Field 1 to be compared to the first 4
numbers in Field 2.

Thanks tons!
 
Dear Phar:

Sounds like:

SELECT *
FROM Table1
INNER JOIN Table2
ON Left(Table2.Field2, 4) = Table1.Field1

Tom Ellison
 
I'm trying to add a primary key (Unique Index) on a table via a SQL query,
but it's not working the way I expected. My SQL is:

CREATE UNIQUE INDEX Unid
ON mytable (Id);

I expected Access to add the new column "Id", but the query fails. After I
manually add the new column in the Design view the query runs, but the table
is not changed. I expected unique row numbers to be generated. Finally,
what is the function of index_name? I don't see that it adds anything, but
without it the query fails.

Thanks, Glenn
 
Try also

SELECT *
FROM Table1 INNER JOIN Table2
ON Table1.Field1 = Table2.Field2 \ 100
 
Hi,


Create index creates an index, not the field. You add a new field to an
existing table with an ALTER TABLE ... ADD COLUMN ... statement. To get
automatically a number generated for you, you need an AUTONUMBER "data type"
field, and once you have that field in the table, add an index to it, and an
UNIQUE constraint on it, if you really desire no dup. Instead of using ALTER
TABLE then the index then the constraint, if you want an "operation" that
does it for you, use the table designer with its graphical user interface.


Hoping it may help,
Vanderghast, Access MVP
 
Back
Top