By using this site, you agree to our updated Privacy Policy and our Terms of Use. Manage your Cookies Settings.
424,491 Members | 858 Online
Bytes IT Community
+ Ask a Question
Need help? Post your question and get tips & solutions from a community of 424,491 IT Pros & Developers. It's quick & easy.

Query and Chart Questions

P: 78
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.
Feb 9 '07 #1
Share this Question
Share on Google+
26 Replies


nico5038
Expert 2.5K+
P: 3,072
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)
Feb 10 '07 #2

NeoPa
Expert Mod 15k+
P: 31,186
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 :
Expand|Select|Wrap|Line Numbers
  1. SELECT [StartDate: ] as StartDate,
  2.        [EndDate: ] as EndDate,
  3.        qryX.*,
  4.        tblSupplier.*
  5. FROM qryX INNER JOIN tblSupplier
  6.   ON qryX.SupplierID=tblSupplier.SupplierID
  7. WHERE qryX.SupplierDate Between [StartDate: ] And [EndDate: ]
Feb 11 '07 #3

P: 78
huh? what is supplierdate?
Feb 13 '07 #4

nico5038
Expert 2.5K+
P: 3,072
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)
Feb 13 '07 #5

NeoPa
Expert Mod 15k+
P: 31,186
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
Feb 13 '07 #6

P: 78
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.

Expand|Select|Wrap|Line Numbers
  1. PARAMETERS Startdate DateTime, Enddate DateTime;
  2. SELECT tblMaster.rptNumber, tblMaster.suppName, tblMaster.Date, tblMaster.Nonconformance, tblMaster.Audit, tblMaster.PA
  3. FROM tblMaster
  4. WHERE (((tblMaster.Date) Between [Startdate] And [Enddate]) AND ((tblMaster.Audit)=False) AND ((tblMaster.PA)=False));
Feb 26 '07 #7

NeoPa
Expert Mod 15k+
P: 31,186
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.
Feb 26 '07 #8

NeoPa
Expert Mod 15k+
P: 31,186
Check out this new Tutorial (SQL JOINs) for a full explanation of the Joins in SQL.
Feb 26 '07 #9

P: 78
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
Feb 28 '07 #10

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

Expand|Select|Wrap|Line Numbers
  1. PARAMETERS Startdate DateTime, Enddate DateTime;
  2. SELECT tblMaster.rptNumber, tblMaster.suppName, tblMaster.Date, tblMaster.Nonconformance, tblMaster.Audit, tblMaster.PA
  3. FROM tblMaster INNER JOIN tblSupplier ON tblMaster.suppName=tblSupplier.Supplier
  4. WHERE (((tblMaster.Date) Between [Startdate] And [Enddate]) AND ((tblMaster.Audit)=False) AND ((tblMaster.PA)=False));
Feb 28 '07 #11

NeoPa
Expert Mod 15k+
P: 31,186
Will do :)
Feb 28 '07 #12

NeoPa
Expert Mod 15k+
P: 31,186
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.
Expand|Select|Wrap|Line Numbers
  1. ((tblMaster.Audit)=False) AND ((tblMaster.PA)=False));
should be
Expand|Select|Wrap|Line Numbers
  1. (Not tblMaster.Audit) AND (Not tblMaster.PA));
Try it with changes made to cover these comments.
Feb 28 '07 #13

P: 78
It works!!!

Now how do I get a seperate query for the Suppliers that are not listed in tblSuppliers????
Mar 1 '07 #14

NeoPa
Expert Mod 15k+
P: 31,186
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.
Mar 1 '07 #15

P: 78
I have it as this:

Expand|Select|Wrap|Line Numbers
  1. PARAMETERS Startdate DateTime, Enddate DateTime;
  2. SELECT tblMaster.rptNumber, tblMaster.suppName, tblMaster.Date, tblMaster.Nonconformance, tblMaster.Audit, tblMaster.PA
  3. FROM tblMaster INNER JOIN tblSupplier ON tblMaster.suppName=tblSupplier.Supplier
  4. 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.
Mar 1 '07 #16

NeoPa
Expert Mod 15k+
P: 31,186
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 ...
Mar 1 '07 #17

P: 78
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.
Mar 1 '07 #18

