Complex SQL Query

[FlakyJake]

I have a table that I have to select out of it all the records that have requested a brochure on their own accord (ie thay have not received a mailer). This enatils searcing all fields, to make sure there is no MAIL4, Encl Ms, etc etc. As this is a UNIX import all the fields are all over the place and each record has its own data in its own column. This is because some have multiple bookings and multiple brochures etc. I tried to do this with a sql query which ended up huge. Also the codes I have to select from are nearly three pages in a word document.

My original query is below the data extract, which works on the word MAIL, however I have to search on all the other codes as well to recover all the records of "people who have requested a brochure without a mailer being sent"

My question is, is there a simpler way of doing this? or am I missing something here.......


0000388,Active,MR,M,abc,5 Wave Drive,,Kansas,Kent,DT6 1ZX,UK,01999
123456,SAME,,Aþ0000388,0105297,Firm,12469,10/08/02,OC,12470,12580,12584þ0000388,Broch
BH98,0010884,05/11/97,11/03/98,OC,MAIL4,,Encl 99,,07/10/98,10/06/99,,99CLIENT,,,Broch
BH99,0035504,04/11/98,10/11/98,OC,99BROC,,,,Encl MS,,04/06/99,04/06/99,,99C,,,,,Broch
BH00,,17/11/99,17/11/99,,97R,,,,,,Encl 01,,31/10/00,31/10/00,,CMAIL3,,,,,,,Encl
02,,20/10/01,20/10/01,,CMAIL3,,,,,,,,Broch
BH02,0096836,20/11/01,30/11/01,OC,2002BROREQ,,,,,,,,,Broch BH03,,21/11/02,21/11/02,,02C


QUERY

