report pulling ID instead of description

[epardo87]

Hello All,

I've created some queries and at the end a report, it groups the results first per Name and then by per Day. the problem is that I have a table for names and another for days,

when I create the queries, they show the text correctly, but now that I've created the report, it shows the ID number of the name and the ID number of the Day... even when the query used to create that report shows the text... how could I make the report show the text instead of the ID numbers?



thanks for your time

[pbaldy]

Sounds like you've used a lookup field, which most of us won't:

http://www.theaccessweb.com/lookupfields.htm

You can base the report on a query that joins the tables together, enabling you to return the text field.

[epardo87]

Sounds like you've used a lookup field, which most of us won't:

http://www.theaccessweb.com/lookupfields.htm

You can base the report on a query that joins the tables together, enabling you to return the text field.

thanks! yeah i know about it... I tried that, but when I link the original table with the final query either it doesn't return anything or it says Type Mismatch in expression

this is the SQL as it is right now:

SELECT [Qry-NutrPerDay-Detail].Paciente, [Qry-NutrPerDay-Detail].Día, [Qry-NutrPerDay-Detail].Nutr_No, [Qry-NutrPerDay-Detail].NutrDesc, Sum([Qry-NutrPerDay-Detail].[N utr_PP]) AS [SumOfN utr_PP], [Qry-NutrPerDay-Detail].UnitsFROM [Qry-NutrPerDay-Detail]
GROUP BY [Qry-NutrPerDay-Detail].Paciente, [Qry-NutrPerDay-Detail].Día, [Qry-NutrPerDay-Detail].Nutr_No, [Qry-NutrPerDay-Detail].NutrDesc, [Qry-NutrPerDay-Detail].Units;

when I try to link them and reads mismatch, this is the SQL:

SELECT [Qry-NutrPerDay-Detail].Paciente, [Qry-NutrPerDay-Detail].Día, [Qry-NutrPerDay-Detail].Nutr_No, [Qry-NutrPerDay-Detail].NutrDesc, Sum([Qry-NutrPerDay-Detail].[N utr_PP]) AS [SumOfN utr_PP], [Qry-NutrPerDay-Detail].Units, [tbl-Names].NombreFROM [Qry-NutrPerDay-Detail] INNER JOIN [tbl-Names] ON [Qry-NutrPerDay-Detail].Paciente = [tbl-Names].ID
GROUP BY [Qry-NutrPerDay-Detail].Paciente, [Qry-NutrPerDay-Detail].Día, [Qry-NutrPerDay-Detail].Nutr_No, [Qry-NutrPerDay-Detail].NutrDesc, [Qry-NutrPerDay-Detail].Units, [tbl-Names].Nombre;

[John_G]

I'm only guessing, but the error may be here:

[Qry-NutrPerDay-Detail].Paciente = [tbl-Names].ID

Both Paciente and ID have to be the same type. ID probably is numeric, but is Paciente also numeric?

[orange]

Here is the SQL reformatted for readability

SELECT [Qry-NutrPerDay-Detail].Paciente
	,[Qry-NutrPerDay-Detail].Día
	,[Qry-NutrPerDay-Detail].Nutr_No
	,[Qry-NutrPerDay-Detail].NutrDesc
	,Sum([Qry-NutrPerDay-Detail].[N utr_PP]) AS [SumOfN utr_PP]
	,[Qry-NutrPerDay-Detail].Units
	,[tbl-Names].NombreFROM [Qry-NutrPerDay-Detail]
INNER JOIN [tbl-Names] ON [Qry-NutrPerDay-Detail].Paciente = [tbl-Names].ID
GROUP BY [Qry-NutrPerDay-Detail].Paciente
	,[Qry-NutrPerDay-Detail].Día
	,[Qry-NutrPerDay-Detail].Nutr_No
	,[Qry-NutrPerDay-Detail].NutrDesc
	,[Qry-NutrPerDay-Detail].Units
	,[tbl-Names].Nombre;

[ssanfu]

@epardo87:
"Day" ("Dia") is a reserved word (and a built in function) in Access and shouldn't be used as an object name.
"Name" ("Nombre") is a reserved word in Access and shouldn't be used as an object name.


Suggestions:
Use only letters and numbers (exception is the underscore) for object names. (Use the underscore instead if the dash)
Do not use spaces, punctuation or special characters in object names.
Do not use look up FIELDS, multi-value fields or calculated fields in tables.