NeoPa
Expert Mod 15k+
P: 31,186
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 :
Expand|Select|Wrap|Line Numbers
  1. Table Name=tblStudent
  2. Field; Type; IndexInfo
  3. StudentID; Autonumber; PK
  4. Family; String; FK
  5. Name; String
  6. University; String; FK
  7. Mark; Numeric
  8. 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 :
Expand|Select|Wrap|Line Numbers
  1. 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).
Expand|Select|Wrap|Line Numbers
  1. chkStored = 5
  2. If chkStored Then MsgBox 1
  3. If chkStored = True Then MsgBox 2
Would only show 1.
Mar 1 '07 #19

NeoPa
Expert Mod 15k+
P: 31,186
If you could post MetaData for tblMaster & tblSupplier then I think we can put this to bed :)
Mar 1 '07 #20

P: 78
I will post the metadata on Monday.... WAY too many deadlines due today...AND More keeps piling up...UGH gotta love Friday afternoon rushes!!
Mar 2 '07 #21

NeoPa
Expert Mod 15k+
P: 31,186
No problem. That gives me a bit of a break too :)
Mar 2 '07 #22

P: 78
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:

Expand|Select|Wrap|Line Numbers
  1. Table Name=tblMaster
  2. Field; Type; Indexinfo
  3. rptNumber; Autonumber; PK
  4. ptNumber; Text
  5. Revision; Text
  6. ptDesc; Text
  7. suppName; Text
  8. Originator; Text
  9. audtype; Text
  10. rptDate; Date/Time
  11. poNumber; Text
  12. soNumber; Text
  13. woNumber; Text
  14. QTy; Number
  15. sampSize; Number
  16. Qacc; Number
  17. Qrej; Number
  18. Nonconformance; Text
  19. Specification; Text
  20. Finding; Text
  21. Comments; Text
  22. RTV; Yes/No
  23. rtvqty; Number
  24. Useasis; Yes/No
  25. useasisqty; Number
  26. Rework; Yes/No
  27. rwkqty; Number
  28. Scrap; Yes/No
  29. scrapqty; Number
  30. Audit;Yes/No
  31. CA; Yes/NO
  32. PA; Yes/No
  33. Auditreq; Yes/No
  34. Major; Yes/no
  35. Minor; Yes/no
  36. Observation; Yes/No
  37. Eng; Text
  38. QA; Text
  39. Authorizedby; Text
  40. Assignedto; Text
  41. AssignDate; Date/Time
  42. DueDate; Date/Time
  43. Results; Memo
  44. CAPAtaken; Memo
  45. PA2; Yes/No
  46. CA2; Yes/no
  47. ANSBY; Text
  48. ansdate; Date/Time
  49. QAApp; Text
  50. Reviewby; Text
  51. ReviewDate; Date/Time
  52. clyes;Yes/No
  53. clno;Yes/NO
  54.  
  55. Table Name=tblSupplier
  56. Field; Type; Indexinfo
  57. 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...
Mar 5 '07 #23

NeoPa
Expert Mod 15k+
P: 31,186
Well...At least tblSupplier doesn't look too complicated ;)
Will get to this when I get home tonight.
Mar 5 '07 #24

NeoPa
Expert Mod 15k+
P: 31,186
Now I understand why you have the strange tblSupplier link ;)
Try this SQL and tell me if it works for you :
Expand|Select|Wrap|Line Numbers
  1. PARAMETERS Startdate DateTime,
  2.            Enddate DateTime;
  3. SELECT M.rptNumber,
  4.        M.suppName, 
  5.        M.Date, 
  6.        M.Nonconformance,
  7.        M.Audit,
  8.        M.PA
  9. FROM tblMaster AS M LEFT JOIN tblSupplier AS S
  10.   ON M.suppName=S.Supplier
  11. WHERE ((M.Date Between [Startdate] And [Enddate])
  12.   AND (Not M.Audit)
  13.   AND (Not M.PA)
  14.   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.
Mar 6 '07 #25

P: 78
Thanks That did it!!!!!!!!!!

Thanks for putting up with me, you have truly been very patient!
Mar 6 '07 #26

NeoPa
Expert Mod 15k+
P: 31,186
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
Mar 7 '07 #27

Post your reply

Sign in to post your reply or Sign up for a free account.