Table shows the wrong field from the combo box

Thread view:

Enable EMail Alerts

Start New Thread

Thread rating:

LyndsyJo - 06 Apr 2006 21:59 GMT

This is my second post, and I'm hoping to get as good of help as I did the
last time, since I still don't really know what I'm doing with Access.

The database I created contains a form with 3 combo boxes: cboMain, cboSub,
and cboDetail. The choice made in cboMain determines what choices are shown
in cboSub, and choice made in cboSub determines what choices are shown in
cboDetail. The available choices in cboMain are based on a table called
tblMainItems; the choices in cboSun are based on a table called tblSubItems,
and the choices in cboDetail are based on a table called tblDetail. The
items in each table each have their own unique ID. [for example,
"Merchandising" has a MainID of 8, and it's sub issues are "Size" (SubID 28)
and "Length" (SubID 29). "Length" then has details of "Petite" (DetailID
45), "Average" (DetailID 46), and "Tall" (DetailID 47).] Hope that makes
sense.

Once I got the help I needed and got these combo boxes working properly (so
that they affected each other properly, loaded blank initially, and the 2nd &
3rd box choices changed if you change the choice in the box above), I found
that after I entered a few records, then went back and looked at them, they
were blank. I couldn't determine why, but the info was being stored in the
main table (tblCustomerIssuesLog), so no big deal. HOWEVER, the info stored
for these fields in tblCustomerIssuesLog shows up as the choice's ID number,
rather than the name. The reason this is a problem is because when I run
reports off this database, I only see the issues' IDs, not the names.

Please let me know how I can do this. If you need other info to determine
what I'm doing wrong, please let me know. Please be gentle and state things
simply - I'm not stupid, but I'm very new to Access and have had no training.

Reply to this Message

tina - 07 Apr 2006 03:20 GMT

sounds like the data is being stored correctly. when you have a numeric ID
as the primary key in a table, and there is a corresponding foreign key in
another table, the data stored in the foreign key field in the other table
will be that numeric ID. that's exactly as it should be.

to see the "names", you need to link the tables in a query, and base the
report on the query. for instance, lets say you have a tblProducts, as

tblProducts
ProdID ProdName
1 Apple
2 Bread
3 Soup
4 Coffee

and a tblProductInventory, as

tblProductInventory
InvID InvDate ProdIDfk InvCount
1 1/1/2006 1 20
2 1/1/2006 2 18
3 1/1/2006 3 27
4 1/1/2006 4 6
5 2/1/2006 1 22
6 2/1/2006 2 10
7 2/1/2006 3 24
8 2/1/2006 4 9

to see the inventory records with the product name, rather than the numeric
IDs, you'd join the tables in a query, as

SELECT tblProductInventory.InvID, tblProductInventory.InvDate,
tblProducts.ProdName, tblProductInventory.InvCount
FROM tblProductInventory LEFT JOIN tblProducts ON
tblProductInventory.ProdIDfk = tblProducts.ProdID;

hth

> This is my second post, and I'm hoping to get as good of help as I did the
> last time, since I still don't really know what I'm doing with Access.
[quoted text clipped - 24 lines]
> what I'm doing wrong, please let me know. Please be gentle and state things
> simply - I'm not stupid, but I'm very new to Access and have had no training.

Reply to this Message

LyndsyJo - 11 Apr 2006 21:38 GMT

I've been playing around with the Query you wrote to try to make it fit my
database, but I just can't seem to make it work. I don't know what the
capitalized commands mean, so it's kind of hard to work around it. The only
times I've written queries before, I just used it for filtering and just
clicked the fields and picked ascending or whatever. Is there a way to write
the query without actualy WRITING it? Or would you (or anyone) be willing to
look at my database?

> sounds like the data is being stored correctly. when you have a numeric ID
> as the primary key in a table, and there is a corresponding foreign key in
[quoted text clipped - 75 lines]
> > simply - I'm not stupid, but I'm very new to Access and have had no
> training.

Reply to this Message

tina - 12 Apr 2006 01:53 GMT

well, this is a simple enough query to build in the query design grid. i
just usually post the SQL because i have a hard time explaining how to do
things in the design view. but i'll have a go, so bear with me and see if
you can figure out these directions:

