Query and Chart Questions

[alphaomega3]

I have a query that lists a supplier name. I also have a table of suppliers and I only want the query to pull only the suppliers that are in that table. We are allowed to type in outside suppliers but the the list is only internal departments that supplied defective material to the next department.

Also I have parameters on this query that are Startdate and Enddate and i want these to be listed on the chart that is based on this query. How do I do this? Can it be done? Basically I want the date range the report is called for on the report.

[nico5038]

JOIN the query that lists a supplier name by the supplier name to the table of suppliers. This will do the selection.
Next use:

select [StartDate: ] as StartDate, [EndDate: ] as EndDate, .... from tblX where supplierdate between [StartDate: ] and [EndDate: ]

Getting the idea ?

Nic;o)

[NeoPa]

While Nico mentioned linking the query to the table I think he may have forgotten this bit in his SQL.
Also, remember we don't have all the information so you will have to fill in the gaps and change any wrong fieldnames that we had to guess.
Try :

SELECT [StartDate: ] as StartDate,
       [EndDate: ] as EndDate,
       qryX.*,
       tblSupplier.*
FROM qryX INNER JOIN tblSupplier
  ON qryX.SupplierID=tblSupplier.SupplierID
WHERE qryX.SupplierDate Between [StartDate: ] And [EndDate: ]

[alphaomega3]

huh? what is supplierdate?

[nico5038]

You stated:
>I have parameters on this query that are Startdate and Enddate
That implies that there is a date you want to select. As you didn't post the name, we assumed it's called "supplierdate", but you'll have to use the real name to make it work.

Nic;o)

[NeoPa]

huh? what is supplierdate?

The posting guidelines for the Access fourum (POSTING GUIDELINES: Please read carefully before posting to a forum) instruct you to :

Give as much detail as possible - When you post a question or problem, express the situation clearly and concisely and include all relevant information (code used; data used; data expected; etc).

You failed to do this but Nico answered your question as well as possible in the circumstances, with the poor information available to him.
I'm sure if you post properly formatted questions in future, that the answers will be more to your liking.

MODERATOR

[alphaomega3]

Okay sorry for my last post not conforming to the posting guidelines. I am still very confused as to what I am doing in this and sorry for it taking me so long to get back to this project but I have been off on other projects.

This is what I have, and yes I recognize now that I should have named my field DATE as something else but unfortunately I didn't know what I was doing when I created the initial table... I am so confused as to what an inner join does. but I just want the suppliers from the tblSupplier.

PARAMETERS Startdate DateTime, Enddate DateTime;
SELECT tblMaster.rptNumber, tblMaster.suppName, tblMaster.Date, tblMaster.Nonconformance, tblMaster.Audit, tblMaster.PA
FROM tblMaster
WHERE (((tblMaster.Date) Between [Startdate] And [Enddate]) AND ((tblMaster.Audit)=False) AND ((tblMaster.PA)=False));

[NeoPa]

I am working on a Tutorial to explain the SQL JOINs.
In the mean time, can you post how the tblMaster is related to tblSupplier so that we can come up with something for you.

[NeoPa]

Check out this new Tutorial (SQL JOINs) for a full explanation of the Joins in SQL.

[alphaomega3]

Okay, getting back to this. I have been out a few days ill. Okay this project is a Non-conformance Reporting (NCR) system for my company. A department issues a NCR against their internal supplier if the internal supplier ( another department) has issued bad or non-conforming parts to them. I need to be able to analyze this data to produce pareto charts to show which internal department has the most non-conformances. This will allow the Quality department the ability to focus on problem areas. However, the QA dept also uses this database to record data for external suppliers.

When the operator opens the database they enter the information into a form that stores all of its data in tblMaster. At the supplier entry the operator has a drop down listing the suppliers. The QA dept types in their external suppliers, without the assistance of a drop down as they have enough expertise to do this on their own without assistance from a drop down.

The query that I currently have as listed above in my previous post pulls out all of the suppliers and I need to limit this to ONLY the suppliers listed in tblSuppliers. We dont want to see the external suppliers listed on this. I do want to in the future be able to pull only those not listed in tblSuppliers. A chart is based on this query to give me the pareto chart and gives a visual report of the count of non-conformances from a particular suppplier.

Any help you can offer would be greatly appreciated. I am reading your tutorial, but am still confused as to how to do this.

Thanks,
Alpha

[alphaomega3]

Okay, I think I got it!! Could somebody look this over and make sure for me for syntax?

PARAMETERS Startdate DateTime, Enddate DateTime;
SELECT tblMaster.rptNumber, tblMaster.suppName, tblMaster.Date, tblMaster.Nonconformance, tblMaster.Audit, tblMaster.PA
FROM tblMaster INNER JOIN tblSupplier ON tblMaster.suppName=tblSupplier.Supplier
WHERE (((tblMaster.Date) Between [Startdate] And [Enddate]) AND ((tblMaster.Audit)=False) AND ((tblMaster.PA)=False));

[NeoPa]

Will do :)

[NeoPa]

Well, it's hard to be certain without your table MetaData, but I'd be surprised if you want to link the tables on the SuppName field.

((tblMaster.Audit)=False) AND ((tblMaster.PA)=False));

should be

(Not tblMaster.Audit) AND (Not tblMaster.PA));

Try it with changes made to cover these comments.

[alphaomega3]

It works!!!

Now how do I get a seperate query for the Suppliers that are not listed in tblSuppliers????

[NeoPa]

Let me get on to that when I get back home this evening.
In the mean time, can you post your current SQL with all your latest amendments. I still don't have all the details I need to duplicate it yet.

[alphaomega3]

I have it as this:

