Create query that shows results in multiple tables ?

[baseborn]

Say my db holds 2 tables. (Books and Categories). In the query, instead of it listing 1 table with all the information i would rather it list them in multiple tables. ex:


Category 1
------------
Book 1


Book 2

Category 2
------------
Book 3
Book 4

Category 3
------------
Book 5
Book 6


How would i go about doing this ? i cant find anything on this subject at all.

[Rixxe]

select a.category, b.books
into tblcategory1
from books as a
inner join categories as b
where (b.category = '1') and (a.book = '1','2')

Then duplicate if needed, for each catergory... Is that the kind of thing you want?

[baseborn]

?? lol, sorry im just trying to do it with the query wizard on access. im trying to avoid having to put this in a script hence using access for the query BUT i think its something like SELECT Books,Categories.name FROM Books, Categories.id = Books.category_id ORDER BY Books.category_id

[Rixxe]

Well you need to give more information about table names, and exactly what you want...
Using SQL code is a good idea, once you have it working you can just select design view, and it should all be there...

Open a query in SQL view, and just put the code in with your field names, and see if it works... Or is what you want...

[baseborn]

Yea im getting a syntax error in the FROM clause with the code i posted..

ok heres my tables:

Books
ID | Category ID | Book Title |


Categories
ID | Category Name


Now my goal is to show 3 tables of Categories and in each table are the books that belong to their category

As i stated in my post:

Category 1
------------
Book 1
Book 2

Category 2
------------
Book 3
Book 4

Category 3
------------
Book 5
Book 6


I thought that instead of going through all the trouble with SLQ (since i dont really know it) that i would just create a query with the wizard

[Rixxe]

SELECT Books.category_id ,Categories.name
FROM Books
inner join Categories
On Categories.id = Books.category_id
ORDER BY Books.category_id

Your code should work now...

[baseborn]

Type mismatch in expression...

[Rixxe]

Check the spelling of the field names, make sure the fields you are joining (ID's) are the same data type.
i.e. both text

[baseborn]

Well good news and bad news on that.. It DID work after correcting the
spelling BUT its nothing close to what i needed..

So heres what i did.. i scratched everything i had been working on over the last few days and started fresh. This time though i decided that i was going to work on my movies list just because i was sick of looking at the word books .. same concept that i wanted to do with the books list. I created a new database and then just created 1 table called Movies with 3 columns; ID,Title, Category; and then enter 5 sample movies into the table. so my table looks like this:


Movies
___________________
ID | Title | Category |
----------------------
1 | Movie A | Comedy |
----------------------
2 | Movie B | Drama |
----------------------
3 | Movie C | Horror |
----------------------
4 | Movie D | Comedy |
----------------------
5 | Movie E | Drama |
----------------------


so i started messing around and i created a query and put this:

SELECT Movies.Title, Movies.[Category]
FROM Movies
WHERE (((Movies.[Category])=[Drama]));


Which when viewed in datasheet it gave me this:


_________________
Title | Category |
-------------------
Movie E | Drama |
-------------------
Movie B | Drama |
-------------------

This is the closest i have got with any code ive put in. from this, what i
need is to just hide the category column in this query i want it still active
but just not shown. THEN.. lol then i need Category (which in this example is "Drama") to be placed at the top, as if the tables name was Drama. It should look like this:


Drama
________
Title |
----------
Movie E |
----------
Movie B |
----------




Granted that i can get this to work i would then need the rest of the
Categories to be listed along with the Drama in the query. like this:



Drama
________
Title |
----------
Movie E |
----------
Movie B |
----------



Comedy
_________
Title |
----------
Movie A |
----------
Movie D |
----------



Horror
_________
Title |
----------
Movie C |
----------


with the possibility of me being able to arrage the order of these
tables to the way i want. aphabeticly or just any. maybe this would
be a time to use VIEW and Virtual tables ?

[SteveF]

What's the ultimate purpose of the query?

IMO, queries should not be used for directly viewing data. If you need to view data, use a query to feed a form or report. You can always preview the report to see it on-screen instead of on paper.

The visual format you laid out is very easy to achieve in a report using a grouping level for "Category"; much more difficult strictly within a query.

Steve

[baseborn]

Hey steve, thanks for joining the topic. so far Rixxie has been a HUGE help so regaurdless of the outcome of this nothing has went to waste. The big picture im trying to achieve is that im working on a private website for family/friends and instead of me having to always repeat eveything i have i just want to list it on this website. so my goal is to have a database and a form. i intend on using the form for entering my movies into the database itself and then just create a .php to put on my website that will show my list, as in the format above. i thought creating a query to put into my .php was how i i made this happen ? pls correct me if im wrong. im going to go re-read about grouping and see what i can come up with. btw, not sure if u have read all the post in this topic but before i even posted this topic on here i was told by someone else that in order to do what im wanting to do i would need to have 2 tables in my DB, 1 for my movies and the other for my categories. obviously my last post i tried doing this with just 1 table. do you think i need to go back to 2 tables or just stay at this 1 ?

[Rixxe]

SELECT Movies.Title, Movies.[Category]
FROM Movies
WHERE (((Movies.[Category])=[Drama]));

Gives you:

Title | Category |
-------------------
Movie E | Drama |
-------------------
Movie B | Drama |
-------------------


So:

SELECT Movies.Title
INTO Drama
FROM Movies
WHERE (((Movies.[Category])=[Drama]))
Order by Movies.Title

This will obviously create a new table, but this is what you want to show, you can then do whatever you want with Forms and reports etc.

[baseborn]

ok i FINALLY got it now! i stuck with having 2 tables and then using the report instead of query but now this creates a problem with my form and i dont know why because i havnt changed anything. my form is setup to where i type in the name of the movie, select which category it belongs to and then a add record button. it adds the title to the movies table but doesn't put it in the category as if the combo box doesn't even exist. (when i say it doesn't put it in the category i don't mean the categories table i mean the category column in the tables field). So i am pulling data off the Categories table with combo box to enter it in Movies table. this is how the form looks.


---------------------------------------------------------

Title |__________|

Text box, to enter the movie title
that enters it into the movies table


Add Category |___________|

Combo box that pulls data of the
Categories table (Category_Name)
but enters it into the Movies table.
(Category Column).

____________
|_Add Record_|

and when i click this it use to work
fine, now it only places the title into
the movies table. so i have a movie with no
category.

Im almost positive that have not changed anything unless i did unintentionally while messing around with another form trying to learn more about how all of it works which i should have done a different db. idiot.. i do know that the relationships have not changed (Movies.Category to Category.Category_Name)