Setting number of rows in a Query (1 Viewer)

[Nevsky78]

Dear all,

I have an Access Query that I have created a Data Connection for in Excel.

I need the same number of rows to pull through from the Access Query (1500) in order to do some analysis on the Query in Excel.

Does anyone know if it is possible to 'Set' the number of rows in the Query to 1500?

This is the Query:

SELECT Raw2010OrdersUnits.scode, AccessFeed.Brand, Raw2010OrdersUnits.Market, IIf(IsNull([Wk 1]),0,[Wk 1]) AS 1, [1]+(IIf(IsNull([Wk 2]),0,[Wk 2])) AS 2, [2]+(IIf(IsNull([Wk 3]),0,[Wk 3])) AS 3, [3]+(IIf(IsNull([Wk 4]),0,[Wk 4])) AS 4, [4]+(IIf(IsNull([Wk 5]),0,[Wk 5])) AS 5, [5]+(IIf(IsNull([Wk 6]),0,[Wk 6])) AS 6, [6]+(IIf(IsNull([Wk 7]),0,[Wk 7])) AS 7, [7]+(IIf(IsNull([Wk 8]),0,[Wk 8])) AS 8, [8]+(IIf(IsNull([Wk 9]),0,[Wk 9])) AS 9, [9]+(IIf(IsNull([Wk 10]),0,[Wk 10])) AS 10, [10]+(IIf(IsNull([Wk 11]),0,[Wk 11])) AS 11, [11]+(IIf(IsNull([Wk 12]),0,[Wk 12])) AS 12, [12]+(IIf(IsNull([Wk 13]),0,[Wk 13])) AS 13, [13]+(IIf(IsNull([Wk 14]),0,[Wk 14])) AS 14, [14]+(IIf(IsNull([Wk 15]),0,[Wk 15])) AS 15, [15]+(IIf(IsNull([Wk 16]),0,[Wk 16])) AS 16, [16]+(IIf(IsNull([Wk 17]),0,[Wk 17])) AS 17, [17]+(IIf(IsNull([Wk 18]),0,[Wk 18])) AS 18, [18]+(IIf(IsNull([Wk 19]),0,[Wk 19])) AS 19, [19]+(IIf(IsNull([Wk 20]),0,[Wk 20])) AS 20, [20]+(IIf(IsNull([Wk 21]),0,[Wk 21])) AS 21, [21]+(IIf(IsNull([Wk 22]),0,[Wk 22])) AS 22, [22]+(IIf(IsNull([Wk 23]),0,[Wk 23])) AS 23, [23]+(IIf(IsNull([Wk 24]),0,[Wk 24])) AS 24, [24]+(IIf(IsNull([Wk 25]),0,[Wk 25])) AS 25, [25]+(IIf(IsNull([Wk 26]),0,[Wk 26])) AS 26, [26]+(IIf(IsNull([Wk 27]),0,[Wk 27])) AS 27, [27]+(IIf(IsNull([Wk 28]),0,[Wk 28])) AS 28, [28]+(IIf(IsNull([Wk 29]),0,[Wk 29])) AS 29, [29]+(IIf(IsNull([Wk 30]),0,[Wk 30])) AS 30, [30]+(IIf(IsNull([Wk 31]),0,[Wk 31])) AS 31, [31]+(IIf(IsNull([Wk 32]),0,[Wk 32])) AS 32, [32]+(IIf(IsNull([Wk 33]),0,[Wk 33])) AS 33, [33]+(IIf(IsNull([Wk 34]),0,[Wk 34])) AS 34, [34]+(IIf(IsNull([Wk 35]),0,[Wk 35])) AS 35, [35]+(IIf(IsNull([Wk 36]),0,[Wk 36])) AS 36, [36]+(IIf(IsNull([Wk 37]),0,[Wk 37])) AS 37, [37]+(IIf(IsNull([Wk 38]),0,[Wk 38])) AS 38, [38]+(IIf(IsNull([Wk 39]),0,[Wk 39])) AS 39, [39]+(IIf(IsNull([Wk 40]),0,[Wk 40])) AS 40, [40]+(IIf(IsNull([Wk 41]),0,[Wk 41])) AS 41, [41]+(IIf(IsNull([Wk 42]),0,[Wk 42])) AS 42, [42]+(IIf(IsNull([Wk 43]),0,[Wk 43])) AS 43, [43]+(IIf(IsNull([Wk 44]),0,[Wk 44])) AS 44, [44]+(IIf(IsNull([Wk 45]),0,[Wk 45])) AS 45, [45]+(IIf(IsNull([Wk 46]),0,[Wk 46])) AS 46, [46]+(IIf(IsNull([Wk 47]),0,[Wk 47])) AS 47, [47]+(IIf(IsNull([Wk 48]),0,[Wk 48])) AS 48, [48]+(IIf(IsNull([Wk 49]),0,[Wk 49])) AS 49, [49]+(IIf(IsNull([Wk 50]),0,[Wk 50])) AS 50, [50]+(IIf(IsNull([Wk 51]),0,[Wk 51])) AS 51, [51]+(IIf(IsNull([Wk 52]),0,[Wk 52])) AS 52, [Raw2010OrdersUnits]![Wk 1] AS [Wk 1], [Raw2010OrdersUnits]![Wk 2] AS [Wk 2], [Raw2010OrdersUnits]![Wk 3] AS [Wk 3], [Raw2010OrdersUnits]![Wk 4] AS [Wk 4], [Raw2010OrdersUnits]![Wk 5] AS [Wk 5], [Raw2010OrdersUnits]![Wk 6] AS [Wk 6], [Raw2010OrdersUnits]![Wk 7] AS [Wk 7], [Raw2010OrdersUnits]![Wk 8] AS [Wk 8], [Raw2010OrdersUnits]![Wk 9] AS [Wk 9], [Raw2010OrdersUnits]![Wk 10] AS [Wk 10], [Raw2010OrdersUnits]![Wk 11] AS [Wk 11], [Raw2010OrdersUnits]![Wk 12] AS [Wk 12], [Raw2010OrdersUnits]![Wk 13] AS [Wk 13], [Raw2010OrdersUnits]![Wk 14] AS [Wk 14], [Raw2010OrdersUnits]![Wk 15] AS [Wk 15], [Raw2010OrdersUnits]![Wk 16] AS [Wk 16], [Raw2010OrdersUnits]![Wk 17] AS [Wk 17], [Raw2010OrdersUnits]![Wk 18] AS [Wk 18], [Raw2010OrdersUnits]![Wk 19] AS [Wk 19], [Raw2010OrdersUnits]![Wk 20] AS [Wk 20], [Raw2010OrdersUnits]![Wk 21] AS [Wk 21], [Raw2010OrdersUnits]![Wk 22] AS [Wk 22], [Raw2010OrdersUnits]![Wk 23] AS [Wk 23], [Raw2010OrdersUnits]![Wk 24] AS [Wk 24], [Raw2010OrdersUnits]![Wk 25] AS [Wk 25], [Raw2010OrdersUnits]![Wk 26] AS [Wk 26], [Raw2010OrdersUnits]![Wk 27] AS [Wk 27], [Raw2010OrdersUnits]![Wk 28] AS [Wk 28], [Raw2010OrdersUnits]![Wk 29] AS [Wk 29], [Raw2010OrdersUnits]![Wk 30] AS [Wk 30], [Raw2010OrdersUnits]![Wk 31] AS [Wk 31], [Raw2010OrdersUnits]![Wk 32] AS [Wk 32], [Raw2010OrdersUnits]![Wk 33] AS [Wk 33], [Raw2010OrdersUnits]![Wk 34] AS [Wk 34], [Raw2010OrdersUnits]![Wk 35] AS [Wk 35], [Raw2010OrdersUnits]![Wk 36] AS [Wk 36], [Raw2010OrdersUnits]![Wk 37] AS [Wk 37], [Raw2010OrdersUnits]![Wk 38] AS [Wk 38], [Raw2010OrdersUnits]![Wk 39] AS [Wk 39], [Raw2010OrdersUnits]![Wk 40] AS [Wk 40], [Raw2010OrdersUnits]![Wk 41] AS [Wk 41], [Raw2010OrdersUnits]![Wk 42] AS [Wk 42], [Raw2010OrdersUnits]![Wk 43] AS [Wk 43], [Raw2010OrdersUnits]![Wk 44] AS [Wk 44], [Raw2010OrdersUnits]![Wk 45] AS [Wk 45], [Raw2010OrdersUnits]![Wk 46] AS [Wk 46], [Raw2010OrdersUnits]![Wk 47] AS [Wk 47], [Raw2010OrdersUnits]![Wk 48] AS [Wk 48], [Raw2010OrdersUnits]![Wk 49] AS [Wk 49], [Raw2010OrdersUnits]![Wk 50] AS [Wk 50], [Raw2010OrdersUnits]![Wk 51] AS [Wk 51], [Raw2010OrdersUnits]![Wk 52] AS [Wk 52]
FROM Raw2010OrdersUnits LEFT JOIN AccessFeed ON Raw2010OrdersUnits.scode=AccessFeed.scode
WHERE (((Raw2010OrdersUnits.Market)=101));

