Transfering criteria matching records into one table

I have the following data which are in different tables
          Address.db           Name and Birthday of Husband
          Wife.db              Name and Birthday of Wifw
          Child1.Db            Name and Birthday of First child
          Child2.Db            Name and Birthday of Second child
(due to table design the data have to be in different tables and not just in one table)
There are other tables and other data which are not relevant for the present problem.
What i wanted to do was based on some criteria like 'display name and birthday for birthdays occuring
during the next one week' i want  all the
records satisfying the criteria to be stored and displayed in one dbgrid with two fields
name and birthday.
I have done it in the following manner
Check the criteria against each of the table data(address,wife,child1,child2 etc).If any record
satisfies the criteria, a new record is inserted into the Birthdays.db(name and Birthdate fields) till
all the tables are checked.Finally the data is displayed in the dbgrid.
When the next criteria is required emptyTable is done for Birthdays.db and the whole process is done again.

Even though the above  works fine ,can SQL do a better job? ie by just firing one query
all the work gets done of transfering the criteia matching data from different tables into one table only

thanks
venks

           
LVL 1
venksAsked:
Who is Participating?
 
kretzschmarConnect With a Mentor Commented:
hi venks,

this would be become more complex. well a working solution for paradox (tested)(other databases may have other functions)

procedure TForm1.Button1Click(Sender: TObject);
const
  SQLStatementPart =
    'select name, birthday, Cast((Cast(Extract(Day from Birthday) As Char(2))||''.''||'+
    'Cast(Extract(Month from Birthday) As Char(2))||''.''||%s)as date) As TheBirthDate '+
    'from %s where cast((Cast(Extract(Day from Birthday) As Char(2))||''.''||'+
    'Cast(Extract(Month from Birthday) As Char(2))||''.''||%s) as date) '+
    'between :FromDate and :ToDate %s';
var
  y,m,d : Word;
  S : String;
  SRes : String;
begin
  query1.Close;
  query1.sql.Clear;
  DecodeDate(DateTimePicker1.Date,y,m,d);
  s := QuotedStr(IntToStr(y));
  FmtStr(SRes,SQLStatementPart,[s,'Address',s,'Union all']);
  query1.Sql.Add(SRes);
  FmtStr(SRes,SQLStatementPart,[s,'Wife',s,'Union all']);
  query1.Sql.Add(SRes);
  FmtStr(SRes,SQLStatementPart,[s,'Child1',s,'Union all']);
  query1.Sql.Add(SRes);
  FmtStr(SRes,SQLStatementPart,[s,'Child2',s,'Order by TheBirthDate']);
  query1.Sql.Add(SRes);
  Query1.ParamByName('FromDate').AsDateTime := Trunc(DateTimePicker1.Date);
  Query1.ParamByName('ToDate').AsDateTime := Trunc(DateTimePicker2.Date);
  query1.Open;
end;

a little explaination what i'm doing:

i get name and birthday from one table, calculate TheBirthDate by building day and month from birthday and year from datetimepicker1. Because the year cannot passed as parameter i build a part from the sql statment as const and fill the variant parts by using FmtStr.

restrictions:
you can query only in one year, it is not possible to query birthdays between 1.12.2000 and 1.1.2001

remarks:
depending on your regional settings, you must replace '.' with '/' and/or swap the day<->month order in the const SQLStatementPart.

try it out

meikl ;-)
0
 
kretzschmarCommented:
hi venks,

this should do what you need in sql

procedure TForm1.Button1Click(Sender: TObject);
//query1 holds:
//select name, birthday from address where birthday between :FromDate and :ToDate union all
//select name, birthday from wife where birthday between :FromDate and :ToDate union all
//select name, birthday from child1 where birthday between :FromDate and :ToDate union all
//select name, birthday from child2 where birthday between :FromDate and :ToDate
begin
  query1.Close;
  Query1.ParamByName('FromDate').AsDateTime := Trunc(DateTimePicker1.Date);
  Query1.ParamByName('ToDate').AsDateTime := Trunc(DateTimePicker2.Date);
  query1.Open;
end;


try it out

meikl
0
 
kretzschmarCommented:
hi again venks,

for ordering just append an order by clause like

select name, birthday from address where birthday between :FromDate and :ToDate union all
select name, birthday from wife where birthday between :FromDate and :ToDate union all
select name, birthday from child1 where birthday between :FromDate and :ToDate union all
select name, birthday from child2 where birthday between :FromDate and :ToDate
order by brithday

meikl
0
Get your problem seen by more experts

Be seen. Boost your question’s priority for more expert views and faster solutions

 
venksAuthor Commented:
Dear meiki
Thanks for the great help.It is working
beautifully.Hence  i am awarding the points.
The only problem that i am facing is that all the birthdates are input with
the year in which the person was born eg
10/04/1956 etc,Bur in the query we have to check the birthdays for this year ie 2000.
Hence what should i do(ie with out changing the inputted data in the tables)
Even though this was not part of the original question,I would be grateful if you could answer this
thanks once again
venks
0
 
venksAuthor Commented:
Dear meikl
Thanks for the superb work.I have already wriiten code to do the required work through table component.
But your code is much shorter and I have learnt a lot
venks
0
 
kretzschmarCommented:
:-) glad to helped you
0
All Courses

From novice to tech pro — start learning today.