SQL in VB

[lsc]

I need help with a complicated set of queries. I have 4 tables, each have a start and end date and they have either 8 fields or 12 fields for an amount. If the start date <=Date() and the end date >=Date() then I need to pull some of the amount fields and insert them into a temp table. The amount must be >0. Currently this is done by queries but this takes up too much space because there is a query for each amount field. To the best of my knowledge, there can only be one field that appends to another field in append queries. So if I need to insert fields amt1, amt2, and amt3 from table1 into the amount field of the temp table (along with the other corresponding information in table1), I have to use three different queries. Then this is repeated for the other 3 tables and their fields.

What I would like to do is write an SQL or series of SQL statements in a module that will take the place of the queries. Is there any way to consolidate some of the SQL statements and make this process more efficient?

I appreciate the help!

 

[Rich]

"To the best of my knowledge, there can only be one field that appends to another field in append queries. So if I need to insert fields amt1, amt2, and amt3 from table1"? what do you mean amnt1+amnt2 etc into one field or three separate fields

 

[lsc]

In an append query, I can only append one amount field from table1 to the amount field in the temp table. So say from Table 1 I need to append ID, start date, end date, amt1, amt2, and amt3 to the temp table to the fields, ID, start date, end date, and amount. I can't append amt1, amt2, and amt3 to the Amount in one query. Does that make sense?

 

[lsc]

Pat, Could you tell me a little more changing the tables so I don't have to 'do this normalization "on the fly"'?

I've been reading about normalization. I'm not sure it will work in my case but I'll do some testing. Thank you! I appreciate your suggestion and input!!


[This message has been edited by lsc (edited 04-16-2002).]