Query text in a table / variable length tables

[sts023]

Sorry for this ridiculously amateurish query – I’m a recently made redundant ex-mainframe programmer with a smattering of Microsoft VBA code , doing voluntary work for a charity for the blind.

The Background
We send out “Talking Books” to Borrowers.
We use Access 2003 to keep track of what’s happening.
We have a Books Table, and a Members Table
The Members table holds Name and Address details etc.
The Books Table holds Title, Author, Genre, currently-on-loan etc.

The Project
We want to be able to accept instructions from the Members, like “I want all Crime, but not by Ellis Peters or Val McDermid”. I know we can run a Query to identify the next book to send, but first, we have to either in some way save the Query in the Member Table, or at the time we receive instructions from the Member, identify the likely books and stick them in a table somewhere.
Obviously, the first option is preferable, ‘cos we may get more matching books in during the life of the query, but as I said earlier, this is my first venture into Access, and that approach probably needs someone with a lot more experience than I have.

My Questions

1) Is it possible to create a Table (Member Table) which has within it another table (Books I’ve read/want to read) with a variable number of entries in it, and if so, what’s the best way to access the “sub-table”?

2) Is it possible to save a Query as an entry in a Table, and if so, how do I get that saved query text into an executable Query?

Any help/guidance you can offer will be gratefully received!

 

[MarkK]

So you already have members and books. Now you need a definition for how a member and a book are related, which is at least one more table.
One analogous structure you can consider is Customer->Order->OrderDetail<-Product, where a customer might have many orders, and each order may contain many products (details). You'd have something like Member->Borrow->BorrowDetail<-Book, where a member might borrow many times, and each time he borrows he might borrow many books.
I might do something more generic like Member->BookList->BookListDetail<-Book where a member might have mutiple BookLists, some of which are loans, but some of which might be more sophisticated, like a must-read list, request lists. So BookListType might be Loan, Requests, Must-Read, Favourites, etc...
To implement the BookList concept you need two new tables, BookList and BookListDetail. These tables will need to look like ...

[B]tBookList[/B]
'represents a discrete typed list associated with a member
BookListID (PK)
MemberID (FK)
ListType
Dated
Due 

[B]tBookListDetail[/B]
'relates a single book to a single list, and thereby to a single member
BookListDetailID (PK)
BookListID (FK)
BookID (FK)

Does that help?

 

[sts023]

Thanks lagbolt - some interesting concepts, but I can't help thinking that the approach misses the "select by genre" option, which, coupled with the arrival of new books, is why I think that the "stored seach" approach may be the eventual answer.
Sadly, I've got a lot of learning to do before I can implement a solution!!!

 

[MarkK]

I think my post quite specifically addresses your question 1) where you want to relate to a member the ...

(Books [he's] read/want to read) with a variable number of entries in it

In respect to 2) you can indeed store any kind of text in a table, noting that a text field has a max of 255 characters, so you may need to use a memo if your SQL is long.
Assuming you have already saved executable query text in a field in a table, you can retrieve that text ...

dim sql as string
sql = DLookup("SQL", "tMySavedQueries", "Genre = 'Crime'")

...and create a querydef on the fly...

dim qdf as dao.querydef
set qdf = currentdb.createquerydef("YourQueryName", sqlHere)

...and show the query or open a recordset or whatever ...

docmd.openquery "YourQueryName"
dim rst as dao.recordset
set rst = qdf.openrecordset

Does that help?

 

[sts023]

lagbolt....

You're certainly saving me a lot of "I wonder if you can....?" searches .

I think I should have been more forthcoming about my level of Access code experience - to date somewhat less than 12 hours, so things in your replies are only just starting to make sense (like references to PK [Primary Key?] and FK [Foreign Key?]).

Thanks for your perseverance with such an amateur as myself - things are slowly coming together - when I get stuck again I'll certainly come to Access World Forums for help.

As a last question - there seem to be lots of free "Teach yourself Access VBA" sites, most of which seem to be riddled with irritating adverts. Do you happen to know of any good ones? I'm registered blind myself, and use screen magnificaion software, which sometimes "loses" part of the text, so in my experience PDF based sites are usually better.

Thanks in advance....

Steve

 

[MarkK]

- I'm not aware of any structured or comprehensive intructional material. The scope of important knowledge is just too broad. I learn by reading reference material one grain at a time to answer the immediate problem.
- I think Microsoft help files are really really useful and note that the help you get in a code window is different and specifically tailored to learning about VBA language elements, the Access object model, and other tools available in code.
- The object browser is well of information. It shows you all the tools that are available to you as provided by various object models that you can reference.
- Search this site too. Or post here!!!
Cheers,

 

[boblarson]

As a last question - there seem to be lots of free "Teach yourself Access VBA" sites, most of which seem to be riddled with irritating adverts. Do you happen to know of any good ones?

I know of FunctionX which has some good, structured tutorials. Yes, they have a bit of advertising but it does keep the content free.

Access 2007

Access 2007 VBA

Access 2003

Access 2003 VBA

 

[sts023]

Thanks Bob, I've bookmarked the FunctionX sites, and will plod through them, trying to avoid those annoying "roll-over me for an advert" words!

Steve

 

[boblarson]

trying to avoid those annoying "roll-over me for an advert" words!

I hear you on that one. I don't like those either.