Names needed not id# in report

  • Thread starter Thread starter Ron
  • Start date Start date
R

Ron

db has several main tables with id# fields that relate to
customer etc names in sub tables. Union query joins the
main tables for searching, & select query uses union query
to display customer names to user not id#, as list box set
to 2 columns but 2nd column of id#s = 0 width. Ditto for
data input form. But report of select query results shows
id#s not customer names!
Help please
 
Ron

You'll need to give us a look at the SQL statement used as the source for
your report.

More info, please...

(By the way, your ID# column is your ?!second column? Usually, the bound
column is the ID# column, is always the first column, and its width is set
to 0 -- or am I missing something?)

Jeff Boyce
<Access MVP>
 
Jeff,
Thanks for your time. My report is based on a simple
select qry by wizard, but it shows names, as I
reformatted fields in qry as listboxes set to 2 columns &
ID# width is 0. I have this query as I don't know how to
set properties of listboxes in SQL & don't know if VBA
appropriate. Your answer may make this qry redundant.

The SQL as requested [Object names edited to make it
easier to follow]
SELECT quniP.[SName ID#] AS Expr1, quniP.[FName ID#] AS
Expr2, quniP.[SName2 ID#] AS Expr3, quniP.[FName2 ID#] AS
Expr4, quniP.[txtB] AS Expr5, qunP.[txtPlace] AS Expr6,
quniP.[PL ID#] AS Expr7, quniP.[txtXX] AS Expr8
FROM quniP;

The union qry the above qry is based on is quniP & has
SQL of:
SELECT [SName ID#],[FName ID#],[SName2 ID#],[FName2 ID#],
[txtB],[txtPlace],[PL ID#],[Src ID#],[txtXX]
FROM [tblMain1]
UNION ALL SELECT [SName ID#],[FName ID#],NULL,[FName2
ID#],[txtB],[txtPlace],[PL ID#],[Src ID#],[txtXX]
FROM [tblMain2]
UNION ALL SELECT [SName ID#],[FName ID#],[SName2 ID#],
[FName2 ID#],[txtB],[txtPlace],[PL ID#],[Src ID#],[txtXX]
FROM [tblMain3];

Yes, id# is 2nd column in all name subtables & that's the
bound column. I didn't know of convention / wisdom to put
id# first. My way I don't have to set column widths, the
name displays as first column.

By the way, I wish to run a form OnLoad of report to
input the underlying qry criteria into txtboxes on the
form, as shown in Access Help.
Thanks again,
Ron
 
Ron

I'm more confused. Queries don't have listboxes. Is there a chance your
underlying tables include "lookup" data type fields? If so, you may be
confused between what is displayed (the "lookup" aspect), and what is
actually being stored in the field (typically an ID#).

Take a look at the underlying table structure for this -- it IS an issue and
you'll see, by a review of the .tablesdbdesign newsgroup, that the "lookup"
data type causes many problems and much confusion.

Good luck

Jeff Boyce
<Access MVP>
 
Jeff,
1) I opened the select qry in Design View with Lookup tab
of Field Property Sheet displayed. For Display Control
property, I selected Listbox, which creates bound column
properties etc like listbox on a form. This is what I
meant by listboxes in query. Have I done this the wrong
way?

2) Main table data is unintelligible #s, needs subtable to
see what related name is to be displayed for each #, &
that's my report problem.
 
Jeff,

Thanks for mentioning problems with lookup type fields in
tables. Reviewing these problems such as sorts on id# not
name, I realise this applies also to my query. So I linked
the name subtables in the query instead of lookup type of
listboxes, the report becomes simple. I didn't realise
combo & list boxes are best on forms, but don't work
consistently in queries [or table fields].

Ron
 
Back
Top