relating two tables with common field but not seeing data in forms.

[Pat McCann]

Firstly - I'm no expert.

What I'm trying to achieve - This is a simple club membership
requirement. I have some data held in an external system which I can
export via csv format. This is club member data and each record has a
unique membership number field and some associated name and address
fields etc.

I want to create an access database and add some additional club
member datafields to each record. My approach is to create two tables.
One table holds the imported data from the external system and the
second table will hold the additional data fields. I'm also creating
an identical unique membership reference number field in the second
table and then creating a one to one relationship between them.

So at this stage I have 800 records in the imported table with for
example, to keep it simple, one reference number field and name field.

I have two fields in the second table - one reference number field and
one telephone number field. At this stage there is data in the table
with 800 records imported and no data in the newly created second
table, albeit there is an identical reference number field with a
relationship to the first table.

If I create a form with all fields on it to enable me to input the
telephone number in each record, when the first record shows in the
form I expected to see the reference number field and name field
filled in so that I could just add the telephone number. This is not
the case.
What I do get is all fields blank so I don't know what field I'm
entering telephone data for.

Whilst I could get round this by copying the import table data to the
new table so that I have 800 reference numbers in both table, my real
concern is maintaining the data the future.
To maintain this database, I will need to import from the external
system on, say, a monthly basis. I'm assuming that to do this, I would
temporarily delete the relationship between the two tables, delete the
import table. create a new import table from the new csv file and
recreate the relationship. Hopefully all records with the same
references number would still be related, but new records with new
member references since last import would show blank again wouldn't
they?, and I have the same problem with the update form.

Is what I'm trying to do feasible? If so, any ideas why the form
shows only blank fields when I have data in one of the tables?

I'm using access '97

Thanks in anticipation

Pat McCann, Wakefield, England

 

[Andrew Smith]

Pat,

Open up your form in design view, open the property sheet and find the
"Record Source" field. Click on the ellipsis (...) next to this field and
this should bring up the query design grid with your two tables shown. The
tables should show the 1 to 1 relationship.

Double click on the relationship line to bring up the edit relationship
dialog. This gives you three options for the type of join - these will give
you an inner join, left outer join or right outer join. You will see that
option one is selected. Change it to option two (proably, if this is wrong
try option three) to change from an inner join to an outer join. Then close
the query grid to update the Record Source property. Hopefully the form
should now work as you expect.

The default inner join (option 1) only shows records where there are
matching entries in both tables. Since your second table is empty you will
get no records. The other options give you all the records from one table
and will display nulls for the second table records if there is no matching
record. Clearly this is what you want.

For the update of your database you should be able to set up the csv file as
a linked table, and then run an append query to add any new records to your
database table when you get a new csv file. You may also need to run an
update query to amend any of the existing records that have changed, and a
delete query to delete any records that no longer exist in your new file.

 

[Chris2]

See Below:

Firstly - I'm no expert.

What I'm trying to achieve - This is a simple club membership
requirement. I have some data held in an external system which I can
export via csv format. This is club member data and each record has a
unique membership number field and some associated name and address
fields etc.

I want to create an access database and add some additional club
member datafields to each record. My approach is to create two tables.
One table holds the imported data from the external system and the
second table will hold the additional data fields. I'm also creating
an identical unique membership reference number field in the second
table and then creating a one to one relationship between them.

So at this stage I have 800 records in the imported table with for
example, to keep it simple, one reference number field and name field.

Reference Number field? Is this the Primary Key of the import Table?
I'll assume it is.

I have two fields in the second table - one reference number field and
one telephone number field. At this stage there is data in the table
with 800 records imported and no data in the newly created second
table, albeit there is an identical reference number field with a
relationship to the first table.

It looks like first Table and second Table both have Reference Numbers,
too. Are they also Primary Keys? I'll assume they are.

If I create a form with all fields on it to enable me to input the
telephone number in each record, when the first record shows in the

See my next comment.

form I expected to see the reference number field and name field
filled in so that I could just add the telephone number. This is not
the case.
What I do get is all fields blank so I don't know what field I'm
entering telephone data for.

Since records in the import Table and the second Table both have the same
Primary Key, why not just run an UPDATE query to get all the phone numbers
into the second Table in a snap, instead of using a Form to do each phone
number one at a time by hand?

UPDATE second AS S1
INNER JOIN
import AS I1
SET S1.PhoneNumber = I1.PhoneNumber
WHERE S1.ReferenceNumber = I1.ReferenceNumber


Note: Please use real table names when dicussing each table . . . I am not
at all sure I followed first Table, second Table, and import Table
references.

 

[Pat McCann]

Thanks to both Andrew and Chris for responding. I'm proberly using an
older version of access than you Andrew as I couldn't get to the
'join' parameters that way, but I could edit them from the
relationships view. I changed the setting to 2 and that appears to
have resolved it.
My version of access (97) does not seem to have the ability to 'link'
csv, only import. nor are their any pre-made 'update' and delet
queries pre-defined. However, I can now move on thanks. (I'll probably
get the club to buy the latest version of access, hoefully some of the
'ready to use' stuff and the wizards will be better than access 97.

many thanks to you both for the help - but I may be back!!

Chris - just for completeness, I had just two tables. One that
contained import data (lets call it 'import' with two fields, one
being the unique ref number , the other being a name.

The second table is one I created, let call it 'new data', containing
two fields, one being a replica of the unique ref number in the first
table, the other being a telephone number field.
Obviously the real system will have many more fields in each table but
I dumbed it down to make it easier to solve my problem.



n Sat, 3 Apr 2004 20:29:18 +0100, "Andrew Smith"