Search results

Thanks CJ!

Dear All When I use DoCmd.Outputto to create a spread sheet from a query, most (but not all) of the date fields appear in the resultant spread sheet in the format DD-Mon-YY, despite the fact that hh:mm is included in the data in each cell (which you can see when you click on it). Is there any...

Of course...

Thanks Plog. I suppose it's going to have to be minutes! In practice I would only really need this level of granularity when 3 or more records are concurrent, which is going to be very rare. Does that help with processing time at all?

Sorry Plog. I sense your annoyance, it certainly hasn't been my intention to move goalposts, I think I'm just struggling to explain. I have used the same word "occasions" in both posts very deliberately because it doesn't denote a defined interval of time. This is because returning the number...

Dear Plog Thank you very much for your reply! I've learned a huge amount from this method, it's been extremely helpful and I can see this being a very fruitful avenue for me to explore for various purposes in the future. With respect to my current task though I've already hit another wall...

Dear All I have a table in which each record has a start date and an end date. The following query appears successfully to count the number of occasions on which one record within the table overlaps with another: SELECT Count(1) FROM tblMain AS t1 INNER JOIN tblMain AS t2 ON [t1].[id] <...

Thank you very much for the response Isskint, it was very helpful indeed. Sorry for not replying sooner - I ended up attempting to conserve my energy for today, and it's given me some time to think about what you said. In terms of fields referencing the old PK I've got off pretty lightly I...

Dear All My database is currently in use in three offices and each office has its own discrete copy. I now wish to combine the three databases so that all the offices have access to the same data - and in order to do so I must append all of the table data together, and one particular (the most...

a) I would rather not b) As above c) It's one union query, just a very long one, because I need the user of the database to be able to ask their choice of up to 30 questions of the data at the same time, and display the results on the same report. Edit - I do not intend to be rude with my...

Dear All My database employs an extremely long and complicated union query, which is only going to get longer and more complicated. It struck me today that all of the 30-ish select statements in the query share one common WHERE clause. Would that union query run more quickly if I based it on...

Thank you Paul.

Sorry Paul, I appreciate that that wasn't the SQL, I just couldn't immediately gather what you'd learn from the SQL that I hadn't already described. Thank you for responding.

Completely standard append query with two data sources: - ExprX: [Forms]![Form]![TextBox or ComboBox] - ExprY: [TempVars]![MyVariable] Now I have added a third data source: - ExprZ: [Table].[Field] ...and hey presto it works.

Paul it was simply a list of expressions defined as text/combo boxes in the form and a few temporary variables. It seems that as long as ONE field is derived from a table then the append query works perfectly. Probably another of those database fundamentals that I've somehow contrived not to know.

I've found a rather inelegant workaround - some of the text boxes on the form now write to a table that temporarily holds that data, the append query pulls some of the fields from that table and the rest from the form, then the temporary record in the table gets deleted. It works, I only get...

Dear All I'd be very grateful for some help with what feels like a humiliating simple problem. I want an append query to create a new record in a table and populate that record with fields from a form. Some of these fields are free text, others are from combo boxes (which is, I suspect, where...

Thanks for the responses chaps. I would dearly love the solution to be as simple as described, but I can categorically assure you that every value of PersonID in every record in tblOrders corresponds to a valid ID number for a record in tblPeople and that every record in tblPeople has a...

Hi All I wonder if anyone can help me with a problem around joins in a query? Let's say I have tblOrders and tblPeople. tblOrders contains a PersonID field corresponding to a record in tblPeople. Each record in tblPeople has a PersonName text field. The tables are set as related in...

I thought I'd post the code I created to solve this problem, in case another beginner has similar requirement in future. I hope it's not too sloppy, it seems to work. The first thing I did was to change the query behind the report. It still returns records from tblData filtered by an ID from...