i'm going to use the table examples that i previously posted; i'll leave it
to you to ring the changes for your own database.
1. open a new query in design view, and add first the table
tblProductInventory, then tblProducts.
2. click on field ProdIDfk in tblProductInventory, drag it over to
tblProducts, and drop it on top of field ProdID. now the two tables are
linked, with an INNER JOIN.
3. from the menu bar, click View | Join Properties. in the dialog box, the
first option is selected: "Only include rows where the joined fields from
both tables are equal."
4. select the second option: "Include ALL records from
'tblProductInventory' and only those records from 'tblProducts' where the
joined fields are equal." then click OK.
5. from the menu bar, click View | SQL View. the FROM clause in the SQL
statement should say "FROM tblProductInventory LEFT JOIN tblProducts ON
tblProductInventory.ProdIDfk = tblProducts.ProdID;"
note: make sure the LEFT JOIN goes *from* tblProductInventory *to*
tblProducts, and not the other way around.

if you still have trouble figuring this out, suggest you build the sample
tables that i posted, then build the query using those tables. that should
help make it clear to you how it works.

hth

> I've been playing around with the Query you wrote to try to make it fit my
> database, but I just can't seem to make it work. I don't know what the
[quoted text clipped - 83 lines]
> > > simply - I'm not stupid, but I'm very new to Access and have had no
> > training.

Reply to this Message

LyndsyJo - 13 Apr 2006 00:00 GMT

OK, I was able to link my first two tables (tblMainItems & tblSubItems) using
this method, but when I tried to then link tblSubItems and tblDetail in the
same manner, I got an error in the SQL statement. Does that mean I just have
to create a separate query?

Also, I don't have an "inventory" type field to pull numbers from; I instead
need to create reports that say how many records were created that contained
each type of issue chosen from the 3 combo boxes based on the tables. How
can I get the report to indicate that information?

Thank you for your help and patience; I really am learning a lot.

> well, this is a simple enough query to build in the query design grid. i
> just usually post the SQL because i have a hard time explaining how to do
[quoted text clipped - 132 lines]
> > > > simply - I'm not stupid, but I'm very new to Access and have had no
> > > training.

Reply to this Message

tina - 14 Apr 2006 01:05 GMT

post your SQL statement, please. to do so, open the query in design view,
from the menu bar click View | SQL View, highlight and copy the *entire* SQL
statement (all text in the window), and paste into a post.

hth

> OK, I was able to link my first two tables (tblMainItems & tblSubItems) using
> this method, but when I tried to then link tblSubItems and tblDetail in the
[quoted text clipped - 144 lines]
> > > > > simply - I'm not stupid, but I'm very new to Access and have had no
> > > > training.

Reply to this Message

LyndsyJo - 14 Apr 2006 16:38 GMT

Thanks so much, tina. I'll have to have my boss put you on the payroll!

I had to rewrite the one where I was linking 3 tables because once I close
it, I couldn't open it back up; I got an error message telling me the query
can't be executed. However, when I rewrote it today, it worked fine. I'm
not sure what I did before. It's been a rough week! :)

So the only thing left is to figure out how to display in a report the
number of records created for each issue. I'm sure there's a way to do it,
but I can't seem to find it.

> post your SQL statement, please. to do so, open the query in design view,
> from the menu bar click View | SQL View, highlight and copy the *entire* SQL
[quoted text clipped - 190 lines]
> no
> > > > > training.

Reply to this Message

tina - 15 Apr 2006 04:29 GMT

well, generally speaking, and keeping it simple - if all you need in the
report are total counts of instances of a category or type, you can create a
Totals query and base the report on that query. if you need see total counts
AND the detail of the records that make up those counts, then write an
ordinary query to pull the records you need and base the report on that
query; in the report, use the Sorting and Grouping options to group the
detail records, and use unbound textbox controls to show the counts. is this
sounds a little greek to you, then you might try using the Reports wizard to
build the report, including providing totals. then look at the report design
view to see how the wizard set up the Sorting and Grouping, and the
calculated controls to show totals.

hth

> Thanks so much, tina. I'll have to have my boss put you on the payroll!
>
[quoted text clipped - 201 lines]
> > no
> > > > > > training.

Reply to this Message

LyndsyJo - 20 Apr 2006 17:16 GMT

I tried this, but instead of counting the number of records with each
different detail, it just counts the details, so I get "1" for each detail.
I don't know what I'm doing wrong.

> well, generally speaking, and keeping it simple - if all you need in the
> report are total counts of instances of a category or type, you can create a
[quoted text clipped - 257 lines]
> > > no
> > > > > > > training.

Reply to this Message