Table shows the wrong field from the combo box

[Guest]

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.

 

[tina]

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.

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.

 

[Guest]

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
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.

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.

 

[tina]

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
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
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.

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.

 

[Guest]

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
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
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
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.

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.

 

[tina]

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
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
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
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

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.

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.

 

[Guest]

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
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
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
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
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
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.

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.

 

[tina]

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!

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
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

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

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

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
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.

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.

 

[Guest]

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
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!

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
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
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
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
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
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.

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.