Access merging records and count

[andy143vivi]

hi, my first time here, great community site, found lots of help for my access development. So here is first question:

how do i merge a multiple record and shows the count as well, im making a library catalogue system, i made a query out of 4 tables, and come up with 4 different fields author_name, isbn, book_title, copy_id

so generally lets say one author has 1 book with several copies in the library, so it shows like this:

author_name: ISBN: book_title: copy_id:
Matthew James 1001 My Book 1
Matthew James 1001 My Book 2
Matthew James 1001 My Book 3

so i wanna merge all of them to look like this

Matthew James 1001 My Book 3 (copies)

how am i able to do this? ive tried the count funtion for SQL though it just count the whole query table, need help thanks

[ADezii]

hi, my first time here, great community site, found lots of help for my access development. So here is first question:

how do i merge a multiple record and shows the count as well, im making a library catalogue system, i made a query out of 4 tables, and come up with 4 different fields author_name, isbn, book_title, copy_id

so generally lets say one author has 1 book with several copies in the library, so it shows like this:

author_name: ISBN: book_title: copy_id:
Matthew James 1001 My Book 1
Matthew James 1001 My Book 2
Matthew James 1001 My Book 3

so i wanna merge all of them to look like this

Matthew James 1001 My Book 3 (copies)

how am i able to do this? ive tried the count funtion for SQL though it just count the whole query table, need help thanks

1. In Query Design Mode ==> View ==> Totals
2. [author_name]: GROUP BY
3. [ISBN]: GROUP BY
4. [book_title]: GROUP BY
5. [copy_id]: COUNT

[andy143vivi]

thank you for the fast response, but iam using access 2007, and i am creating it the records are in the query already, so there is an SQL code on it already, how should i do this with the codes provided? can you please be a bi specific, bit noob here, thanks :)

[ADezii]

thank you for the fast response, but iam using access 2007, and i am creating it the records are in the query already, so there is an SQL code on it already, how should i do this with the codes provided? can you please be a bi specific, bit noob here, thanks :)

Sorry, but I am a little confused on your explanation, try posting the SQL.

[andy143vivi]

hi, i got it already, didnt see the total there, but now ive figured it out, thanks for the great help, one more, ummm... i have a table of username and password field, and would like to use that as a log in for certain access to my forms or queries, any advice how should i do this? thanks