Access lookups and relationships question

[djpilot]

Hi,
I have an access 2007 database and the main table consists mainly of lookup fields that are populated from other tables in the database. When I open the main table in access I am shown a drop down list box for each field to select the data (the data displayed is the text from the lookup table. However if I look at the main table using DBPro I can only see the record ID number that identifies the record in the other table/s.

Is there a way to display the lookup text and not show the number?

Thanks in advance

[Gaev]

djpilot:

Take a look at the dbpDefineValueList command ... from the Help file ...

dbpDefineValueList

Identify a field from one table to be used as an input source for a field in another table. When editing in grid mode, a field that has been assigned a value list will function like a combo box allowing the user to select list items from a drop down window.

[djpilot]

Hi Gaev, thanks for that but I had already tried it and it doesn't work the way I need.

Using dbpDefineValueList, the displayed table shows numbers unless I click on a cell to change it. At that time is will show a drop down list box from the related table/field but once I select something the selected text changes back to the number.
As a side note, the table is purely for display purposes, so the end user will have no way to edit it.

I have implemented a work around by creating a temporary table and populating that with a query for each field, but it is very slow, even when compiled.

Do you have any further ideas?
Kind regards

[Neosoft Support]

I think a query/temp table is the only way to display data from multiple tables.

[djpilot]

Thanks. Looks like I will have to use what I have already done but it is very slow.

Would there be any kind of SQL statement that could do what I need?

Thanks

[stu]

One question, if the table if read only why would you put a dropdown on a field?, that's the only part I'm missing. (?)

[djpilot]

Hi stu,
I dont want a dropdown field. As per my original question, I want to display the lookup value not the index number.
In access it automatically puts a dropdown with the lookup from the related table, but in NeoBook it only shows the index number/key.
Gaev suggested that a 'dbpDefineValuelist' may do what I wanted even though I don't need a dropdown, but it doesn't work.
Thanks for your question.

[Gaev]

djpilot:

Looks like I will have to use what I have already done but it is very slow.

Perhaps you can post ...

a) details of all the (looked up) fields in the main and lookup tables ... how many such fields, field types etc. ?

b) your code ... use of dbpExecSQL to create a View might speed things up

c) what you consider slow ... in seconds ... and does it slow down as the number of lookups increases ?

d) the location of the database relative to your machine ... same local disk, on a local server or over the internet ?

[stu]

Create a new view. Join your tables and instead of displaying a table display the view. Should work just fine.

----------

tblCustomers

Code: Select all

 
-------------------------------------------
|      ID    |    NAME    |    COUNTRY    |
-------------------------------------------
|      1     |    NICK    |  RUSSIA       |
|      2     |    JANE    |  CHINA        |
|      3     |    MARK    |  N. COREA     |
|      4     |    PAGE    |  VIETNAM      |
-------------------------------------------


tblProducts

Code: Select all

 
-------------------------------------------
|      ID    |    NAME    |    PRICE      |
-------------------------------------------
|      1     |  YORKIE    |  1.30         |
|      2     |  MARS      |  2.67         |
|      3     |  SNICKERS  |  1.99         |
|      4     |  CARAMEL   |  1.99         |
-------------------------------------------


tblOrders

Code: Select all

 
-------------------------------------------
|      ID    |  CUSTOMERID  |  PRODUCTID  |
-------------------------------------------
|      1     |     3        |  4          |
|      2     |     1        |  2          |
|      3     |     1        |  3          |
|      4     |     4        |  2          |
|      5     |     2        |  3          |
|      6     |     1        |  4          |
-------------------------------------------


Then Inner join to get the result you want to display.

Code: Select all

SELECT
  `tblcustomers`.`NAME` AS `CustomerName`,
  `tblproducts`.`NAME` AS `ProductName`,
  `tblorders`.`ID` AS `ID`
