Records getting interchanged in Table (1 Viewer)

Hi everyone,

I have my query below that I was using till last evening.
God knows what suddenly happened. I am not an expert user of MS Access. So need help fixing it.
Please see the issue is as below:-

My data table name ABC Sales Data has following fields which is giving my problem is as below:-
Date Account Pubcode Revenue


What went wrong is few records in Date column records got interchanged with Revenue column data records ( like 25000 out of 40000, rest 15000 were fine).
Also revenue figures got automatically converted into data with time stamp.
What I tried doing is correcting all the records manually and then re upload the whole 40000 file, still post running the queries, records getting interchanged.

My query is as below:

1)DELETE [ABC Sales Data].Date
FROM [ABC Sales Data]
WHERE ((([ABC Sales Data].Date)>#12/31/2016#));

2)INSERT INTO [ABC Sales Data] ( ACCOUNT, [PUB CODE], REVENUE, [Date] )
SELECT [ABC 2017 H1].Customer, [ABC 2017 H1].Media, [ABC 2017 H1].Revenue, [ABC 2017 H1].Date
FROM [ABC 2017 H1];

3)INSERT INTO [ABC Sales Data] ( ACCOUNT, [PUB CODE], REVENUE, [Date] )
SELECT [ABC 2017 H1 Real Estate].ACCOUNT, [ABC 2017 H1 Real Estate].[PUB CODE], [ABC 2017 H1 Real Estate].Revenue, [ABC 2017 H1 Real Estate].Date
FROM [ABC 2017 H1 Real Estate];

4)SELECT DISTINCT [ABC Sales Data].ACCOUNT
FROM [ABC Sales Data] LEFT JOIN [ABC Client table] ON [ABC Sales Data].[ACCOUNT] = [ABC Client table].[Customer]
WHERE ((([ABC Client table].Customer) Is Null));

5)INSERT INTO [ABC Client table] ( Customer )
SELECT [03 ABC Sales Data Without Matching ABC Client table].ACCOUNT
FROM [03 ABC Sales Data Without Matching ABC Client table];

6)SELECT DISTINCT [ABC Sales Data].[PUB CODE]
FROM [ABC Sales Data] LEFT JOIN [ABC Pubcode table] ON [ABC Sales Data].[PUB CODE] = [ABC Pubcode table].[Pubcode]
WHERE ((([ABC Pubcode table].Pubcode) Is Null));

7)INSERT INTO [ABC Pubcode table] ( Pubcode )
SELECT [04 ABC Sales Data Without Matching ABC Pubcode table].[PUB CODE]
FROM [04 ABC Sales Data Without Matching ABC Pubcode table];

8)SELECT First([ABC Client table].Customer) AS [Customer Field], Count([ABC Client table].Customer) AS NumberOfDups
FROM [ABC Client table]
GROUP BY [ABC Client table].Customer
HAVING (((Count([ABC Client table].Customer))>1));

9)SELECT [ABC Date Convertion].Date, [ABC Date Convertion].Month, [ABC Date Convertion].Calendar, [ABC Client table].Customer, [ABC Client table].[Primedia Account], [ABC Client table].[Parent Account], [ABC Client table].Rep, [ABC Client table].Team, [ABC Client table].Auction, [ABC Client table].NRS, [ABC Client table].Category,
IIf([Calendar]=2017,[Revenue],0) AS [2017 revenue], IIf([Calendar]=2016,[Revenue],0) AS [2016 revenue], [ABC Pubcode table].Pubname, 1 AS [transaction]
FROM (([ABC Sales Data] INNER JOIN [ABC Date Convertion] ON [ABC Sales Data].DATE = [ABC Date Convertion].date) INNER JOIN [ABC Client table] ON [ABC Sales Data].ACCOUNT = [ABC Client table].Customer) INNER JOIN [ABC Pubcode table] ON [ABC Sales Data].[PUB CODE] = [ABC Pubcode table].Pubcode
WHERE ((([ABC Client table].Rep)<>"star") AND (([ABC Client table].Team)<>"out"));


Any help would save me.

Thanks.

When you say "Records get interchanged" I have two ways to interpret that, but the most likely is that the order of record appearance in the table appears to change. That is, you run something, look at the order of records, and they change - but the records do not interchange DATA with one another, only POSITION in the table.

If this is your complaint, then that is (technically) correct Access behavior because records in a table have no guaranteed order. The only way to see things in a specific order is to use a QUERY that imposes order based on a field such as your date field.

You see, the basis for SQL is set theory, in which all records are (effectively) treated in a bunch, not in a specific order. Therefore, when you try to look at the records via a table rather than an ordering query, you see whatever Access wants to present to you.

If your problem is more complex than that, please describe more specifically.

I'll ask the obvious. Do you have a recent backup?