MS Query..Query...

[m0atz]

Hi all,

I'm pretty new to MS Query (in fact only started using it today!). Perhaps a basic question then, but what I'm trying to achieve is a simple sheet which enables me to look up customer detail using their mobile number.

I've got a huge worksheet of customer info and I've made the query to give me the data i want. I've set parameters up so that when I type the mobile number in Cell A1 it refreshes the query and gives me the name address postcode etc.

My problem is that my customer info worksheet is that huge that there are more than 65536 records. For example there is ColA which is "Customer Name" this has 65536 records. Then in col AA the Col is repeated with another approx 40,000 or so details. When I import the data into MS Query it appends the second "Customer Name" col with "Customer Name1" therefore I dont get the results all in one list.

So when I'm searching for a number, i'm not getting the right results because I'm not sure i've set it up correctly to search in both fields. Does this make sense? Hope so

any advice appreciated.

Col

 

[Fazza]

Hi, Col.

It sounds like there is one table of data split into two tables. An approach is to have the SQL combine the two tables back into one table. Assuming both tables have defined names table_1 and Table_2, and field names field_n this is done like,

SELECT field_1, field_2, field_3
FROM table_1
UNION ALL
SELECT field_1, field_2, field_3
FROM table_2

This can be extended to multiple tables, btw. To also account for the parameterisation, this combination can be done at a lower level and the paramater step act on this at a higher level. Such as,

SELECT field_1, field_2, field_3
FROM (
SELECT field_1, field_2, field_3
FROM table_1
UNION ALL
SELECT field_1, field_2, field_3
FROM table_2)
WHERE field_1 = '?'

The lower level combination is enclosed with parentheses. This gives a flavour of the approach. For specific help, please post your SQL.

You can see & edit your SQL via the 'SQL' button in MS Query, or via the VBE(VB editor).

I'll find a link to a thread that has some relevant links. Though for sure google can find lots, lots more.

regards, Fazza

 

[Fazza]

this is the thread I was thinking of, http://www.mrexcel.com/forum/showthread.php?t=315768

 

[m0atz]

Fazza, thanks for the reply. The link you posted certainly given me more food for thought and the code is very useful.

I think what I need to do is examine exactly what I want to achieve as there are several ways of getting the output I want and this is pointing me down the right path.

I'll let you know how I get on, in the mean time much appreciated.

Col

 

[Fazza]

You're most welcome, Col. Lots of fun stuff to learn - enjoy. F