FROM ((`tblorders`
  JOIN `tblcustomers`
    ON ((`tblorders`.`CUSTOMERID` = `tblcustomers`.`ID`)))
  JOIN `tblproducts`
    ON ((`tblorders`.`PRODUCTID` = `tblproducts`.`ID`)))

Result:

[djpilot]

Hi Gaev, I have provided some answers to your questions further down in my post, as always thanks for your input and guidance.

Hi stu, thanks for such a detailed response. However I don't understand something. You say to create a view, but I am not clear on this. What do I create a view of? Do you mean (in NeoBook DBPro speak) to "dbpShowGrid" as I can't find a command that says "CreateView". Do I need to create a new table for the view?, if so, do I create the fields as strings to display the data or as integers to hold the lookup value? If I don't use the NeoBook DBPro "dbpShowGrid" then how do I go about doing it?

Please forgive my ignorance or stupidity, I am certainly not an expert and know just a little about databases and even less about sql.

I have a table named 'songs'

Code: Select all

--------------------------------------------------------------------------------
|      ID    |  songtitle  |  artist |  genre  | songfile                      |
--------------------------------------------------------------------------------
|      1     |     3        |  4     |    4    |\\192.168.0.1\Music\song1.mp3  |
|      2     |     1        |  2     |    3    |C:\Users\Temp\Music\song2.mp3  |
|      3     |     1        |  3     |    3    |\\192.168.0.1\Music\song3.mp3  |
|      4     |     4        |  2     |    4    |C:\Users\Temp\Music\song2.mp3  |
|      5     |     2        |  3     |    2    |\\192.168.0.1\Music\song5.mp3  |
|      6     |     1        |  4     |    3    |\\192.168.0.1\Music\song6.mp3  |
--------------------------------------------------------------------------------


Then I have multiple tables that are similar to the following (each has only an ID and a string field. The ID in each table is the primary key, auto-increment, and unique).
An example of the 'songtitle' table

Code: Select all

--------------------------------
|      ID    |  songtitle      |
--------------------------------
|      1     | hot in here     |
|      2     | there she goes  |
|      3     | shake it up     |
--------------------------------


An example of the 'Artist' table

Code: Select all

--------------------------------
|      ID    |  artist         |
--------------------------------
|      1     | marky tom       |
|      2     | the deadly cats |
|      3     | Roxanne Clint   |
--------------------------------


The database file is located on the local drive, however the music files can be located somewhere on the local network (the network location is not relevant as the file is only read when it get loaded to be played. The data to be displayed always comes from the database file located on the local hard drive.
The time it takes is 1 to 2 seconds. The actual query to show a specific CD is very fast, it is the looping through the quantity of tracks on the CD and populating the temporary table that takes the time.
Example actual code below;

Code: Select all

Loop "1" "[music.songs.$RecCount]" "[lpy]"
  dbpGotoRecord "music" "songs" "[lpy]"
  dbpAddRecord "music" "temptable"

  dbpFind "music" "cdname" "ID" "[music.songs.cdname]" "ExactMatch=No;CaseSensitive=No"
  SetVar "[music.temptable.cdname]" "[music.cdname.cdname]"

  dbpFind "music" "songtitle" "ID" "[music.songs.songtitle]" "ExactMatch=No;CaseSensitive=No"
  SetVar "[music.temptable.songtitle]" "[music.songtitle.songtitle]"

  dbpFind "music" "artists" "ID" "[music.songs.artist]" "ExactMatch=No;CaseSensitive=No"
  SetVar "[music.temptable.artist]" "[music.artists.artist]"

  dbpFind "music" "genre" "ID" "[music.songs.genre]" "ExactMatch=No;CaseSensitive=No"
  SetVar "[music.temptable.genre]" "[music.genre.genre]"

  dbpFind "music" "yearreleased" "ID" "[music.songs.yearreleased]" "ExactMatch=No;CaseSensitive=No"
  SetVar "[music.temptable.yearreleased]" "[music.yearreleased.yearreleased]"

  dbpFind "music" "copyright" "ID" "[music.songs.copyright]" "ExactMatch=No;CaseSensitive=No"
  SetVar "[music.temptable.copyright]" "[music.copyright.copyright]"

  dbpFind "music" "tracktype" "ID" "[music.songs.tracktype]" "ExactMatch=No;CaseSensitive=No"
  SetVar "[music.temptable.tracktype]" "[music.tracktype.tracktype]"
 
  SetVar "[music.temptable.filesong]" "[music.songs.filesong]"
  SetVar "[music.temptable.filecover]" "[music.songs.filecover]"
EndLoop

I really do appreciate all your help (stu and Gaev) it is invaluable and helps me learn. I have spent several hours trying to find a solution with Google but haven't succeeded. This could be because I am not sure how to ask the right question (search term) or the fact that I know what I want to do, just not how to ask the question.
Thanks again.

[djpilot]

Hi Stu, Now I feel like an idiot. I just found the dbpCreateView command and think I understand what you mean. I will have a play and see how I go.
Thanks again.

[stu]

Hi Stu, Now I feel like an idiot. I just found the dbpCreateView command and think I understand what you mean. I will have a play and see how I go.
Thanks again.

Hey dude, no worries... I'ts all about helping YO! hahaha

A view its no other that a query... I don't use dbpCreateView because I don't know why. Maybe because I was working with another rab that didn't had that facility. Anyway what I do is create the view from the outside, more particulary with dbForge Studio (I manage all my mySQL dbs with this) and then I just dbpopentable the view same as I would do with a table and display the content.

I will take a look at dbpCreateView since might solve some of my own old doubts! =)