Sorry it's so long!!

Any help would be much appreciated.
Nick

 

[pbaldy]

Try

SELECT TOP 1500 ...
FROM..
WHERE...

You may want an ORDER BY clause as well.

 

[Nevsky78]

Hi Paul,

Thanks for getting back to me. I've included that in the SQL, ran the query and saved it.

I then set up a new Data Connection in Excel but it still on returned 925 rows in this case. I need it to always return 1500 rows (so in this case there will be 575 'blank' rows).

Does that make any sense?!

Nick

 

[Brianwarnock]

What is the point of exporting 500+ blank rows?
I don't know what you are going to do in Excel but it would be easy to set A1500 to a Blank and thus achieve what you require, ie a spreadsheet of 1500 rows.

brian

 

[pbaldy]

The TOP predicate will limit the records returned, but it won't return extra rows to get up to 1500. I can't think of a way offhand, so try Brian's idea. Maybe if you clarified what you were trying to achieve, someone would have an alternate method.

 

[Nevsky78]

Hi guys,

Thank you for your replies.

To try and explain further, I have a Data Connection set up pulling the Access Query via Microsoft Query into Excel. There may be up to 1500 rows over the course of the year but no more than that.

Once the Data Connection is established, I have a series of SUMIFs on the 1500 rows from Rows 1501 down to 1600.

This would be fine to keep updated and run but the problem I have is that I need to use Text to Columns on column A once it is in Excel to change the format from 'Text' to 'General'. When I do this, occasionally the act of Text to Columns inserts rows at the bottom of the sheet, thus pushing out the SUMIF cells.

So I thought if I could 'fix' the Access Query rows it might help in running my Text to Columns macro in Excel. I would run Text to Columns on A2:A1500 and see if that worked any better.

So the main bugbear I have is having to convert from Text to General, due to the vast amount of legacy Excel Spreadsheets I have inherited with General format on the scode fields. I am trying to move as much as possible into Access at the moment.

I think I shall have to approach this differently.

Nick