search function VI

[labviewproject]

Hi, currently i have this search function vi which will display my data from the database which i have query. However, i wish to modify it into a vi which can display data from 2 different table in my ms access. For example, if i enter the input for the unit no., it will display the address & the name in my database. the address is in my address table while the name is in my persondetails table. im currently using ms access for my database & my labview version is 8.5. I oso wish to show my field name at the top of the display table in my front panel. I hope somebody can give me an idea on how i could go about doing this.

[smercurio_fc]

I can't look at your VI right now, and you didn't indicate the schema of your tables, but it sounds to me like you're wanting to use a JOIN clause in your SELECT statement.

[labviewproject]

I tried to use the join clause before but the vi shows me an error.

[smercurio_fc]

---

labviewproject wrote:
I tried to use the join clause

---

In what way?

---

but the vi shows me an error.

---

which is?

[labviewproject]

Hi, the first picture below is the vi which i have attached in the first post. the second picture is the error which comes out after i run the vi. im not really sure if i use the join clause correctly.

[IMG]http://i93.photobucket.com/albums/l65/vittoire/NewPicture.jpg[/IMG]

[IMG]http://i93.photobucket.com/albums/l65/vittoire/NewPicture1.jpg[/IMG]

Message Edited by labviewproject on 07-13-2009 09:13 PM

[Dennis_Knutson]

No, it's not correct. Did you look at the link from smercurio_fc? Since you are using Access, you can also have Access show you the correct SQL syntax. Design a query to return the data. There is then an option to show SQL. I don't remember exactly where the option selection as I don't have Access here and actually have not used anything other than SQL Server or Oracle in quite a few years.

p.s. You also have to have a relationship between the two tables before you can do a join.

[labviewproject]

Hi, i have seen the link which smercurio_fc posted when i tried to join the tables together. I have also used the join clause when i started learning about database design using oracle. i have design a query yesterday after reading up about ms access. Im currently thinking of using macro with activex to display my data. do you have any example regarding using labview sql query or using macro ms access with activex for labview? i have the relationship between the 2 tables done. i read through the ms access book but i could not find the method to use access to show me the correct sql syntax.

[Dennis_Knutson]

My example of doing a join would be similar to the example in the link. Why don't you post details on the relationship between the two tables?

I would not recomend ActiveX for the query. It is significantly slower and you would have to have Access installed instead of just the jet database.

[labviewproject]

in my address table, i have AddID, Unit, BlockNo, Address.

 in my persondetails table, i have OVID, FirstName, LastName, ContactNo, Age, Marital status AddID

my AddID is the pri key for my address table & it is link to my persondetails table as the foreign key.

[Dennis_Knutson · Accepted solution]

Something like:

SELECT address.address, personaldetails.FirstName, personaldetails.LastName

FROM address

INNER JOIN personaldetails

ON address.AddID = personaldetails.AddID