SELECT [NO BOOKINGS].ID, [NO BOOKINGS].Field1, [NO BOOKINGS].Status, [NO BOOKINGS].Title, [NO BOOKINGS].Initial, [NO BOOKINGS].Surname, [NO BOOKINGS].Address, [NO BOOKINGS].Address1, [NO BOOKINGS].Town, [NO BOOKINGS].County, [NO BOOKINGS].[Post Code], [NO BOOKINGS].Country, [NO BOOKINGS].Telephone, [NO BOOKINGS].Telephone2, [NO BOOKINGS].Field14, [NO BOOKINGS].Email, [NO BOOKINGS].Bookings, [NO BOOKINGS].Field17, [NO BOOKINGS].Booked_Date, [NO BOOKINGS].Field19, [NO BOOKINGS].Field20, [NO BOOKINGS].Field21, [NO BOOKINGS].Field22, [NO BOOKINGS].Field23, [NO BOOKINGS].Field24, [NO BOOKINGS].Field25, [NO BOOKINGS].Field26, [NO BOOKINGS].Field27, [NO BOOKINGS].Field28, [NO BOOKINGS].Field29, [NO BOOKINGS].Field30, [NO BOOKINGS].Field31, [NO BOOKINGS].Field32, [NO BOOKINGS].Field33, [NO BOOKINGS].Field34, [NO BOOKINGS].Field35, [NO BOOKINGS].Field36, [NO BOOKINGS].Field37, [NO BOOKINGS].Field38, [NO BOOKINGS].Field39, [NO BOOKINGS].Field40, [NO BOOKINGS].Field41, [NO BOOKINGS].Field42, [NO BOOKINGS].Field43, [NO BOOKINGS].Field44, [NO BOOKINGS].Field45, [NO BOOKINGS].Field46, [NO BOOKINGS].Field47, [NO BOOKINGS].Field48, [NO BOOKINGS].Field49, [NO BOOKINGS].Field50, [NO BOOKINGS].Field51, [NO BOOKINGS].Field52, [NO BOOKINGS].Field53, [NO BOOKINGS].Field54, [NO BOOKINGS].Field55, [NO BOOKINGS].Field56, [NO BOOKINGS].Field57, [NO BOOKINGS].Field58, [NO BOOKINGS].Field59, [NO BOOKINGS].Field60, [NO BOOKINGS].Field61, [NO BOOKINGS].Field62, [NO BOOKINGS].Field63, [NO BOOKINGS].Field64
FROM [NO BOOKINGS]
WHERE ((([NO BOOKINGS].Field58) Not Like "*MAIL*&quot) OR ((([NO BOOKINGS].Field17) Not Like "*MAIL*&quot) OR ((([NO BOOKINGS].Booked_Date) Not Like "*MAIL*&quot) OR ((([NO BOOKINGS].Field19) Not Like "*MAIL*&quot) OR ((([NO BOOKINGS].Field20) Not Like "*MAIL*&quot) OR ((([NO BOOKINGS].Field21) Not Like "*MAIL*&quot) OR ((([NO BOOKINGS].Field22) Not Like "*MAIL*&quot) OR ((([NO BOOKINGS].Field23) Not Like "*MAIL*&quot) OR ((([NO BOOKINGS].Field24) Not Like "*MAIL*&quot);

 

[willir]

FlakyJake

I responded to your other posting, but I am seeing more of the bigger picture with this post.

Your SQL statement looks fine. It can be cleaned up a bit for readability - use an alias for referencing the NO_BOOKINGS table.

SELECT b.ID, b.Field1 ...
FROM [NO BOOKINGS] as b


On to more issues...

- Is the NO_BOOKINGS table static, or is it recreated from time-to-time?

- How often do you run this query and under what conditions?

I suspect this query takes time to run. (The import file is mostly not indexed on desired fields, and you are running a query on 120,000 records with numerous OR conditions.)

If you run the search for specific queries -- is this person in the database, is that person in the database, then I suspect you get a lengthy time delay for each individual search - probably very frustrating for the user.

Suggestion 1...
If this is a one time condition, ie, a migration project, then normalize then consider NO_BOOKINGS table.


Suggestion 2...
If this file is recreated from time-to-time, consider coding an import routine. From what I understand, you are really only concerned about information if "MAIL" condition is not found. Part of the import module would combine your fields 17, 19, 20, 21, 22, 23, 24, 58 and book_date into one search field. For example,

IF not isnull(b.field17) then strSearchBook = strSearchBook & b.field17

And then write strSearchBook to your NEWLY DEFINDED field in the NO_BOOKINGS table. (And this field would be indexed!)

This approach will speed up your quries, and the import module can be run everytime the NO_BOOKINGS table changes.

Suggestion 3...
If you run this statement to produce a listing infrequently, or the data does not change to often, or you work with the same data over and over, then consider changing your SQL select statement into a MAKETABLE query. This would write your listing to a table for general use. (And I suspect it would be much faster to search this table, and hopefully get an unsuccessful hit meaning that there is an address.)

Suggestion 4...
If the import table is external to the Access database is to use Unix tools instead of SQL to test for a hit. A pretty useful tool is "grep"

grep "YourAddress" NO_BOOKINGS

Will return 0 is successful. (I would have to search my Unix books for an unsuccessful hit)

The only issue with this approach is a false match where your search hits a non-associated record.

The NT environment have similar, but not as powerful, search tools.

This approach would probably be faster than running the SQL statement.

Good luck
Richard

 

[FlakyJake]

Thanks Richard that is extremely helpful.

No this is a one time migration from an old bookings database. I would under normal conditions normalize but that is a huge task in itself and as with ALL clients time is against me..

I have created two tables now ,BOOKINGS and NO BOOKINGS. However the mailing codes are all over the place. Query would only be run a few times to create a mailing list. No time constraints.

My three searches are:-

1 - Clients who have booked in the last 3 years
2 - Clients who requested a brochure in the last 3 years of their own accord
(not previously on our database) but have not yet booked
3 - Clients who booked more than 3 years ago, have since requested a
brochure of their own accord, but not booked
4 - Clients who booked more than 3 years ago, have since requested a
brochure due to a mailing from us, but not booked
5 - Clients who have been on our database for brochure requests or mailings
for more than 3 years and have never booked

1 and 5 dealt with, using simple queries as that part of the data is easy to find. However the mailing data is in various fields depending on how many brochures and bookings they have completed.

I have input a listing of all the mail codes in another table to simplify it, so just need to search for people who have or have not got listed these codes in various fields, so I thought a lookup function on the other table, but not sure how then how to search the various fields in the other table.

You idea of putting all the data into one field certainly seems to fit the bill. How would that be best accomplished?

Andrew (and thanks)

 

[willir]

Andrew




++++-----------------++++++++++++++++++++++++++++++++




















000000000000000000000000q

 

[lespaul]

Keyboard get stuck Richard? I have to say that is the most useless post I've ever seen in TT!!!!!
[ROFL]

Not that I'm able to help, I know SQUAT about UNIX!

Leslie

 

[willir]

Andrew

What a wonderful post. I had to leave my office and some threw a report on to my keyboard!

Since this is a one-time only event, the path of least resistance may be best.

The method with the "best" control would be to use VBA coding to run through the table and build the appropriate modifications. This type of approach can also be used to "normalize" the data during the pass.

You can run a series of UPDATE queries to go through each field and update the search field when appropriate. This will be faster and easier, but you will have less control.

Your solution will depend on your comfort level with coding.

I will add to this post later -- in the middle of a server upgrade. In the mean time, check out syntax for update statement.

Richard

 

[FlakyJake]

LesPaul

Just think of it as a big text file, and not in a columnar format, ie some columns will have extra info, so pushes other columns out to right.

Hence messy import which makes queries a pain as you cannot specify one field.


Richard

Not very comfortable with vba, mainly used dbases on web so confident in asp. #pointers would help.

Also as a one off wouldn't merging the fields into one ie mailing fields so that i have to search using string constraint on that one field?

what would be the format for doing such a thing?

FlakyJake

 

[willir]

Andrew

To add valid data to a single search field, try the following (on a backup of course)...

- Add a new field to NO_BOOKINGS, say MailSearch. Define it as a text field with enough characters to handle all address fields (but can not go over 255).
- Index it to accept null values and duplicates

Then try this UPDATE statement. (You may have to tweak to suite your needs).

UPDATE [NO BOOKINGS] SET [NO BOOKINGS].MailSearch = [Trim(NZ([field17])) & Trim(NZ([field19])) & Trim(NZ([field20])) & Trim(NZ([field21])) & Trim(NZ([field22])) & Trim(NZ([field23])) & Trim(NZ([field24])) & Trim(NZ([field58])) & Trim(NZ([book_date]))

NZ function handles null values
Trim function removes leading and trailing spaces

This field will look pretty ugly after the update, but you just want to find/search for a string within the field.

Richard