Fields showing the ID and not the actual record name.....

  • Thread starter Thread starter Guest
  • Start date Start date
G

Guest

Hi guys, I have a few tables, one called Denominations and one called
Products...

They're classic table structures, i.e.

Denom_ID Denoms
1 Each
2 Job Lot
3 Per Kilo

Product_ID Products
1 Belts
2 Ties
3 Scarves

etc etc.

I then have a table for orders which lists the details of an order referring
to their denomination and product types. However whenever I refer to the
denom or product type in my query it shows the actual code and not the name,
instead of showing Each, the query shows a 1.

How do I make my query show the actual name I'm referring to.
 
Hi Lynn and thanks for your reply. I was actually talking hypothetically in
that last post but now I Will be more specific:

This is my query as things stand:

SELECT Purchase_Orders.[Job No], Purchase_Orders.[PO Number] AS
[Purchase_Orders_PO Number], Purchase_Orders.Date, Purchase_Orders.Suppliers,
Purchase_Orders.Dept, Purchase_Orders_Details.[Item No],
Purchase_Orders_Details.Product, Purchase_Orders_Details.Description,
Purchase_Orders_Details.TypeColourSize, Purchase_Orders_Details.Quantity,
Purchase_Orders_Details.Denom, Purchase_Orders_Details.Price
FROM Purchase_Orders INNER JOIN Purchase_Orders_Details ON
Purchase_Orders.[PO Number] = Purchase_Orders_Details.[PO Number]
WHERE (((Purchase_Orders.[Job No]) Like "*" & [cboJobNo]));

My two other tables are as follows:

Table Name: Denominations
Fields: denomid
Denom


Table Name: Products
Fields Id
Product

I also have two other tables - Purchase_Orders and Purchase_Order_Details

Purchase_Order_Details has fields called Product and Denom.

I've tried to adjust my own query to use the inner joins as per your code
but can't get the syntax correct.... I just can't seem to get my head around
joins..

Any chance you could adjust based on the above? Thank you.
 
Obviously, this is UNTESTED, but you need to add the Denominations table and
the Products table to your query.


SELECT Purchase_Orders.[Job No], Purchase_Orders.[PO Number] AS
[Purchase_Orders_PO Number], Purchase_Orders.Date,
Purchase_Orders.Suppliers,
Purchase_Orders.Dept, Purchase_Orders_Details.[Item No],
Products.Product, Purchase_Orders_Details.Description,
Purchase_Orders_Details.TypeColourSize, Purchase_Orders_Details.Quantity,
Denominations.Denom, Purchase_Orders_Details.Price
FROM (Purchase_Orders Join Denominations ON Denominations.Denom_id =
Purchase_orders.Denom_id) Join (Purchase_orders Join Products ON
Products.Product_id = Purchase_Orders.Product_id) Join Purchase_Orders INNER
JOIN Purchase_Orders_Details ON
Purchase_Orders.[PO Number] = Purchase_Orders_Details.[PO Number]
WHERE (((Purchase_Orders.[Job No]) Like "*" & [cboJobNo]));


--
Lynn Trapp
MS Access MVP
www.ltcomputerdesigns.com
Access Security: www.ltcomputerdesigns.com/Security.htm
Jeff Conrad's Access Junkie List:
http://home.bendbroadband.com/conradsystems/accessjunkie.html



Richard Horne said:
Hi Lynn and thanks for your reply. I was actually talking hypothetically
in
that last post but now I Will be more specific:

This is my query as things stand:

SELECT Purchase_Orders.[Job No], Purchase_Orders.[PO Number] AS
[Purchase_Orders_PO Number], Purchase_Orders.Date,
Purchase_Orders.Suppliers,
Purchase_Orders.Dept, Purchase_Orders_Details.[Item No],
Purchase_Orders_Details.Product, Purchase_Orders_Details.Description,
Purchase_Orders_Details.TypeColourSize, Purchase_Orders_Details.Quantity,
Purchase_Orders_Details.Denom, Purchase_Orders_Details.Price
FROM Purchase_Orders INNER JOIN Purchase_Orders_Details ON
Purchase_Orders.[PO Number] = Purchase_Orders_Details.[PO Number]
WHERE (((Purchase_Orders.[Job No]) Like "*" & [cboJobNo]));

My two other tables are as follows:

Table Name: Denominations
Fields: denomid
Denom


Table Name: Products
Fields Id
Product

I also have two other tables - Purchase_Orders and Purchase_Order_Details

Purchase_Order_Details has fields called Product and Denom.

I've tried to adjust my own query to use the inner joins as per your code
but can't get the syntax correct.... I just can't seem to get my head
around
joins..

