Recent content by jtkjames

additional - having tested it on my Core i5 laptop, creating a year in VBA as above takes only 1-2 seconds, something which my users may just have to live with, if there isn't a better way to generate that table? Also, I am having a bit of trouble with my modified Xtab, I have tried the left...

Thanks for the reply! I think I understand - I need to make a one-field table with one record per day, and use this table to drive the columns in the Xtab. This however prompts an additional question - is the only way to do this using VBA to create a table, or can I create some SQL to do it...

Hi all Long time since I last posted in here!!! I have spent the last few hours trying to find a solution to an issue I am having with an XTab query, but to no avail, so was wondering if anyone else could cast their eye over it to see if it is even possible (I am thinking it might not be)...

Sorry for resurrecting this from the dead, but it might help anyone searching for the function (as I was)... WHERE type= '" & pgroup & "' should be changed to WHERE fieldname= '" & pgroup & "' where fieldname is the name of the field you are grouping by. Additionally, to group by...

I figured this out, the WHERE type= '" & pgroup & "' should be changed to WHERE fieldname= '" & pgroup & "' where fieldname is the name of the field you are grouping by. Additionally, to group by more than one field, I modified it again: Function MedianF(pTable As String...

I've solved it, but I think this just points to it being a bug: I saved it as an "Access 2000" format database rather than "2002-2003" [this is in A2007] and now when pressing a button it doesn't ask for them twice anymore. Hopefully this might help someone out there!!!

Good morning all (at least, it's morning in the UK) Not posted for a while, but I have hit a brick wall with my MDB developed in A2007. I have a query, it's a Xtab which takes data from an Aggregated Select, which in turn takes data from a table. The query is not sorted in datasheet view...

Thanks so much for the quick response Rabbie I appreciate that it would be more secure and less prone to corruption to use append queries, but for my purposes I need to be able to use make table queries. Do I presume that my fears are true and it is in fact impossible given the structure...

Hello I have an issue with a make-table query. Im not sure if I should have posted this in teh Table or Query sections of the forum, so I apologise if this is inappropriately classified. I am using Access XP/2002 and the MDB is Access 2002 format and has user level security enabled...

thanks for you help with this one! I managed to get it to work using Dlookup. Still can't get it to transpose using unions - but at least I got summat working...

Hi again I've tried to make a union all for 60 months and its come back Query Too Complex. Most I can do is 7. I've tried nesting the unions but still no avail, even so - 7x7 = 49 at the most via nesting. I know it's bad practice - but I've got the Dlookup to work on my example, but for some...

OK i have been reading and it seems the DLOOKUP function might help me overcome this? I tried adding Expr1: DLookUp("["&SaleDate&"]", "tblStandardPrices", "") but this doesn't return anything. Is this a fundamental problem, or just my bad syntax? I don't get an error or anything, just...

I never thought of that (d'oh) :rolleyes: One final Q - Will it work unioning 60 months (5 years worth) - which is the data I have to work with? IIRC there is a limit - I could try but I don't want to get half way through and find a "query too complex" error :eek: :)

unfortunately both tables are linked to CSV sheets on a network drive (archaic but required) which in turn are produced by some 1990s POS that they use. ...so no modification possible there. But a query could produce a modified equivalent to a table? I have read a bit about normalisation...

Hi all, I am having a bit of trouble with a query I want to make, such that I can't actually figure out how to do it (tried trial and error, and about an hour of googling). Basically I have a Select Query with two tables joined. One table has a series of rows with a "sale date" field and...