Replies

I think I can get away without doing it (quite whatever it is).

The spreadsheet I’ve imported the data into is hideous beyond belief. There will be a fair amount of manual work for someone (somethings just aren’t possible for me to do). Whoever does that can sort out the ‘sorting’ I can’t (be bothered?) to fix. To be honest, the way I can provide the data can at least be checked easily to see if everthing is there. The re-ordering I was thinking of would actually make it harder to check!

The query is sorted by 2 columns to get the overall proper order.

It’s actually a make table query (not entirely necessary but it helps keep me organised – this is related to my othe recent post with Excel DAO). I’d managed to then use a modify table query to add in a autonumber field.

Unfortunately, I ‘ve realised the ‘row number’ doesn’t actually help with the problem I have. I cant figure out how to actually explain my next question

Thanks again

That’s disappointing but good to know.

Thanks for the quick reply.

Thanks, I don’t think it’s going to be as hard as I thought.

I found this https://www.planet-source-code.com/vb/scrip…=47409&lngWId=1%5B/url%5D and this http://www.aspfree.com/c/a/Database/Data-A…from-Excel-VBA/%5B/url%5D pretty helpful.

That’s kind of what I’m after really.

I’ve tried various things and it almost looks ok with mimimal manual modification needed. Unfortunately, on closer inspection I’ve found on exporting a report with many subreports (each one as a column), the subreport data gets put in the same column in excel.

Fortunately, there are <500 records so even if I come up with some clunky inefficient code it shouldn't take too long to run it.

I think I need to do something with nested recordset loops, i.e first record, paste the columns into excel, move to query for 1st related table, paste data etc, somehow counting so I can move around cells in the worksheet using relative offsets.

Thanks

That seems to do the trick.

Very interesting

Thanks

Glad to hear that!

I didn’t realise you could bring the same table into a query twice and use an incrementing ID to get data from the next record – I’ve always used recordset loops.

I’ve got 3.7 in my head so the table could be wrong or I could be wrong – it ‘s only something someone decided and they may well have changed their mind anyway.

Thanks again.

Hans

1. I’ve modified the querys qryHiLo and qryLoHi to include the height values from adjacent rows, calculating the height difference (using abs when going from Low to High – I suppose I could have just swapped the order of the subtraction).

2. The height_diff field is pulled through into queries qryRising and qryFalling

3. In qryTide I’ve used an IIF statement with the criteria height_diff >=3.7 = ‘Spring’ otherwise Neap (just noticed this is different to th atrributes in tblTides but it doesn’t matter, I’ll just have to check the cut-off value)

I’ve re-labelled the headings to Tidal_Cycle (high, mid, low) , Tidal_Flow for (rising, falling) and Tidal_Phase (spring, neap)

Can you check my logic on this, particularly the first step – I’ve confused everyone in the office!

Wow, that took me a while to figure out what’s going on there.

I also need to pull out whether the time is a spring or neap tide (listed as the phase attribute in the tide table), the cutoff was decided as being neap if the absolute height difference was 3.7m or greater.

I can’t figure out whether to try to use the attribute field or to try to work it out in as similar way to your qryHiLo and qryLoHigh by pulling in the same table twice and used the id + 1

edit: I’m just trying something now which looks sensible – I’ll post it here when I’m done

Hans

I’m wondering if you could help me take this further. Attached is a sample database (actual data) with the original queries you wrote.

I’ve been asked to extract more information, namely the tidal phase (Phase) and whether the tide is rising or falling (falling – nearest tide event is HIGH and in the past OR low and in the future, rising – nearest tide event is low and in the past or high and in the future)

tblTides is real tide data, the date time column is [dt_bst], the tidal phase is [Phase]
tblSurveys are the actual survey dates – I have used the survey start date [SURV_DT_START] but may need to derive a halfway time between a survey’s start and end (surveys were carried out over a few hours).

That’s exactly what I’m after

Sorry for the delay in replying, I’ve just returned from my holiday.

I’m beginning to wonder if there’s a problem you can’t solve.

As always, many thanks

Thanks Hans

I’m struggling a bit today – it’s rather hot in the office (and nearly home time!). Can you elaborate on the queries a bit – i.e what’s the reason for the <0.3 in qryDiff

I may also need (or rather the surveyors will need) to complicate matters.

I think they may want to know the following

time to nearest High
time to near Low

if either of these are say more than 1.5 hours away it'd be mid tidal, otherwise, obtain the nearest high/low as you example.

Seems happier with this

Me.Line (15, 0)-Step(0, (Me.Height) / 56.7), vbBlack
Me.Line (52.91, 0)-Step(0, (Me.Height) / 56.7), vbBlack
Me.Line (87.92, 0)-Step(0, (Me.Height) / 56.7), vbBlack
Me.Line (122.93, 0)-Step(0, (Me.Height) / 56.7), vbBlack
Me.Line (157.4, 0)-Step(0, (Me.Height) / 56.7), vbBlack

and this for a line along the bottom
Me.Line (0, (Me.Height) / 56.7)-Step((Me.Width) / 56.7, (Me.Height) / 56.7), vbBlack, B

Still confused with the DrawWidth property. I’ve read it’s supposed to be pixels but to get something approximating to a 1pt line I used DrawWidth = 15.

I think I’m going to have to see what happens when I actually get given some more data. I only have a few records to work with at the moment. I remember the first db I was involved in – it worked fine until the users entered data

I’m glad I’ve figured out table definition queries which, as the troublesome fields have a sequential numerical suffix, made it pretty easy to write a loop to create the sql for the table.

Thanks for the advice.