Recent content by bkiser

That's close, but doesn't return every matching VALUE field. The result set looks like this: Ben 2 Phone <Ben's phone number> Mark 1 Email <Mark's email address> But Ben actually has multiple values, not just a phone number. Same with Mark. I tried just switched the INNER...

SUBCATEGORYID. Ben and Mark have unique IDs in the SubCategories table, and there's a foreign key in the FieldValues table containing that SUBCATEGORYID. I have to admit I've lost myself with this database design, although it's normalized pretty well. Perhaps I just need to add an ID field...

Ah, I see what you mean. No, it's not 1-to-1 based on that relation. It's a 1-to-many, based on that. I had planned on using the FIELDVALUES.SUBCATEGORYID to differentiate which values goes to which SUBCATEGORY. Do you think to change the table structure? Although I set out to have a clean...

Hi, PHV. The pk of FieldValues is ID. However, when I relate it to the FIELDS table, I use this relationship: FIELDS.ID = FIELDVALUES.FIELDID Does that help explain anything?

Thanks, Scriverb. That works syntactically (minus the AS clause in the group by), but the results still don't look right. I'm still getting Ben's Phone listed twice, once for him and once for Mark. The reverse is also true with Mark--I get his phone and Ben's as well. I am actually working...

Scriverb, Thanks--something's still not right, though. It's a little hard to describe, but I'll try. My original SQL statement gave me all the subcategories with their respective fieldnames, your query gives me all the subcategories with the same fieldname repeated for each subcatgory. Here...

I'm getting a syntax error "missing operator" on this query. Maybe the parenthesis are off?

Hello. I have a SQL statement like this that works fine: SELECT Subcategories.Subcategory, Subcategories.ID as SubCategoryID, fields.FieldName, fields.id as FieldID from Fields Inner Join Subcategories on Fields.CategoryID=Subcategories.CategoryID But I need to add another table to the mix...

Leslie, No, you were right! That works. The table name was wrong, and that's all. You guys are great. I appreciate all the patience you have showed to me. I am just coming back to the Microsoft world after 5 years, and I am SO glad to be back! Take care, and thanks again. -Brian

Hmmm... something's still not quite right. On the first query, Access is prompting me for a parameter called Subcategory.ID

Hi, Jonfer--yes, that's it! I'm not at my PC right now, but that query looks right. Thanks SO tremendously much to all of you, especially you Jonfer! Hopefully this will put me on the road again. Thanks, guys. -Brian

Hi, PHV. That actually gives the same result as lespaul's select statement. It's probably my fault that I wasn't clear about needing all the FIELDNAME/FIELDVALUE combinations for a specific category. Can you guys come up with a query to do that? If it's something that's just not possible, I...

Hi, Leslie. That's actually the SQL statement I've been using, but doesn't quite fix my problem. For example, if you use SubCategory = 1, you get this: FIELDNAME VALUE Phone 502-555-MARK Email mark@bellsouth.net But I'd like to return all the values under that category, even...

Hello. Please bear with me. This is a little complex and I'm pulling my hair out. I hope you can help (not pull my hair out, but with this problem.:) I have 4 tables that look like this: CATEGORIES ID Category 1 Contacts 2 Credit Cards 3 Work Related ... SUBCATEGORIES ID SubCategory...