Any chance you could adjust based on the above? Thank you.

Lynn Trapp said:
Give this a try.

SELECT Orders.Order_Number, Products.Products, Denominations.Denoms
FROM (Orders INNER JOIN Denominations ON Orders.Denom_id =
Denominations.Denom_ID) INNER JOIN Products ON Orders.Product_id =
Products.Product_ID;



--
Lynn Trapp
MS Access MVP
www.ltcomputerdesigns.com
Access Security: www.ltcomputerdesigns.com/Security.htm
Jeff Conrad's Access Junkie List:
http://home.bendbroadband.com/conradsystems/accessjunkie.html
Click to expand...
Click to expand...
 
Hi Lynn I've just tested your code and I get a 'Syntax error in Join
operation error message'

I'll see if I can get the query working with the Denominations and Products
code in the meantime......

Lynn Trapp said:
Obviously, this is UNTESTED, but you need to add the Denominations table and
the Products table to your query.


SELECT Purchase_Orders.[Job No], Purchase_Orders.[PO Number] AS
[Purchase_Orders_PO Number], Purchase_Orders.Date,
Purchase_Orders.Suppliers,
Purchase_Orders.Dept, Purchase_Orders_Details.[Item No],
Products.Product, Purchase_Orders_Details.Description,
Purchase_Orders_Details.TypeColourSize, Purchase_Orders_Details.Quantity,
Denominations.Denom, Purchase_Orders_Details.Price
FROM (Purchase_Orders Join Denominations ON Denominations.Denom_id =
Purchase_orders.Denom_id) Join (Purchase_orders Join Products ON
Products.Product_id = Purchase_Orders.Product_id) Join Purchase_Orders INNER
JOIN Purchase_Orders_Details ON
Purchase_Orders.[PO Number] = Purchase_Orders_Details.[PO Number]
WHERE (((Purchase_Orders.[Job No]) Like "*" & [cboJobNo]));


--
Lynn Trapp
MS Access MVP
www.ltcomputerdesigns.com
Access Security: www.ltcomputerdesigns.com/Security.htm
Jeff Conrad's Access Junkie List:
http://home.bendbroadband.com/conradsystems/accessjunkie.html



Richard Horne said:
Hi Lynn and thanks for your reply. I was actually talking hypothetically
in
that last post but now I Will be more specific:

This is my query as things stand:

SELECT Purchase_Orders.[Job No], Purchase_Orders.[PO Number] AS
[Purchase_Orders_PO Number], Purchase_Orders.Date,
Purchase_Orders.Suppliers,
Purchase_Orders.Dept, Purchase_Orders_Details.[Item No],
Purchase_Orders_Details.Product, Purchase_Orders_Details.Description,
Purchase_Orders_Details.TypeColourSize, Purchase_Orders_Details.Quantity,
Purchase_Orders_Details.Denom, Purchase_Orders_Details.Price
FROM Purchase_Orders INNER JOIN Purchase_Orders_Details ON
Purchase_Orders.[PO Number] = Purchase_Orders_Details.[PO Number]
WHERE (((Purchase_Orders.[Job No]) Like "*" & [cboJobNo]));

My two other tables are as follows:

Table Name: Denominations
Fields: denomid
Denom


Table Name: Products
Fields Id
Product

I also have two other tables - Purchase_Orders and Purchase_Order_Details

Purchase_Order_Details has fields called Product and Denom.

I've tried to adjust my own query to use the inner joins as per your code
but can't get the syntax correct.... I just can't seem to get my head
around
joins..

Any chance you could adjust based on the above? Thank you.

Lynn Trapp said:
Give this a try.

SELECT Orders.Order_Number, Products.Products, Denominations.Denoms
FROM (Orders INNER JOIN Denominations ON Orders.Denom_id =
Denominations.Denom_ID) INNER JOIN Products ON Orders.Product_id =
Products.Product_ID;



--
Lynn Trapp
MS Access MVP
www.ltcomputerdesigns.com
Access Security: www.ltcomputerdesigns.com/Security.htm
Jeff Conrad's Access Junkie List:
http://home.bendbroadband.com/conradsystems/accessjunkie.html



Hi guys, I have a few tables, one called Denominations and one called
Products...

They're classic table structures, i.e.

Denom_ID Denoms
1 Each
2 Job Lot
3 Per Kilo

Product_ID Products
1 Belts
2 Ties
3 Scarves

etc etc.

I then have a table for orders which lists the details of an order
referring
to their denomination and product types. However whenever I refer to
the
denom or product type in my query it shows the actual code and not the
name,
instead of showing Each, the query shows a 1.

