query lots of different ranges

[freddie]

One table of data has been extracted from a live read-only database via odbc.

I wish to add a new field to each record based on the combination of two other fields - Nom and Dept below



I'm trying to use a query with no joins and the query returns a new line for each output from my iif clause.

	NewField	Nom	Nom2	Nom3	Nom4	Nom5	NomFr	NomTo	NomFr2	NomTo2	NomFr3	NomTo3	Dept	Dept2	Dept3	DeptFr	DeptTo	DeptFr2	DeptTo2	DeptFr3	DeptTo3	DeptFr4	DeptTo4
	Name 1	7000	7006													101	229	250	274	276	289	291	299
	Name 2	5009	5055				7300	7400	8001	8251	7808	7809	700
	Name 3	7100	2300	2331	7000	7006							0	500
	Name 4	4001	4400										202	49	701	1	8
	Name 5	4420											29
	Name 6	4400	5071	5055									572
	Name 7	4400	4020				4010	4011					701			300	399

NewField: iif(table.nom = nom.nom or table.nom =nom2.nom2 or table.nom =nom3.nom3 or table.nom =nom4.nom4 or table.nom between nomfr.nomfr and nomto.nomto or table.nom between nomfr2.nomfr2 and nomto2.nomto2 or table.nom between nomfr3.nomfr3 and nomto3.nomto3,iif(table.dept = dept.dept or table.dept = dept2.dept2 or table.dept between deptfr.deptfr and deptto.deptto or table.dept between deptfr2.deptfr2 and deptto2.deptto2 or table.dept between deptfr3.deptfr3 and deptto3.deptto3 or table.dept between deptfr4.deptfr4 and deptto4.deptto4,data.newfield))

There must be a better way- this takes 4 minutes and returns duplicate lines - this stops me searching for null values so that I can identify gaps in the complicated field mapping.

There are 10,000,000 max combinations but only about 5,000 in use.

How should I plan this query?

[June7]

What is this data? What do you mean by 'field mapping'?

This looks like non-normalized data structure.

If you don't use join clause, the query will do Cartesian relation - all records of each table will join with all records of other tables.

Exactly what are you trying to determine from this calculation?

[freddie]

Dear Moderator,

Thanks so much for the reply.

The result I am trying to achieve is to speed up the query. I can achieve the result I want by filtering - but the result is too slow (4 minutes 20 seconds) which makes me think there is a better way.

The data are accounting transaction records with departmental, nominal and other fields as below.

TRAN_NUMBER	TYPE	Date	Nominal	DEPT_NUMBER	DETAILS	Amt
1	SI	03/04/2012	4020	410	Residential	-580
2	SI	03/04/2012	4020	410	Residential	-5
2	SI	03/05/2012	4020	410	Residential	-575
3	SI	03/05/2012	4020	410	Residential	-10
3	SI	06/06/2012	4020	410	Residential	-570
4	SI	06/06/2012	4020	410	Residential	-15
4	SI	03/07/2012	4020	410	Residential	-565
5	SI	03/07/2012	4020	410	Residential	-20
5	SI	03/08/2012	4020	410	Residential	-560

By field mapping I mean the table in the previous post which shows the combinations of department and nominal values.

One of these combinations needs to match in order for a value to be inserted into the new field.

[Thus if Nominal value is 7000 or 7006 and Department value is between ((101 and 299) or (250 and 274) or (276 and 289) or (291 and 299)) then the New_Field value is Name_1.]

The iif clause (slightly different to the one quoted) successfully matches Nominal 4020 with Dept 410 to produce New Field 105 as below, but with an extra record added. Indeed it works completely as intended except for the adding of the extra records which I think slows it down a lot.

TRAN_NUMBER	TYPE	Date	Nominal	DEPT_NUMBER	DETAILS	Amt	New_Field
1	SI	03/04/2012	4020	410	Residential	-580	105
1	SI	03/04/2012	4020	410	Residential	-580	0
2	SI	03/04/2012	4020	410	Residential	-5	105
2	SI	03/05/2012	4020	410	Residential	-575	105
2	SI	03/04/2012	4020	410	Residential	-5	0
2	SI	03/05/2012	4020	410	Residential	-575	0
3	SI	03/05/2012	4020	410	Residential	-10	105
3	SI	06/06/2012	4020	410	Residential	-570	105
3	SI	06/06/2012	4020	410	Residential	-570	0
3	SI	03/05/2012	4020	410	Residential	-10	0
4	SI	06/06/2012	4020	410	Residential	-15	105
4	SI	03/07/2012	4020	410	Residential	-565	105
4	SI	06/06/2012	4020	410	Residential	-15	0
4	SI	03/07/2012	4020	410	Residential	-565	0
5	SI	03/07/2012	4020	410	Residential	-20	105
5	SI	03/08/2012	4020	410	Residential	-560	105
5	SI	03/08/2012	4020	410	Residential	-560	0
5	SI	03/07/2012	4020	410	Residential	-20	0
6	SI	03/08/2012	4020	410	Residential	-25	105
6	SI	03/09/2012	4020	410	Residential	-555	105

Below is the intended result.

