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

[drkundu]

Data are coming from an analytical instrument. I can apply the excel formula on the excel sheet that are directly coming from the analytical instrument. Please see column M (named as Test) of the excel sheet named "Forum 4".

I have shown a real sample of my excel formula in the excel sheet named "Forum 3". Please see the column S (NO_Corrected). Similar formula will be applied on the remaining rows (about 80,000, I have curtailed from the excel sheet). Hence, double click will not work.



As I can apply formula on the excel sheet that are coming from analytical instrument (discussed above), it sounds like an issue with Access. Thanks for continuous suggestion. Files are sent as attachment.

[June7]

Wasn't suggesting you double click every cell to fix. It was just an observation about the oddity.

Forum 3 workbook is data exported from Access? I've never encountered this issue. I just tested exporting data from your O3 table and calcs in worksheet run just fine.

[drkundu]

When I export data from O3 table to excel, the calculation also works for me.

But when I export data from master (after aligning according to Time) to excel, the calculation on the columns made within the Access does not work.

Please see this latest attachment for example.

When I export 8Aug_NOx table from Access to excel, the calculation works for me. Note that this table I imported from excel to Access and I have not added any new column after importing data to Access.

But, when I export master table from Access to excel, the calculation does not work on the columns (NO, NO2_Thermo, NOx, PMT and Preconcentration) that I created in Access. In the newly created columns, data are then aligned by designing query (see attached). Please let me write how I created those columns.

1. I clicked on "Click to Add" and then "Short Text" and then wrote name of columns (e.g., NO, NO2_Thermo, NOx, PMT and Preconcentration). Is it a problem?

I also made the columns in following way: I clicked on "Click to Add" and then "Number" and then wrote name of columns. In this case, all aligned data are shown as "1". I tried to reformat the newly created column by increasing digits after decimal point, then all data are shown as "1.00".

[June7]

The fields in Master table are text type. Change them to number (double).

[drkundu]

Ok. For doing that I clicked on the "Fields", then changed "Data Type" from "Short Text" to "Number" Below the "Data Type". there is "Format". There I do not see any thing like "Double" Please let me know how I can change to number (double)

When I change Short Text to Number, all data are changed to 1. Thank you.

[June7]

Set the Field Size property to Double.

[drkundu]

When I change "Short Text" to "Number", Field Size property becomes un-highlighted, which does not let me change. Please let me know what to do. Thanks.

[June7]

I don't know what else. I did exactly what I describe with the db you posted and it works.

[drkundu]

Thank you. It is working with me now.

[drkundu]

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.

I tried to delete my duplicates by one of the above procedure. The procedure is as follows.

1. Copy the Master_Unique_Double table when it is closed.
2. Then paste table as "Structure Only". I have named table as Master_Unique.
3. I want to delete duplicate based on the column "Time". Hence, I stamped that column as Primary Key by going to design view of Master_Unique.
4. Again copy the Master_Unique_Double table, but paste as "Append Data to Existing Table"

However, I do not see that duplicates are getting removed Please let me know where I am doing the mistake. I have attached the table Master_Unique_Duplicate. I am also attaching the table Master_Unique that I have got after doing the above procedure

[June7]

Well, Duplicate has 47 records and the other table has 45 so two records (ID 28 and 38) were not copied.

[drkundu]

Well, Duplicate has 47 records and the other table has 45 so two records (ID 28 and 38) were not copied.

Please see my problem in the attached Access file. I used the similar procedure that discussed in my last e-mail. Duplicates are not getting removed. I am making so mistake. But I could not figure out. Thanks

[June7]

Odd, one of the duplicates is removed but not other.

I retyped the time value in record 12 and then the duplicate 13 was not retained. Something about the time values in records 12 and 13 are not exact duplicates.

This is also demonstrated by query of the original data before my edit:

SELECT DISTINCT [Time] FROM Master_Unique_Duplicate;

[drkundu]

My table is so large. So, I cannot re-type it. Master_Unique_Duplicate table is the combination of two excel table. I have checked that the time format of two excel table is similar. Is there any viable way to solve this issue? Thanks.

[June7]

Are the duplicates always the records that don't have data in other fields? Those records can easily be filtered out.