Update query help

Thread view:

Enable EMail Alerts

Start New Thread

Thread rating:

xg - 03 Aug 2006 17:43 GMT

I have a table called Course need to be updated. It is like this:

Sect Type
0352
0352
0352
1376
1376
1376
1376
1408
1408
1451
1452

I use another table called FixFall to update Course. It is like this:

Sect Type
0352 1
0352 3
0352 3
1376 1
1376 1
1376 3
1376 3
1408 1
1408 3
1451 3
1452 3

I want Type code in FixFall inserted in Type code in Course when the Sect
match and in that sequence. Both tables should have exact number of records
but actually Course has 2825 records and FixFall has 2840 records and I
don't know why.

I know this might be done in recordset to go through each record but I just
don't know how. Please help. Thanks

Reply to this Message

Amy Blankenship - 03 Aug 2006 18:46 GMT

It looks like you have a basic design problem, in that you have no way of
uniquely identifying a record, so that you know it is the same record.
Might I suggest that you either redesign your database or simply delete all
records in Course and replace them with the FixFall table?

HTH;

Amy

>I have a table called Course need to be updated. It is like this:
>
[quoted text clipped - 33 lines]
> I know this might be done in recordset to go through each record but I
> just don't know how. Please help. Thanks

Reply to this Message

xg - 03 Aug 2006 19:20 GMT

I know it's design problem However, Course is designed to import text file
downloaded from mainframe daily and cannot be altered.

I cannot use FixFall to replace Course completely as there are many other
fields in Course but not in FixFall.

I want to know if some kind of intermediate table or query be created to
solve this problem.

Thanks.

> It looks like you have a basic design problem, in that you have no way of
> uniquely identifying a record, so that you know it is the same record.
[quoted text clipped - 42 lines]
>> I know this might be done in recordset to go through each record but I
>> just don't know how. Please help. Thanks

Reply to this Message

Amy Blankenship - 03 Aug 2006 19:29 GMT

>I know it's design problem However, Course is designed to import text file
>downloaded from mainframe daily and cannot be altered.
>
> I cannot use FixFall to replace Course completely as there are many other
> fields in Course but not in FixFall.

Could one of those other fields be used to help you to identify what records
need to be updated?

> I want to know if some kind of intermediate table or query be created to
> solve this problem.

I doubt it. There's no way to create a join that would know what record
goes with what, especially since you don't have the same number of records.

Reply to this Message

Michel Walsh - 03 Aug 2006 23:17 GMT

Hi,

Append the data into a table with an autonumber, for each table you import.
Doing so, you don't modify the date you received, you just handle it into
another table, that is not forbidden by anyone.

The autonuber field, in each of the table, will then allow you to easily
match the records row by row, between your two "working" tables (each one, I
repeat myself, having an autonumber).

Hoping it may help,
Vanderghast, Access MVP

>I know it's design problem However, Course is designed to import text file
>downloaded from mainframe daily and cannot be altered.
[quoted text clipped - 53 lines]
>>> I know this might be done in recordset to go through each record but I
>>> just don't know how. Please help. Thanks

Reply to this Message