TRAN_NUMBER	TYPE	Date	Nominal	DEPT_NUMBER	DETAILS	Amt	New_Field
1	SI	03/04/2012	4020	410	Residential	-580	105
2	SI	03/04/2012	4020	410	Residential	-5	105
2	SI	03/05/2012	4020	410	Residential	-575	105
3	SI	03/05/2012	4020	410	Residential	-10	105
3	SI	06/06/2012	4020	410	Residential	-570	105
4	SI	06/06/2012	4020	410	Residential	-15	105
4	SI	03/07/2012	4020	410	Residential	-565	105
5	SI	03/07/2012	4020	410	Residential	-20	105
5	SI	03/08/2012	4020	410	Residential	-560	105

I can achieve this result by filtering (in the design view) but the query is too slow and I am therefore wondering if there is a better way of planning the query to speed it up.

Kind regards,

Freddie

[June7]

I don't see a department 410 in the mapping table so can't reconcile with the output examples.

Normalization is usually the best solution. For the mapping table, maybe like:

Nom	NomFr	NomTo	Dept	DeptFr	DeptTo
7000				101	229
7000				250	274
7000				276	289
7000				291	299
7006				101	229
7006				250	274
7006				276	289
7006				291	299
5009	7300	7400	700
5009	8001	8251	700
5009	7808	7809	700
5055	7300	7400	700
5055	8001	8251	700
5055	7808	7809	700

In other words, a record for every possible combination (or at least the 5,000 in use). Since I still don't understand what this mapping table data is, hard to say if I have this normalization correct. For instance, why does the first record of the original table not have a Dept?

I did say 'usually'. I do have a situation where a normalized table would have been more trouble than the 'flat-file' table I have. I use VBA code to extract values from the table, no attempt to even try an expression in Cartesian query. I expect it is the Cartesian relating that is making the query so slow.

[freddie]

Dear June,

The Dept field is missing from the first record because all the fields starting with Dep refer to dept_number in the original data. Thus all of Dept1, Dept2, DeptFr, DeptTo, DeptFr1 and DeptTo2 (etc) are referring to the same field in the original data. The same applies to the fields beginning Nom which all refer to the Nominal_Code field in the original data.

I am trying to find any records in the original data which have a nominal code which is either in Nom, Nom1, Nom2 (etc) or in the range between NomFr and NomTo or in the range NomFr2 to NomTo2 (etc.)

This is the missing record from the data mapping table which refers to the outputs quoted

New_Field

Nom

Nom2

Nom3

Nom4

Nom5

NomFr

NomTo

NomFr2

NomTo2

NomFr3

NomTo3

Dept

Dept2

Dept3

DeptFr

DeptTo

DeptFr2

DeptTo2

DeptFr3

DeptTo3

DeptFr4

DeptTo4

105

4020

402

499

In this record the query checks to see if any record has a match with Nom1 to Nom5, or NomFr-NomFr3 to NomTo-NomTo3 (i.e. 4020) with the various Dept Fields and ranges i.e. department range 1 - the range between 402 and 499. In this case Dept 410 falls within that departmental range. Thus the combination, Nominal 4020 with Department 410, returns New_Field 105.

Would your suggestion mean that in fact I should only have three fields in my data mapping table? NewField Nom and Dept and join them to the original data?

Is that what you mean by writing VBA code (which I have no experience with?) I.e. do I write a code to extract all the permutations and present them in a table that can be joined?

For example

Dept

I've been thinking about the strategy to map the nom-dept combinations which is based on making a list of the final product - the New_Field.

The problem with that list is that the field is new and does not exist in the existing data so I cannot join a table based on that layout with the original data.

If, however, I changed the way I plan the data, so that I am listing the nominal codes and their combinations then I can join the nominal codes to the original data.

I think this will work because I think the right hand (dept) side of the datamapping table can still be used and all I need to do is reduce the nominal side of data mapping table to a list of nominal/new_field combinations. Thus a new record for each nominal and a further new line for each nominal-new_field combination.

Only 80 nominals have more than one new_field combination.

Some VBA code to help with this task would be fabulous.

I think this is better than using VBA to produce a full 3 column solution because this solution will make it easy to check for omissions and duplicates.

The example below shows how the 4020/410 combination would be mapped.

New_Field	Nom	Dept	Dept2	Dept3	DeptFr	DeptTo	DeptFr2	DeptTo2
105	4020				402	499

If the nominal code 4020 is associated with more than one new_field (which it isn't) I would insert another record for that nominal code. Thus

New_Field	Nom	Dept	Dept2	Dept3	DeptFr	DeptTo	DeptFr2	DeptTo2
105	4020				402	499
106	4020	999	555		200	299	700	755

I hope this will be fast, not have the duplicate line on the output, will be easy to spot duplicate mappings and omissions in the mapping and will pick up the omissions in the mapping in the output. None of this currently happens.

If you could give me a clue how to write VBA code to expand the left hand side of the table and join the nom output field in the output data to the nominal_code field in the underlying data that would be very helpful?

Might I say how helpful you have been in helping me to think straight.

Kind regards
Freddie.

[June7]

I can't offer any code because I still don't understand what this is all about. Your explanation for why there is no data in Dept for the first record means nothing to me. Those last examples still don't look normalized enough to me.

The table in first post is the ODBC data source?

I just realized the expression in first post references 22 tables. It's no wonder the query takes forever with 22 tables in a Cartesian relation! There are serious flaws in this data structure and I really don't understand it well enough. If you want to provide db for analysis, follow instructions at bottom of my post.