Setting relationship in Access 97

  • Thread starter Thread starter madman33
  • Start date Start date
M

madman33

Got a question I have two database which are used independent of one
another, actually two different software vendors. I wnat to do
conparisons between the two. The relationsship is a hard one to
define. one field of one is related to the first 7 characters of the
second field.. Can I set up a one to many relationship between the
two without having to change either database structure?

Charles Suhr
 
You won't be able to create a relationship between the two tables, but you
can join them together in queries.

Create a query with a calculated field of Left([MyField], 7) (replace
MyField with whatever the name of the field should be). You can now join
between the first field and the computed field.
 
If I use
D3:Left([h484].[d3],7)
is not null crtiteria

and set relationship of D3 on the h484 table to the corresponding ID
table on my other database. The exprssion does not change orginal
does it? If so is there a way to prevent this?

Charles
 
Ok it's early on Sunday morning and I'm not awake yet and trying to do
work stuff. Having problems getting the expression to go on to the
relationship table to allow the join. Ignore previous message about
changing values. It worked just fine that way, but changing data is
not allowed. Any suggestion on what I'm doing wrong to get the
expression to join?

Charles

If I use
D3:Left([h484].[d3],7)
is not null crtiteria

and set relationship of D3 on the h484 table to the corresponding ID
table on my other database. The exprssion does not change orginal
does it? If so is there a way to prevent this?

Charles

You won't be able to create a relationship between the two tables, but you
can join them together in queries.

Create a query with a calculated field of Left([MyField], 7) (replace
MyField with whatever the name of the field should be). You can now join
between the first field and the computed field.
Click to expand...
Click to expand...
 
Not sure what exactly you're doing.

Let's make sure we're talking the same thing. To me, a Relationship is
something you create between 2 tables in the Relationships window (or
through DAO using the CreateRelationship method). You cannot create a
relationship between a table and a query, nor can you create a relationship
between a field and part of another field.

If all you're trying to do is create a query that joins the two tables,
attempt to join them normally through the query builder, ignoring the fact
that you don't want to join the complete field from one table to the other
table. Once you've done that, select the SQL view (View | SQL View from the
menu bar) and where the SQL says something like:

ON [h484].[d3] = [table1].[id]

change it to

ON Left([h484].[d3], 7) = [table1].[id]

Another way is to create a query based on table [h484]. Drag all of the
fields to the grid. Add a calculated field by typing the following into an
empty Field cell:

NewID: Left([h484].[d3], 7)

and save the query (for the sake of argument, call the saved query qryh484.

Now, create another query that joins your first table to qryh484, instead of
table h484, and join the table to qryh484 using the NewID field in the
query.


--
Doug Steele, Microsoft Access MVP



Charles Suhr said:
Ok it's early on Sunday morning and I'm not awake yet and trying to do
work stuff. Having problems getting the expression to go on to the
relationship table to allow the join. Ignore previous message about
changing values. It worked just fine that way, but changing data is
not allowed. Any suggestion on what I'm doing wrong to get the
expression to join?

Charles

If I use
D3:Left([h484].[d3],7)
is not null crtiteria

and set relationship of D3 on the h484 table to the corresponding ID
table on my other database. The exprssion does not change orginal
does it? If so is there a way to prevent this?

Charles

You won't be able to create a relationship between the two tables, but you
can join them together in queries.

Create a query with a calculated field of Left([MyField], 7) (replace
MyField with whatever the name of the field should be). You can now join
between the first field and the computed field.
Click to expand...
Click to expand...
Click to expand...
 
That works... Only problem I have now is the date in the one database
is in the format of YYYYMMDDTIME
and the field is not a date type. want to do date comparisons with it
and the other database what is the easiest way to format data
and not change database structure

1. use a expression? example?
2 new field and convert to date field?

Thanks for the help so far....

Charles
 
Easiest approach would probably be to use the Format statement in an
expression to convert the date to a string so that you can compare it with
the other field.

--
Doug Steele, Microsoft Access MVP



Charles Suhr said:
That works... Only problem I have now is the date in the one database
is in the format of YYYYMMDDTIME
and the field is not a date type. want to do date comparisons with it
and the other database what is the easiest way to format data
and not change database structure

1. use a expression? example?
2 new field and convert to date field?

Thanks for the help so far....

Charles



Not sure what exactly you're doing.

Let's make sure we're talking the same thing. To me, a Relationship is
something you create between 2 tables in the Relationships window (or
through DAO using the CreateRelationship method). You cannot create a
relationship between a table and a query, nor can you create a relationship
between a field and part of another field.

If all you're trying to do is create a query that joins the two tables,
attempt to join them normally through the query builder, ignoring the fact
that you don't want to join the complete field from one table to the other
table. Once you've done that, select the SQL view (View | SQL View from the
menu bar) and where the SQL says something like:

ON [h484].[d3] = [table1].[id]

change it to

ON Left([h484].[d3], 7) = [table1].[id]

Another way is to create a query based on table [h484]. Drag all of the
fields to the grid. Add a calculated field by typing the following into an
empty Field cell:

NewID: Left([h484].[d3], 7)

and save the query (for the sake of argument, call the saved query qryh484.

Now, create another query that joins your first table to qryh484, instead of
table h484, and join the table to qryh484 using the NewID field in the
query.
Click to expand...
Click to expand...
 
Ok I new to this...

Lost me on that last one.
whats in the field
table 1 field 1 200305010700 5/1/2003 0700
table 2 field 2 5/1/2003 5/1/2003

was hoping to just use a criteria in the querey to do comparison if I
can format the 2 cells similar, Only retriction is that I can not
change data.

Still using Access 97
BTW the query with a query idea worked great for setting relationship
 
Okay, what's what?

Is [Table1].[Field1] a Date field that contains Date and Time, and
[Table2].[Field2] a Text field that only contains Date?

If all you want is to check for equality, you can use WHERE
Format$([Table1].[Field1], "m/d/yyyy") = [Table2].[Field2], although there's
always a chance that you might have leading zeroes in the string, in which
case that may not work.

I suppose the safest is use the CDate function to convert your text date
into a "real" date. You could then do something like WHERE
DateValue([Table1].[Field1]) = CDate([Table2].[Field2]).

As far as I can see, the second way is what you'd have to do if you wanted
inequalities.
 
Thanks for all the help... ended up using a cdate and picking portion
of date field and then during comparison, and setting critera there
and flagging the errors.....

Thanks again

Charles
 
Back
Top