Matching 2 tables

  • Thread starter Thread starter Raj
  • Start date Start date
R

Raj

I have 2 fields in my [MainData]![To_Location],
[From_Location] and I have another table that I would
like to veryify that the data in these fields are valid.
How would I write a query to do this? I also would like
the query to verify that these field are no less than 8
charaters and more than 14.
I used this expressions Len([MainDate]![To_Location]),Len
([MainDate]![From_Location]) and the cirteria = "Is Null
Or >12 Or <8" but I will not pull up the correct
information it is giving me all information.
Thank you for your help..
 
The simplest solution would be to create a table that contains the names of
all the valid places.

In the relationships window (Tools menu), include two copies of this table.
Access will alias the second with a name such as Location_1.

Create a relation between Location and To_Location.
Create another relation between Location_1 and From_Location.
In both cases, check the box for Referential Integrity.

Now it is impossible to enter a location that does not exist in the Location
table. On your form, you can use combo boxes for the From_Location and
To_Location.
 
Allen,
That will work great going forward. The data that I have
now was import from another database and just from a
quick visual audit there are quite a few that are not
valid and there are over 89000 row to verify that is why
I was hoping that a query would be useful.
-----Original Message-----
The simplest solution would be to create a table that contains the names of
all the valid places.

In the relationships window (Tools menu), include two copies of this table.
Access will alias the second with a name such as Location_1.

Create a relation between Location and To_Location.
Create another relation between Location_1 and From_Location.
In both cases, check the box for Referential Integrity.

Now it is impossible to enter a location that does not exist in the Location
table. On your form, you can use combo boxes for the From_Location and
To_Location.

--
Allen Browne - Microsoft MVP. Perth, Western Australia.

Reply to group, rather than allenbrowne at mvps dot org.

I have 2 fields in my [MainData]![To_Location],
[From_Location] and I have another table that I would
like to veryify that the data in these fields are valid.
How would I write a query to do this? I also would like
the query to verify that these field are no less than 8
charaters and more than 14.
I used this expressions Len([MainDate]! [To_Location]),Len
([MainDate]![From_Location]) and the cirteria = "Is Null
Or >12 Or <8" but I will not pull up the correct
information it is giving me all information.
Thank you for your help..
Click to expand...


.
Click to expand...
 
You can get Access to create the table of locations from your existing data.

1. Create a query into your existing table.

2. Drag the Location field into the output grid.

3. Open the properties box (view menu).

4. Set the Unique Values property to Yes.
At this point, the query shows every location once.

5. Change the query to a Make Table query (Make Table on Query menu).

6. Supply a name for your table.

7. Run the query.

The new table contains a record for every location in your table - once. You
can review this table, and use it to track down any invalid spellings etc in
your original table.

When you are happy with the result, create the relationships between the
tables.

--
Allen Browne - Microsoft MVP. Perth, Western Australia.

Reply to group, rather than allenbrowne at mvps dot org.

Raj said:
Allen,
That will work great going forward. The data that I have
now was import from another database and just from a
quick visual audit there are quite a few that are not
valid and there are over 89000 row to verify that is why
I was hoping that a query would be useful.
-----Original Message-----
The simplest solution would be to create a table that contains the names of
all the valid places.

In the relationships window (Tools menu), include two copies of this table.
Access will alias the second with a name such as Location_1.

Create a relation between Location and To_Location.
Create another relation between Location_1 and From_Location.
In both cases, check the box for Referential Integrity.

Now it is impossible to enter a location that does not exist in the Location
table. On your form, you can use combo boxes for the From_Location and
To_Location.

--
Allen Browne - Microsoft MVP. Perth, Western Australia.

Reply to group, rather than allenbrowne at mvps dot org.

I have 2 fields in my [MainData]![To_Location],
[From_Location] and I have another table that I would
like to veryify that the data in these fields are valid.
How would I write a query to do this? I also would like
the query to verify that these field are no less than 8
charaters and more than 14.
I used this expressions Len([MainDate]! [To_Location]),Len
([MainDate]![From_Location]) and the cirteria = "Is Null
Or >12 Or <8" but I will not pull up the correct
information it is giving me all information.
Thank you for your help..
Click to expand...


.
Click to expand...
Click to expand...
 
Back
Top