Cheers!

[djpilot]

Hi stu,

I have been playing with this for quite some time now and was always getting errors with your code. I even created a database basically copying the tables you showed in your post and still couldn't get it to work.
After quite some time I have managed to get it to work and I have included the dbpExecSql code that I used for reference.
I still haven't implemented this on the real database as yet, just got it working on a sample.

Code: Select all

SELECT
tblCDs.ID,
tblSong.fldSONG as Song,
tblArtist.fldArtist as Artist

FROM ((tblCDs
  INNER JOIN tblSONG
     ON tblCDs.SongID = tblSong.ID)
  INNER JOIN tblArtist
     ON tblCDs.ArtistID = tblArtist.ID)

Thanks for your help.

[stu]

Hi stu,

I have been playing with this for quite some time now and was always getting errors with your code. I even created a database basically copying the tables you showed in your post and still couldn't get it to work.
After quite some time I have managed to get it to work and I have included the dbpExecSql code that I used for reference.
I still haven't implemented this on the real database as yet, just got it working on a sample.

Code: Select all

SELECT
tblCDs.ID,
tblSong.fldSONG as Song,
tblArtist.fldArtist as Artist

FROM ((tblCDs
  INNER JOIN tblSONG
     ON tblCDs.SongID = tblSong.ID)
  INNER JOIN tblArtist
     ON tblCDs.ArtistID = tblArtist.ID)

Thanks for your help.

Oh, yeah. Sorry about that... The query I posted comes from dbForge, I figured there should be some discrepancies but if you create your view from outside NB will work fine...

have a look:

[Gaev]

djpilot:

Looks like you are half way there with the info provided by stu ... note that ...

1) you do not need to use external facilities to create the view that he suggested ... you can supply the SQL commands within dbpExecSQL.

2) if you are not familiar with raw SQL commands (and their syntax), this section of the w3 schools website can be a good place to start learning.

3) dbpQuery might be faster than dbpFind ... especially if you setup the "looked up" tables with Primary Keys for the "looked up" fields

4) I am assuming that access to files/databases on the local network is generally fast e.g. if you were to view one of those tables directly, it does not take long.

5) You did not mention the sizes (number of records) in each of the tables ... might give consideration to "replicating the looked up tables" on each machine ... and refreshing the tables when there was a change to the master table.