Base files screwed up since lost connection

[avvy651]

I have 2 libre office base files which suddenly went to 0 bytes, and all the data disappeared. After some time, hours?, the data in 1 of the base files re appeared!
the problem is that some of the data was not as it should be, and I had to correct it.
On that said base, I have several tables, queries and forms, and 1 form , after correcting the data, does not read the relevant query correctly.
I have the query to read specific data from a table, and there are functions to read min, max and count etc. The form does not read that data correctly in that it does not count and produce the correct information.

I’ll upload that base file if required.
Thanks

[RobertG]

Upload the file here, if there are no critical data inside.

Which database do you use? Is there a backup with (nearly) the last data?

[avvy651]

It is LibreOffice dBase, yes I have a backup of base file.
the form that does not read the data correctly from the query is called Temp&Rain. there only 2 records in it for this month so far, and it previously read the average,counted and the min and max , but now it doesn’t.

Oh dear , the file, 3.6mb, has no gone back to 0 bytes again and everything has disappeared. It seems the connection has been lost again!!

The backup file is now also not reading data correctly. I have had to delete a lot of non required tables and queries for the upload.
this backup file has 2 records for this month in the table, but the query only shows 1 record, as does the form Temp&Rain.
Also in the tblWXdataLeeds, the ID has changed from a number to a date. If I change it back I lose the data…aaargh!

LeedsWeather3.odb (895.0 KB)

[RobertG]

There is a query qryTemp&Rain. It will show one row.
There is a form Temp&Rain - also one row.
The query will group all values by the current year. So the query could only get 1 row, which has been calculated from the 2 entries in the original table (2023-01-01 and 2023-01-02).

tblWXdataLeeds:
How has this table been created? There is no primary key. ID1 is VARCHAR …
ReadDate is wrong in 2 of the rows: 2202-02-16 and 2108-12-05 (sort the data)

[avvy651]

The original table was created by ‘ratslinger’ on here a good few years ago when I moved over to libreoffice base from MS Access.
As I say, it all got changed when base kept and finally lost connection and all the data was lost. The complete base file went from 3.6mb to 0 bytes in seconds.
the file came back to it’s original size some hours later, but it was all messed up.
And if I try to change anything I lose all the records.

I have just made up another tblWXDataLeeds and the data is all sorted with a 4 digit year as it was before yesterday. Also the date is now a Date field and there is a primary ID1 key now
In the qryTemp&Rain, I have set a between date in sql:
{d ‘2023-01-01’ } AND {d ‘2023-01-31’ } , but it is not being read , just 1 day
There are 3 records in the table now but still only 1 is being read.

[RobertG]

@avvy651

In the qryTemp&Rain, I have set a between date in sql:
{d ‘2023-01-01’ } AND {d ‘2023-01-31’ } , but it is not being read , just 1 day
There are 3 records in the table now but still only 1 is being read.

This will always show only one record:

--qryTemp&Rain
SELECT 
	MAX( "TempMaxC" ) AS "Warmest Day", 
	MIN( "TempMaxC" ) AS "Coldest Day", 
	AVG( "TempMaxC" ) AS "Average Temp", 
	MAX( "TempMinC" ) AS "Warmest Night", 
	MIN( "TempMinC" ) AS "Coldest Night", 
	SUM( "RainFall" ) AS "Total rain so far", 
	COUNT( "RainFall" ) AS "No of Days", 
	MAX( "WindGust" ) AS "Max Wind Gust" 
FROM 
	"tblWXdataLeeds" 
WHERE 
	"ReadDate" 
BETWEEN 
	{d '2023-01-01' } AND {d '2023-01-31' } 
GROUP BY 
	YEAR( "ReadDate" )

There are only aggregate functions. It has been grouped by YEAR(“ReadDate”). And you only want to see the content of one year, year 2023.
Change

BETWEEN 
	{d '2023-01-01' } AND {d '2023-01-31' } 

to

BETWEEN 
	{d '2022-01-01' } AND {d '2023-01-31' } 

and you will get 2 rows, because there are data from 2 years.
Might be you want to see MAX, MIN and so on for every week, the you have to group by WEEK(“ReadDate”)

[avvy651]

Thanks but previous to the base file going to 0 bytes, all the queries worked fine and showed the data I wanted when I used
BETWEEN (d ‘2022-12-01’) AND (d ‘2022-12-31’) for example
The qryTemp&Rain is used to give me the max and min readings for that month only.

GROUP BY MONTH gives 13 records and GROUP BY WEEK gives 41 records!!

[RobertG]

I don’t know which data should be shown for you. Do you want to see max and min for a day, a week, a year?
If you set

@avvy651

BETWEEN (d ‘2022-12-01’) AND (d ‘2022-12-31’)

you will get it for 2022-12, and it also will be only one row.

[avvy651]

Oh dear, I’ve done it again!
I have just realised that the qryTemp&Rain WILL ONLY show 1 record because it is taking the readings from all 3 days,so far, to give the max, min and avg etc!!!
Just to let you know, I have built a new weather database, copied the working files to it and got working the ones which didn’t.

Thanks