Working With Two Tables

[m4dd0g]

Ok what I want to do is I want to export data from two tables into one excel sheet but the tables have the same name and what i want is to export the information from the tables where the customer number is the same it must put the number and name from one table and the cash data from the other table with is specified between two dates in one excel sheet here is my code for selecting the data from one table between two dates:

mysql = "Select CustomerCode,Description,Date from HistoryLines where " & Chr$(34) & "Date" & Chr$(34) & " Between '" + txtFrom.Text + "' And '" + txtTo.Text + "'"

I need this to be modified to pull my requested data out of the Pervasive SQL database

 

[mirtheil]

Sounds like you need a join condition.
Without knowing the exact structure of the table, I'm only guessing but it'll probably look like:
select T1.Number, T1.Name, T2.CashData from T1, T2 where T1.Number = T2.Number

You'll need to change the field and table names to match your data.


Mirtheil
Certified Pervasive Developer
Certified Pervasive Technician
Custom VB and Btrieve development.

http://www.mirtheil.com

 

[m4dd0g]

OK I could do that but then I loose my date query because i need that line of code you gave me to be altered in away so that it pulls that data out between two dates and two tables.

 

[mirtheil]

The query I gave is an example. You would need to modify it to match the restrictions you need.


Mirtheil
Certified Pervasive Developer
Certified Pervasive Technician
Custom VB and Btrieve development.

http://www.mirtheil.com

 

[m4dd0g]

I modified it like this but it only gives me the data from one table:

mysql = "Select CustomerMaster.Number,CustomerMaster.Description,Date from CustomerMaster,HistoryLines where " & Chr$(34) & "Date" & Chr$(34) & " Between '" + txtFrom(0).Text + "' And '" + txtTo(0).Text + "' And CustomerMaster.Number=HistorlyLines.CustomerCode

 

[mirtheil]

That's because you aren't specifying any fields from the second table. If "Date" is in both tables, you are going to seend to specify which table you want the data to come from.


Mirtheil
Certified Pervasive Developer
Certified Pervasive Technician
Custom VB and Btrieve development.

http://www.mirtheil.com

 

[m4dd0g]

OK so my line of code is right and Date is only in the historylines table but there where is specified the CustomerMaster.Number and The CustomerMaster.Description that data comes from the historylines table and not from the customermaster table like i specified

 

[m4dd0g]

OK when I export I gives me Only three Fields which is number,name and the date from the HistoryLines table nothing from the customermaster table

 

[m4dd0g]

Thanx For all your help and i learned alot from you guys I will recomend this site to all my programming friends
You guys ROCK BIG TIME and i specially want to thank mirtheil for all the help and we will talk again of not in this life time then in the after life
Enjoy the rest of the night or day and be good and keep up the good work and keep this forum alife

And by the Way:
I'm from south africa and want to meet you guys one day.