Please need help with a query

[Tari]

Good Afternoon,

our company needs requisitions to purchase for our ordering process. We create those with the help of an access database. We are now going through renovations and I need to track all the RTPs that are written for the renovation. So I added a "yes/no" check box to my RTP table and form.

I now need a to create a query that only considers the records that have this box checked.

My access knowledge is very basic I know how to create a query that pulls all the info that I want, but not how to specify it.

Help please



Thanks!

[hertfordkc]

true

When you run the query you have, what values do you get in the renovations column?

[Tari]

tickbox.. so it return a value of 0 or 1

I looked at the query that I use to display my RTPs as report (sample attached) and I totally forgot that somebody here wrote it for me - which now makes it impossible for me to tweak because I don't understand code
Query reads:

SELECT RTP.*, [Order Details].*, Employees.*, Suppliers.*, Department.Department AS dept, [GL Code].GLCode AS GLCDE
FROM [GL Code] INNER JOIN (Employees INNER JOIN (Department INNER JOIN (Suppliers INNER JOIN (RTP INNER JOIN [Order Details] ON RTP.OrderID=[Order Details].[RTP Number]) ON Suppliers.CustomerID=RTP.CustomerID) ON Department.DepartmentID=[Order Details].Department) ON Employees.EmployeeID=RTP.EmployeeID) ON [GL Code].GLCodeID=[Order Details].[GL Code];

renovations is part of the RTP table. So I need this query to only return records that have renovations as value of 1.

Thanks so much for the help

Tari

[Tari]

And I have a 2nd problem with the same query..

I know this is hard to explain without seeing my database.. but it's too big to upload


I created a form based on the query above and I also ran into an issue with how the information is displayed. The query above creates a new record for every RTP ID. But I need it based on RTP number, which can have several ID's on it. An RTP ID is assigned for each order line. I attached a bunch of screenshots, hope that helps

Thanks so much!

Tari

[hertfordkc]

according to RTP number.
http://www.techrepublic.com/article/...access/5756141
http://support.microsoft.com/kb/290136
These are two which I picked from Googling "Access query grouping". I'd suggest you run through several of the Google results as they vary quite a bit in clarity of communication. Also, a good understanding of queries will make life with forms and reports a lot easier.
Since you haven't spent much time with queries, take time to explore the other capabilities of queries such as totaling, sorting, and calculated fields, update queries....

[Tari]

No offense Hertfordkc, but if I had found an answer to my question that I understood by googling, I wouldn't have asked here.

I didn't write the query to begin with, therefore I can't figure out how to change it.

[jzwp11]

SELECT RTP.*, [Order Details].*, Employees.*, Suppliers.*, Department.Department AS dept, [GL Code].GLCode AS GLCDE
FROM [GL Code] INNER JOIN (Employees INNER JOIN (Department INNER JOIN (Suppliers INNER JOIN (RTP INNER JOIN [Order Details] ON RTP.OrderID=[Order Details].[RTP Number]) ON Suppliers.CustomerID=RTP.CustomerID) ON Department.DepartmentID=[Order Details].Department) ON Employees.EmployeeID=RTP.EmployeeID) ON [GL Code].GLCodeID=[Order Details].[GL Code];

Since the query shows RTP.*, all fields from that table can be used in the query. For the query to just return those records where renovations=1, you just simply need to add that criteria. That is accomplished with a WHERE clause. You can do it in design grid view of the query by adding the renovation field to the end of the list of fields and then setting the criteria row to 1

OR

If you want to edit the SQL text as shown above simply remove the ending semi-colon and add the WHERE clause as follows:

WHERE RTP.renovations=1

I created a form based on the query above and I also ran into an issue with how the information is displayed. The query above creates a new record for every RTP ID. But I need it based on RTP number, which can have several ID's on it. An RTP ID is assigned for each order line. I attached a bunch of screenshots, hope that helps

A SELECT query cannot create new records. You cannot base a form directly on this query because of the other tables involved and the relationships between them. If you use a form based on the query, Access will attempt to create unique records across many if not all of the tables involved.

I would create a form based on the RTP table alone. You will have to create a subform for those tables that make up the many side of any one-to-many relationships. Without seeing your relationship diagram it is hard to determine what data can be supplied by combo boxes in the main form or what data has to be shown in subforms. Can you post a snapshot of your relationship window?

[Tari]

I would create a form based on the RTP table alone. You will have to create a subform for those tables that make up the many side of any one-to-many relationships. Without seeing your relationship diagram it is hard to determine what data can be supplied by combo boxes in the main form or what data has to be shown in subforms. Can you post a snapshot of your relationship window?

Attached.

If I create a form based on the RTP table, I'm missing information from other tables. I need stuff out of Suppliers and more importantly out of Order details extended. So the goal is when you open a record in the new form (based on RTP number, for renovations=1 only) it displays all the information out of table RTP, table Order details extended and table Suppliers at one glance.

Thanks for your help!

[jzwp11]

As I said, you will need a subform. For the main form (based on the RTP table), you would use combo boxes to supply the employee and the supplier info. Then you would create a form based on the Order Details Extended table and embed that form in the main form since the order details is the many side of a one-to-many relationship. By the way, I believe the relationship between the RTP and Order Details Extended table is not correct. I believe there should be an infinity sign next RTP Number field in the detail table. Do you have referential integrity enforced for that join?

[Tari]

As I said, you will need a subform. For the main form (based on the RTP table), you would use combo boxes to supply the employee and the supplier info. Then you would create a form based on the Order Details Extended table and embed that form in the main form since the order details is the many side of a one-to-many relationship.

I played around with that yesterday and got almost to where I want to be So I created a query based on my RTP form and put in the criteria to be renovations=1. I then created a form based on the query and added a subform based on OrderID. That pulls the information I want. I just have some calculations left to do and then I'm done. Thanks so much for pointing me in the right direction!

By the way, I believe the relationship between the RTP and Order Details Extended table is not correct. I believe there should be an infinity sign next RTP Number field in the detail table. Do you have referential integrity enforced for that join?

My database started out as Microsoft template and I changed it to my needs. I didn't touch the relationships, that's probably why they are wrong I have no idea what referential integrity means, but I'll google that. Thanks!

[jzwp11]

My database started out as Microsoft template and I changed it to my needs. I didn't touch the relationships, that's probably why they are wrong I have no idea what referential integrity means, but I'll google that. Thanks!

To enforce referential integrity, just right click on the join line between the two tables and select the enforce referential integrity option.