PARAMETERS Startdate DateTime, Enddate DateTime;
SELECT tblMaster.rptNumber, tblMaster.suppName, tblMaster.Date, tblMaster.Nonconformance, tblMaster.Audit, tblMaster.PA
FROM tblMaster INNER JOIN tblSupplier ON tblMaster.suppName=tblSupplier.Supplier
WHERE (((tblMaster.Date) Between [Startdate] And [Enddate]) AND ((tblMaster.Audit)=False) AND ((tblMaster.PA)=False)); 

tblMaster.Audit and tblMaster.PA are check boxes so I only want to pull them in where the checkboxes are not checked. If they are checked they mean something else to the database.

[NeoPa]

This SQL looks exactly the same as the one you posted before (post #11).
Was there any point in my posting the comments?
As this is the same AND you haven't explained why AND you haven't posted any MetaData so that I can check for myself, I'm running a bit blind here.
Conceptually, you need to change the JOIN to a LEFT JOIN and check that the linked field is Null but I can't post any SQL that I have confidence in because ...

[alphaomega3]

Okay you are using terminology I am unfamiliar with. I do not understand what Metadata is...... or where to find it. Would I not want the checkbox info to be =False if I dont want it to show up cause if it is checked it means that an Audit has been performed or that a Preventive action has been dispositioned, which is something other than an NCR.

[NeoPa]

Okay you are using terminology I am unfamiliar with. I do not understand what Metadata is...... or where to find it. Would I not want the checkbox info to be =False if I dont want it to show up cause if it is checked it means that an Audit has been performed or that a Preventive action has been dispositioned, which is something other than an NCR.

Sorry about the MetaData thing. I was in a screaming rush to get away from work when I posted. What I would normally have included to accompany that would be this example :

Table Name=tblStudent
 Field; Type; IndexInfo
StudentID; Autonumber; PK
Family; String; FK
Name; String
University; String; FK
Mark; Numeric
LastAttendance; Date/Time

As far as checking the value of a CheckBox goes, the answer is No.
Like any Boolean type field, the value in the field is actually the result of a comparison. This means it can take the place of the whole comparison in a check.
Consider this :

chkStored = (4 = 4)

==> If 4 = 4 Then... is equivalent to If chkStored Then...
If chkStored is a TRUE value then it will carry out the required action. If not then it won't.
If chkStored = True Then... is equivalent to If (4 = 4) = True Then...
Furthermore, as any numeric value which is not 0 is considered to be TRUE. This will not be treated correctly if compared to the value True (False = 0; True = -1).

chkStored = 5
If chkStored Then MsgBox 1
If chkStored = True Then MsgBox 2

Would only show 1.

[NeoPa]

If you could post MetaData for tblMaster & tblSupplier then I think we can put this to bed :)

[alphaomega3]

I will post the metadata on Monday.... WAY too many deadlines due today...AND More keeps piling up...UGH gotta love Friday afternoon rushes!!

[NeoPa]

No problem. That gives me a bit of a break too :)

[alphaomega3]

I am actually a bit embarrassed to post this here.... I am sure you will look at this and go GEEZ this guy is nuts, but anyway here it is:

Table Name=tblMaster
Field; Type; Indexinfo
rptNumber; Autonumber; PK
ptNumber; Text
Revision; Text
ptDesc; Text
suppName; Text
Originator; Text
audtype; Text
rptDate; Date/Time
poNumber; Text
soNumber; Text
woNumber; Text
QTy; Number
sampSize; Number
Qacc; Number
Qrej; Number
Nonconformance; Text
Specification; Text
Finding; Text
Comments; Text
RTV; Yes/No
rtvqty; Number
Useasis; Yes/No
useasisqty; Number
Rework; Yes/No
rwkqty; Number
Scrap; Yes/No
scrapqty; Number
Audit;Yes/No
CA; Yes/NO
PA; Yes/No
Auditreq; Yes/No
Major; Yes/no
Minor; Yes/no
Observation; Yes/No
Eng; Text
QA; Text
Authorizedby; Text
Assignedto; Text
AssignDate; Date/Time
DueDate; Date/Time
Results; Memo
CAPAtaken; Memo
PA2; Yes/No
CA2; Yes/no
ANSBY; Text
ansdate; Date/Time
QAApp; Text
Reviewby; Text
ReviewDate; Date/Time
clyes;Yes/No
clno;Yes/NO
 
Table Name=tblSupplier
Field; Type; Indexinfo
Supplier; Text

Don't laugh too hard at how long this is and how it could have been broken up and if I need scolding then do it...

[NeoPa]

Well...At least tblSupplier doesn't look too complicated ;)
Will get to this when I get home tonight.

[NeoPa]

Now I understand why you have the strange tblSupplier link ;)
Try this SQL and tell me if it works for you :

PARAMETERS Startdate DateTime,
           Enddate DateTime;
SELECT M.rptNumber,
       M.suppName, 
       M.Date, 
       M.Nonconformance,
       M.Audit,
       M.PA
FROM tblMaster AS M LEFT JOIN tblSupplier AS S
  ON M.suppName=S.Supplier
WHERE ((M.Date Between [Startdate] And [Enddate])
  AND (Not M.Audit)
  AND (Not M.PA)
  AND (S.Supplier Is Null))

This is working on the basis that only unmatched suppliers are required and that the Audit and PA fields are still both FALSE.
You can have the layout as you need - I have it laid out like this simply for displaying easily in the forums.

[alphaomega3]

Thanks That did it!!!!!!!!!!

Thanks for putting up with me, you have truly been very patient!

[NeoPa]

You're not the first member I've had to tell about this - It's against site rules to sound surprised when my suggestions actually work.

Seriously, that's fine. Someone telling me I'm patient is one for the scrapbook though :D