Compare & Update Query - Help

[HapiGuy314]

Hello, i was wondering if anyone can assist me with a lil problem.

Basically i have 2 tables...hypothetically Table1 and Table2.

Now, Table2 pretty much is a small sample of Table1 except that these records have an extra attribute/field.

So pretty much:

Table1 has fields A, B, C.
Table2 has fields A, B, C, and Z.

Now my question is, how do u update Table one so that u can compare the two tables so that when A, B, and C matches for both Tables 1 & 2, that Table1 will automatically update the appropriate attribute that was present in Table2.

I was thinking:
INSERT table1 (a,b,c,z)
SELECT table2.a, table2.b, table2.c, table2.z
FROM table2 LEFT OUTER JOIN table1
ON table2.a = table1.a and
table2.b = table1.b and
table2.c = table1.c and
table2.d = table1.d
WHERE -- This is where you add a filter
(sorry spacing didnt work in the post)

but my friend said its more of UPDATE comman rather than INSERT..and she might be right. Pls help if anyone can.

Sorry to make this so verbose but my 2nd question is more on the scripting side. Say im dealing with dates. And i have patients in my database that are seen in a medical facility. Some patients are treated 1 day, 2 days, 3 days etc. Some even last for several months.

Now the question is, what if a patient is treated at the medical facility for example from March 14 - June 10. How can i

1)Select these records that have over lapping months

2)List the starting date and ending dates of record if the treatment occured over 1 month in a new field ( June 30 - July 2 coutns as 2 months).

and

3) Create a new field so that it will list each specific months for the duration of the treatment.

Any advice or help would be much appreciated...even a link to a proposed solution is greatly appreciated.

[barlowr70]

you need to do some reading up on dB design specifically normalisation and redundancy. Why do you need 2 tables that hold almost exactly the same data ? In access always use an Autonumber field as your Primary Key and when you do have a related table the single Primary key ID will become your Foreign Key.

Q1
select PatientID where visit date between #03/01/2003# and #06/30/2003#, this wiil give you the details of all patient visits between Mar 01 03 and June 21 03 inclusive

for the duration of the treatment search Help for the DateDiff and Date Add Functions, you don't need a new field.

hope this helps ...