Yes/No Checkbox Select Query Criteria!~

[Elvis72]

Need a tiny bit of help with a select query where if the yes/no checkbox is true in PNP or true in BNB they will be listed.

Here is the code:

SELECT [PRP Table].[PNP or BNB], [PRP Table].[Meeting Date], [Process Proposals].Business_Sector, [Process Proposals].[MSCRM NUM], [Process Proposals].[Cost Code FY 05], [Process Proposals].[FY 05 BUnit], [Process Proposals].CLIENT, [Process Proposals].DESCRIPTION, [Process Proposals].[PROPOSAL MANAGER], [Process Proposals].Proposal_Office, [Process Proposals].Contract_Type, [PRP Table].[Original Signed Copy PRP for SVP Ppls and Commercial Analysis], [PRP Table].[Original Signed PRP Rev #], [PRP Table].[Original Signed PRP Rev Date], [PRP Table].[Original Signed PRP Signatures Complete], [PRP Table].[Original Signed PRP Signatures Missing], [PRP Table].[Risk Committee PRC Approval Received], [PRP Table].[Copy Signed As Sold CRP and PRP with MRP Retention], [PRP Table].[Copy Signed As Sold Rev #], [PRP Table].[Copy Signed As Sold Rev Date], [PRP Table].[Opportunity Outcome], [PRP Table].Price, [PRP Table].Risk, [PRP Table].[Is Price >$1MM], [PRP Table].Status, [BNB Table].[Budget Approved], [PNP Table].[Budget Approved]
FROM (([Process Proposals] INNER JOIN [PRP Table] ON [Process Proposals].[Cost Code FY 05]=[PRP Table].[Cost Code]) INNER JOIN [BNB Table] ON [Process Proposals].[Cost Code FY 05]=[BNB Table].[Cost Code]) INNER JOIN [PNP Table] ON [Process Proposals].[Cost Code FY 05]=[PNP Table].[Cost Code]
WHERE ((([BNB Table].[Budget Approved])=True)) OR ((([PNP Table].[Budget Approved])=True));


Thanks!~

 

[dhookom]

What "tiny bit of help" are you looking for? Your WHERE clause looks to be compatible with your requirements. What is your issue?

Duane MS Access MVP
Now help me support United Cerebral Palsy

 

[Elvis72]

Duane -

The issue is its not bringing up any records that have the yes/no box checked?

I posted the code because I can't figure out what I have done to make it not work?

thanks!~

 

[Elvis72]

~Update~

If I take the PRP Table, Process Table and BNB Table and specify the = true statement it comes up fine.

If I take the PRP Table, Process Table and PNP Table and specify the = true statement it comes up fine.

What I need to do is have them in the same query to have it only list the latest entry for that cost code number.

When I take the above two queries and bring them together they are only showing the BNB information no PNP information.

Any questions please ask, I don't know how clear I am being.

Thanks!~

 

[dhookom]

If you remove the criteria do you return any records?
Where did "list the latest entry for that cost code number" come from since I don't see anything in the SQL that would specify a single record or sorting.


Duane MS Access MVP
Now help me support United Cerebral Palsy

 

[Elvis72]

Duane -

I tend to work systematically, if I can get this to work then I got to the next criteria...

If I leave the original query with the Process Proposal Table, the PRP Query, the BNB Table and the PNP table. And remove the criteria then all I see is BNB information no PNP information.

The "list the latest entry for that cost code number" comes after I figure out why the simple part isn't working.

I have done this in the past I'm sure I'm just forgetting something but for the life of me I can't figure out what..?

 

[Elvis72]

Officially I think I have lost my brain...

OK, to clarify a little more

I have a PNP Table and a BNB Table, then there is a Cost Code that is the linking the two. But in reality a Cost Code can have multiple PNP's and BNB's or a PNP with no BNB or a BNB with no PNP.

I'm thinking doing a select query is never going to give me what I'm wanting which is a list of all the BNBs and PNPs that are approved....

So, if that is not possible how?

 

[dhookom]

I think you may be looking for a union query. I don't have a clue how your records link or what you are storing or the relationships. Perhaps a few sample records or explanation of the tables would help us understand.

Duane MS Access MVP
Now help me support United Cerebral Palsy

 

[Elvis72]

Duane -

I figured out the union query with a simple:

SELECT * FROM somewhere
UNION ALL
SELECT * FROM somewhere

BUT can I use the union query to select the latest record by meeting day and cost code and only list those?

Or am I diving into the programming ocean and I'm going to get lost?

 

[GingerR]

As Duane asked, how about some sample records from each table and what you wish your end result to be....

Want to get great answers to your Tek-Tips questions? Have a look at FAQ219-2884 or FAQ222-2244. Basics at

http://r937.com/relational.html

 

[Elvis72]

On the spreadsheet there are two entries for one cost code, all I need to do is have the latest Meeting date show for each cost code.

Cost Code FY 05 PNP or BNB Meeting Date Business_Sector FY 05 BUnit Opportunity Outcome Price Risk Status
99998888 PNP 15-Dec-07 TC 308113085 Medium Pending
21107 BNB 01-Jan-08 EY 308113085
99998888 BNB 14-Dec-08 TC 308113085 Medium Pending


Regardless if its a BNB or PNP.

 

[GingerR]

SELECT CostCode, Max(MeetingDate) AS MaxOfMeetingDate
FROM tblX
GROUP BY CostCode;

Want to get great answers to your Tek-Tips questions? Have a look at FAQ219-2884 or FAQ222-2244. Basics at

http://r937.com/relational.html

 

[Elvis72]

Ginger -

Your Code Listed above goes in the report or query?

 

[GingerR]

Either one.

Want to get great answers to your Tek-Tips questions? Have a look at FAQ219-2884 or FAQ222-2244. Basics at

http://r937.com/relational.html

 

[Elvis72]

Ginger - I put this into a query:

SELECT [Test Union].[Cost Code], [Test Union].[Meeting Date] AS MaxOfMeetingDate
FROM [Test Union]
ORDER BY [Test Union].[Cost Code];

And I still see the extra records?

 

[GingerR]

You didn't put in what I wrote.

GROUP BY not ORDERBY.

Want to get great answers to your Tek-Tips questions? Have a look at FAQ219-2884 or FAQ222-2244. Basics at

http://r937.com/relational.html

 

[GingerR]

SELECT [Cost Code], Max([Meeting Date]) AS MaxOfMeetingDate
FROM [Test Union] GROUP BY [Cost Code];

Want to get great answers to your Tek-Tips questions? Have a look at FAQ219-2884 or FAQ222-2244. Basics at

http://r937.com/relational.html