How do I make my query show the actual name I'm referring to.
Click to expand...
Click to expand...
Click to expand...
 
It's not easy to see what the problem might be without actually seeing the
database. Let me suggest that you try to build the query in the QBE window.
It will generate the SQL for you and you can, then, tweak it as needed.

--
Lynn Trapp
MS Access MVP
www.ltcomputerdesigns.com
Access Security: www.ltcomputerdesigns.com/Security.htm
Jeff Conrad's Access Junkie List:
http://home.bendbroadband.com/conradsystems/accessjunkie.html



Richard Horne said:
Hi Lynn I've just tested your code and I get a 'Syntax error in Join
operation error message'

I'll see if I can get the query working with the Denominations and
Products
code in the meantime......

Lynn Trapp said:
Obviously, this is UNTESTED, but you need to add the Denominations table
and
the Products table to your query.


SELECT Purchase_Orders.[Job No], Purchase_Orders.[PO Number] AS
[Purchase_Orders_PO Number], Purchase_Orders.Date,
Purchase_Orders.Suppliers,
Purchase_Orders.Dept, Purchase_Orders_Details.[Item No],
Products.Product, Purchase_Orders_Details.Description,
Purchase_Orders_Details.TypeColourSize, Purchase_Orders_Details.Quantity,
Denominations.Denom, Purchase_Orders_Details.Price
FROM (Purchase_Orders Join Denominations ON Denominations.Denom_id =
Purchase_orders.Denom_id) Join (Purchase_orders Join Products ON
Products.Product_id = Purchase_Orders.Product_id) Join Purchase_Orders
INNER
JOIN Purchase_Orders_Details ON
Purchase_Orders.[PO Number] = Purchase_Orders_Details.[PO Number]
WHERE (((Purchase_Orders.[Job No]) Like "*" & [cboJobNo]));


--
Lynn Trapp
MS Access MVP
www.ltcomputerdesigns.com
Access Security: www.ltcomputerdesigns.com/Security.htm
Jeff Conrad's Access Junkie List:
http://home.bendbroadband.com/conradsystems/accessjunkie.html



Richard Horne said:
Hi Lynn and thanks for your reply. I was actually talking
hypothetically
in
that last post but now I Will be more specific:

This is my query as things stand:

SELECT Purchase_Orders.[Job No], Purchase_Orders.[PO Number] AS
[Purchase_Orders_PO Number], Purchase_Orders.Date,
Purchase_Orders.Suppliers,
Purchase_Orders.Dept, Purchase_Orders_Details.[Item No],
Purchase_Orders_Details.Product, Purchase_Orders_Details.Description,
Purchase_Orders_Details.TypeColourSize,
Purchase_Orders_Details.Quantity,
Purchase_Orders_Details.Denom, Purchase_Orders_Details.Price
FROM Purchase_Orders INNER JOIN Purchase_Orders_Details ON
Purchase_Orders.[PO Number] = Purchase_Orders_Details.[PO Number]
WHERE (((Purchase_Orders.[Job No]) Like "*" & [cboJobNo]));

My two other tables are as follows:

Table Name: Denominations
Fields: denomid
Denom


Table Name: Products
Fields Id
Product

I also have two other tables - Purchase_Orders and
Purchase_Order_Details

Purchase_Order_Details has fields called Product and Denom.

I've tried to adjust my own query to use the inner joins as per your
code
but can't get the syntax correct.... I just can't seem to get my head
around
joins..

Any chance you could adjust based on the above? Thank you.

:

Give this a try.

SELECT Orders.Order_Number, Products.Products, Denominations.Denoms
FROM (Orders INNER JOIN Denominations ON Orders.Denom_id =
Denominations.Denom_ID) INNER JOIN Products ON Orders.Product_id =
Products.Product_ID;



--
Lynn Trapp
MS Access MVP
www.ltcomputerdesigns.com
Access Security: www.ltcomputerdesigns.com/Security.htm
Jeff Conrad's Access Junkie List:
http://home.bendbroadband.com/conradsystems/accessjunkie.html



message
Hi guys, I have a few tables, one called Denominations and one
called
Products...

They're classic table structures, i.e.

Denom_ID Denoms
1 Each
2 Job Lot
3 Per Kilo

Product_ID Products
1 Belts
2 Ties
3 Scarves

etc etc.

I then have a table for orders which lists the details of an order
referring
to their denomination and product types. However whenever I refer to
the
denom or product type in my query it shows the actual code and not
the
name,
instead of showing Each, the query shows a 1.

How do I make my query show the actual name I'm referring to.
Click to expand...
Click to expand...
Click to expand...
 
Back
Top