To find out if a Prod Chart Order Number has a record in the Watches table,
you can use a query with a subquery that counts the number of related Watch
records.
SELECT [Prod Chart].[Order Number], [Prod Chart].[LINE ITEMS],
[Prod Chart].[SERVICE ALERT #], [Prod Chart].[CUST CODE],
[Prod Chart].[ORDER TYPE], [Prod Chart].[ORDER QTY],
[Prod Chart].[PO NUMBER], [Prod Chart].[TRACKING #],
[Prod Chart].[ORDER VALUE], [Prod Chart].[ORDER RECD],
[Prod Chart].[DATE TO PROD], [Prod Chart].[SHIP DATE],
[Prod Chart].[MFG DATE], [Prod Chart].[OFFICE STATUS],
[Prod Chart].[FLOOR STATUS], [Prod Chart].[SHIP WITH],
[Prod Chart].MATERIAL, [Prod Chart].TAPE, [Prod Chart].PAINT,
[Prod Chart].[FAST TRACK], [Prod Chart].ALLOCATION,
[Prod Chart].NOTES,
(SELECT Count(*)
FROM [Watches]
WHERE [Watches].[Order Number] = [Prod Chart].[ORDER NUMBER]) As WatchRecs
FROM [Prod Chart];
--
Duane Hookom
Microsoft Access MVP
:
Duane,
TableA is Orders, TableB is Production, and TableC is Watches
The Order table lists all of the information on the order
The Production table lists what is in production
The Watches table lists what has errors on it
What I am trying to do based on these three tables is any order number that
is both on the production and watches tables needs to highlight on the
production report so that everyone is aware there is a problem with it.
Here's the SQL view of the query that didn't work for me:
SELECT Watches.[Order Number], [Prod Chart].[LINE ITEMS], [Prod
Chart].[SERVICE ALERT #], [Prod Chart].[CUST CODE], [Prod Chart].[ORDER
TYPE], [Prod Chart].[ORDER QTY], [Prod Chart].[PO NUMBER], [Prod
Chart].[TRACKING #], [Prod Chart].[ORDER VALUE], [Prod Chart].[ORDER RECD],
[Prod Chart].[DATE TO PROD], [Prod Chart].[SHIP DATE], [Prod Chart].[MFG
DATE], [Prod Chart].[OFFICE STATUS], [Prod Chart].[FLOOR STATUS], [Prod
Chart].[SHIP WITH], [Prod Chart].MATERIAL, [Prod Chart].TAPE, [Prod
Chart].PAINT, [Prod Chart].[FAST TRACK], [Prod Chart].ALLOCATION, [Prod
Chart].NOTES
FROM [Prod Chart] INNER JOIN Watches ON [Prod Chart].[ORDER NUMBER] =
Watches.[Order Number];
Thank you again,
Kristen
:
What are the tables used for? What are you actually storing? Is the an Order
table and an OrderDetails table?
For a particular order number, isn't the customer the same?
What is the SQL view of the query that you tried and didn't return any
records?
--
Duane Hookom
Microsoft Access MVP
:
Duane,
Good morning. Thanks for the quick response. There are too many fields in
each table to list everything here, so I will simply list the main fields.
TableA - ID Number, Customer, Order Number, Etc.
TableB - ID Number(Primary Key), Customer, Order Number, Type, Qty, Etc.
TableC - Order Number, Customer, Date Ordered, Etc.
The only relationship that is set is TableA-Order Number to TableB-Order
Number and TableA-Order Number to TableC-Order Number.
Does this help?
Thank you again,
Kristen
:
You need query assistance. If your tables both have records and the table
have some type of relationship then they should generally return some
records.
You might want to come back to use with a description of your tables and
fields and relationships.
--
Duane Hookom
Microsoft Access MVP
:
Duane,
Thank you for your assistance in this matter. I am not really familiar with
Access, so when I read your response, I went and looked up queries and how to
build one. Now, I have one built and all of the fields show up when I hit the
"run" button; however, nothing shows up in my Datasheet View except for the
titles. Does this mean that it is not working?
Yes, I am using the []s around my tables and fields that have spaces in them.
Thank you again,
:
Are both TableA and Table B in your report's record source query? Are both
fields in the record source?
Since you have spaces and symbols in your object names, are you using []s in
your conditional format expression?
I would probably bind the fields to controls in the report section. Then
using a little code in the On Format event of the section to set the back
color of the section.
--
Duane Hookom
Microsoft Access MVP
:
Hello,
I am trying to get rows in a report to highlight based off of two different
tables. What I need is:
If ((Table A) and (Column/Category A)) are equal to ((Table B) and
(Column/Category B)) Then (the row in the report) Highlighted = True
I have tried to do conditional formatting, but when I do that I get a box
pop-up asking me for the (Column/Category B) information; yet, even when
entered it doesn't highlight the line.
So I was wondering if this is at all possible?
Thanks,
