Problem in aligning data by synchronizing (linking) time of two tables

[drkundu]

I have two table in Microsoft Access They are named as "master" and "O3". I want to align "ozone" data in the "O3" table by synchronizing the "Corrected_Time" column between "master" and "O3" tables. I confirm that all the times in the "Corrected_Time" column of "O3" table are also present in the column of "Corrected_Time" of master table. The format of time is long (hh:mm:ss). Note there different number of rows in the two tables.

I have designed a query in the following way.
1. Connected "Corrected_Time" column of the two tables.
2.
Field: Ozone
Table: master
Update To: [O3]![ozone]



When run the query, only 5279 data points are arranged. But there are 8623 data points in the "ozone" column of "O3" table. Please help me to find my error. Thank you so much.

Best regards,

Kundu

[June7]

Is the time value unique? There are 8623 unique time values?

How many records in master? The time values are unique?

Provide sample data. If you want to provide db, follow instructions at bottom of my post.

[drkundu]

Please see answer to your question below:

Question:

Is the time value unique? There are 8623 unique time values?

Answer: All the time values are unique?

Question: How many records in master? The time values are unique?

Answer: The number of records are 83711 in the master and all are unique>

Question: Provide sample data. If you want to provide db, follow instructions at bottom of my post.

Answer: I have zipped the file. The size is 2.04 MB. Hence I could not send you the file. Is there anyway that I can send the file to you.

Thank you so much.

[June7]

Did you make copy, remove some data, remove unneeded objects, run Compact and Repair?

Alternative is to upload to fileshare site such as Box.com and post link to the file. However, sometimes my slow home internet prevents me downloading large files.

[drkundu]

Did you make copy, remove some data, remove unneeded objects, run Compact and Repair?

Alternative is to upload to fileshare site such as Box.com and post link to the file. However, sometimes my slow home internet prevents me downloading large files.

Here is my answer:

Please see attached the Access file. I have deleted about 4000 rows from master so that I can upload my file and send to you. Thanks.

[June7]

I do see duplicate times in Master. Records ID 47003 and 47004 have the same time displayed. However, when I try to group on this field and count the records for each time value, there are no duplicates. Very odd.

Not every time in O3 has a match in Master. Therefore an INNER JOIN query returns only 5022 records. A LEFT JOIN shows all 8623 O3 records. Examine results of this query:

SELECT O3.Corrected_Time, Master.Corrected_Time
FROM O3 LEFT JOIN Master ON O3.Corrected_Time = Master.Corrected_Time;

[drkundu]

I do see duplicate times in Master. Records ID 47003 and 47004 have the same time displayed. However, when I try to group on this field and count the records for each time value, there are no duplicates. Very odd.

Not every time in O3 has a match in Master. Therefore an INNER JOIN query returns only 5022 records. A LEFT JOIN shows all 8623 O3 records. Examine results of this query:

SELECT O3.Corrected_Time, Master.Corrected_Time
FROM O3 LEFT JOIN Master ON O3.Corrected_Time = Master.Corrected_Time;

How can I make this query.

1. Click on CREATE
2. Click on Query Design
3. Link O3.Corrected_Time, Master.Corrected_Time

Then what. I am confused. I am new in using Microsoft Access. Thank a lot.

[June7]

Your query has a link established between the two tables. Did you do that by click and drag between tables? Double Click on the line to open dialog to change the JOIN TYPE.

This is basic Access functionality. Have you completed any introductory tutorials?

The db has only one day of data. Will there be multiple days in the actual db?

[drkundu]

Thank you. The problem is solved. I am using the Access only for a week. I have few more questions

1. How to look up duplicate in my time column and then delete the duplicate rows
2. How to look up unique time between two columns of time located in the two tables.

Regards,

Kundu

[June7]

1: Bing: Access delete duplicate records same table

http://answers.microsoft.com/en-us/o...b12f16c?auth=1
http://www.bluemoosetech.com/microso...l.php?jid=25&a..

2. maybe like: SELECT * FROM table WHERE #some date/time# BETWEEN field1 AND field2;

[drkundu]

1: Bing: Access delete duplicate records same table

http://answers.microsoft.com/en-us/o...b12f16c?auth=1
http://www.bluemoosetech.com/microso...l.php?jid=25&a..

2. maybe like: SELECT * FROM table WHERE #some date/time# BETWEEN field1 AND field2;

Thanks. It is very helpful.

When I am copying aligned data from Access to Excel, I see green triangle at the corner of excel cell. As a result, I cannot apply any calculation on the data. Please let me know how to solve this problem.

[June7]

And what is message that pops up when you click the green triangle?

[drkundu]

And what is message that pops up when you click the green triangle?

I have removed the green signal. But when I apply calculation on the excel sheet. I see the expected results. I am sending some parts of data. Please look at the Column S (NO_Corrected). I have applied average function. But, it shows unexpected result.

[drkundu]

And what is message that pops up when you click the green triangle?

It is the formatting mismatch between Access and Excel. I reformatted the cells in the excel in various ways. Still I see unexpected results.

[June7]

This is very odd. When I double click into each of the 3 cells referenced in the formula, the error goes away and the calc is correct.

Changing the cell formatting had no impact.

Never encountered this in a spreadsheet before. Where does this data come from?