The SitePoint Forums have moved.

You can now find them here.
This forum is now closed to new posts, but you can browse existing content.
You can find out more information about the move and how to open a new account (if necessary) here.
If you get stuck you can get support by emailing forums@sitepoint.com

If this is your first visit, be sure to
check out the FAQ by clicking the
link above. You may have to register
before you can post: click the register link above to proceed. To start viewing messages,
select the forum that you want to visit from the selection below.

Truncated Memo Field output

Hi,

I am using two different select statements for two different pages but both selecting on the same table in MS Access 2003. Yet the output of SQLQuery 2 is truncating the output of the file_desc field to 255 chars. Any ideas? I have tried moving the file_data.file_desc field to the end of each section in SQLQuery 2 and that made no difference.

I thought if you use "group by" then you had to include all fields? I have removed the group by and replace HAVING with WHERE in the 2nd query which seems to have worked. However I have another query from a different page which needs the group by option as it is using the First() function as well. Or does it need to group? I got an error when replace the group by line in this query so it might need something more. Any ideas?

The first is preventing duplicate values from appearing. Where the data is being taken from more than one table and being cross referenced with others I was getting results apearing mulitiple times. Which is when the access query builder came to the rescue and I managed to get the results I wanted.

So would the best answer be to seperately fetch the file_desc field maybe when I am looping though the results? Surely creating and destroying 20 recordsets per page can't be good. Is there a better way to join it in?

The first is preventing duplicate values from appearing. Where the data is being taken from more than one table and being cross referenced with others I was getting results apearing mulitiple times. Which is when the access query builder came to the rescue and I managed to get the results I wanted.

OK. But that's a poor way to get a unique resullt set IMO. You could use the DISTINCT keyword.

Originally Posted by wavman

So would the best answer be to seperately fetch the file_desc field maybe when I am looping though the results? Surely creating and destroying 20 recordsets per page can't be good. Is there a better way to join it in?

Indeed. Opening n recordsets to get extra data is a *terrible* way to do it, you would have to slightly mad to try. Give me some time, and I'll post some code which will help you.

I used the employees table especially because it contains a memo field ("Notes").

Code:

SELECT distinct e.LastName, e.FirstName, e.Notes from Employees AS e, Orders o
where
e.City in (SELECT top 1 Employees.City FROM Employees GROUP BY Employees.City ORDER BY Count(Employees.City) DESC)
and e.employeeid = o.employeeid

This query is using set theory, to answer the quetion: "List me the firstname, lastname and notes of all employees who have made sales and who live in the city where the most employees live."

Sorry for not looking sooner, but have just got back to working on this project and realised I hadn't had an email saying there was a reply.

Still a bit confused I'm afriad! The issue I'm trying to resolve requires getting info from several tables and checking that info against each other to ensure the user has permission to see the info I am going to display to them. Am I beign really thick or does your example only work for one table?