0

I am creating an Acquisition Management System for a Library in LibreBase (v7.5.4), thus far, I am at the following: A table called BooksDB containing the fields

  1. Accession No (NUMERIC type) (also Primary Key)
  2. Title(VARCHAR type)
  3. Other information, etc.

Another table called AcqSys containing the fields

  1. Acq ID (Primary Key)
  2. Acquisition Date
  3. Acquired From
  4. Order Amount
  5. Other information, etc.

Another table called AcqEnt containing the fields

  1. AcqUEID (Primary Key)
  2. Accession No (NUMERIC type)
  3. Title (VARCHAR type)
  4. Book Cost
  5. Acq ID
  6. Other information, etc.

The Form frmAcqSys is made of two tables (Main Form = AcqSys, and Sub Form = AcqEnt, relationship linked via Acq ID, with the view that one AcqID shall contain various AcqEntries)

Now, based on Accession No entered in AcqEnt subform, I want the AcqEnt.Title to auto-populated/fetch data from BooksDB table for the corresponding Title in BooksDB for the same Accession No (AcqEnt.Title to fetch BooksDB.Title based on AcqEnt.Acession No = BooksDB.Accession No).

I have been unable to change data type to SQL in a text field, so I am using a ListBox for AcqEnt.Title in subform AcqEnt, and using the following SQL:

SELECT "Title", "Title" FROM "tblBDB" WHERE "Accession No" = "AcqEnt.Accession No"

or

SELECT "Title", "Title" FROM "tblBDB" WHERE "Accession No" = 'AcqEnt.Accession No'

They are returning the following error:

SQL Status: S0022
Error code: -28

Column not found: AcqEnt.Accession No in statement [SELECT "Title", "Title" FROM "tblBDB" WHERE "Accession No" = "AcqEnt.Accession No"]

How do I do it? Is there a better/more efficient way to achieve the output?

table AcqEnttable BooksDB

Keshav
  • 21
  • 1
  • 2
  • if you want to access data from another table you need to join them or maybe in your case make a subs query to get the access no you want – nbk Jul 18 '23 at 19:01

1 Answers1

0

You need to JOIN both tablews, so that you can accees all columms from both tables.

You need to add the table name to ambiguous column names as well

SELECT "tblBDB.Title", "AcqEnt.Title" 
FROM "tblBDB"  
JOIN "AcqEnt"
ON "tblBDB.Accession No" = "AcqEnt.Accession No"
nbk
  • 45,398
  • 8
  • 30
  • 47
  • Thanks @nbk for your reply. [this image shows relationships](https://imgur.com/a/S8bhg5W) I entered the SQL code you gave in DropDown box for AcqEnt.Title drop-down in AcqEnt subform It shows the following error: ````SQL Status: S0022 Error code: -28 Column not found: tblBDB.Accession No in statement [SELECT "tblBDB.Title", "AcqEnt.Title" FROM "tblBDB" JOIN "AcqEnt" ON "tblBDB.Accession No" = "AcqEnt.Accession No"]```` – Keshav Jul 19 '23 at 11:09
  • i would check for spaces in the name – nbk Jul 19 '23 at 14:03