If Statement in Search Query

[Wolfroolz]

Background:We currently have a database which we use to track requests for equipment and track orders. In an item table we currently have a memo field called CPRNum which we use to track the numbers we assign to the equipment we purchase. For example "12345, 12346, 12347" etc. The results are based on the below query.

The Problem: We have been using this database for a year and have decided that it would be much more efficient if we can add the serial numbers to each cpr number for sending notifications/tracking. I created a new table that has a CPRnum, serial, ID(autonum), item fields. Item is linked to the Item(autonum) field in the item table. I figured out how to use this in a subform to add the pieces to each item, but now I have to alter the search form. My search form currently has about 21 fields that you can search on using as many of the fields as you want and it is working fine.

Question:
1)Is it possible to have a field for tagged, which will check to see if there is any CPR numbers listed and have "Yes" if there is, or if none(null) "No" instead of the CPR field?

2)How can I search for a CPRnum since it is no longer in that field, but rather in a separate table? is it possible to have it search the Serial table and return the Item ID? I am thinking of just adding a button to a CPR number search form.

SELECT Requests.[CGER Ticket], Items.Status, Items.Description, Items.[Budget Type], Items.[Budget Year] AS [Items_Budget Year], Items.[Special Features], Items.[Accesories / Options], Items.[Unit Price], Items.Quantity, Items.Manufacturer, Items.[Model #], Items.TotalCost, Items.[TotalCost w/OH], Items.RequisitionNumber, Items.ReqDate, Items.DeliveryDate, Items.Vendor, Items.PONumber, Items.PODate, Items.Buyer, Items.[CPRNum], Items.ReceiptedAmount, Items.ReceiptedDate, Items.[ReceiptedAmount w/OH], Items.ItemType, Items.SpecialProject, Items.Comments, Requests.Organization, Requests.Department, Requests.[Requester's Name], Requests.[End User Name], Requests.Section, Requests.Attention, Requests.[Budget Coordinator], Requests.Location, Requests.[MDS Code], Requests.RequestLink, Items.ID, Left(Items.[CPRNum],7) AS ShortCPR, Left(Items.[Description],200) AS ShortDesc
FROM Requests INNER JOIN Items ON Requests.[CGER Ticket] = Items.[CGER Ticket]
WHERE (((Requests.[CGER Ticket]) Like "*" & [Forms]![SearchForm]![RequestID] & "*" Or [forms]![SearchForm]![RequestID] Is Null) AND ((Requests.[Requester's Name]) Like "*" & [Forms]![SearchForm]![Requester] & "*" Or [forms]![SearchForm]![Requester] Is Null) AND ((Requests.[End User Name]) Like "*" & [Forms]![SearchForm]![EndUser] & "*" Or [forms]![SearchForm]![EndUser] Is Null) AND ((Requests.Section) Like "*" & [Forms]![SearchForm]![Section] & "*") AND ((Requests.Attention) Like "*" & [Forms]![SearchForm]![Att] & "*" Or [forms]![SearchForm]![Att] Is Null) AND ((Requests.[Budget Coordinator]) Like "*" & [Forms]![SearchForm]![BudgCo] & "*" Or [forms]![SearchForm]![BudgCo] Is Null) AND ((Requests.Organization) Like "*" & [Forms]![SearchForm]![Org] & "*" Or [forms]![SearchForm]![Org] Is Null) AND ((Requests.Department) Like "*" & [Forms]![SearchForm]![Dep] & "*" Or [forms]![SearchForm]![Dep] Is Null) AND ((Items.Description) Like "*" & [Forms]![SearchForm]![Desc] & "*" Or [forms]![SearchForm]![Desc] Is Null) AND ((Items.Comments) Like "*" & [Forms]![SearchForm]![comments] & "*" Or [forms]![SearchForm]![comments] Is Null) AND ((Items.Manufacturer) Like "*" & [Forms]![SearchForm]![cmbVendor] & "*" Or [forms]![SearchForm]![cmbVendor] Is Null)  AND ((Items.Vendor) Like "*" & [Forms]![SearchForm]![vendor] & "*" Or [forms]![SearchForm]![vendor] Is Null) AND ((Items.[Model #]) Like "*" & [Forms]![SearchForm]![Model] & "*" Or [forms]![SearchForm]![Model] Is Null) AND ((Items.[Budget Year]) Like "*" & [Forms]![SearchForm]![BudgetYear] & "*" Or [forms]![SearchForm]![BudgetYear] Is Null) AND ((Items.[Budget Type]) Like "*" & [Forms]![SearchForm]![BudgetType] & "*" Or [forms]![SearchForm]![BudgetType] Is Null) AND ((Items.RequisitionNumber) Like "*" & [Forms]![SearchForm]![ReqNum] & "*" Or [forms]![SearchForm]![ReqNum] Is Null) AND ((Items.PONumber) Like "*" & [Forms]![SearchForm]![PoNum] & "*" Or [forms]![SearchForm]![PoNum] Is Null) AND ((Items.[CPRNum]) Like "*" & [Forms]![SearchForm]![CPR] & "*" Or [forms]![SearchForm]![CPR] Is Null) AND ((Items.Status) Like "*" & [Forms]![SearchForm]![Status] & "*" Or [forms]![SearchForm]![Status] Is Null) AND ((Items.ItemType) Like "*" & [Forms]![SearchForm]![ItemType] & "*" Or [forms]![SearchForm]![ItemType] Is Null) AND ((Items.SpecialProject) Like "*" & [Forms]![SearchForm]![SpecProj] & "*" Or [forms]![SearchForm]![SpecProj] Is Null));

I have had a few programming classes 15 years ago and a crash course in access from the internet, and I seem to have gotten stuck with the job of database master at work. It has been rough going so I appreciate all the time/help everyone offers. If any further info is needed please let me know. Thanks.

 

[Minty]

Question:
1)Is it possible to have a field for tagged, which will check to see if there is any CPR numbers listed and have "Yes" if there is, or if none(null) "No" instead of the CPR field?

2)How can I search for a CPRnum since it is no longer in that field, but rather in a separate table? is it possible to have it search the Serial table and return the Item ID? I am thinking of just adding a button to a CPR number search form.

1) You don't need this as you can look it up / calculate it
2) You can link the table in a query using an right join and see if any fields are returned , or get cleverer with it by aggregating the CPR info per Main table ID and return a list of them.

 

[Wolfroolz]

Thanks for the quick reply Minty. Sorry I wasn't more clear. For question 1 I meant how would I go about calculating that in my query that won't slow it down.

For question two, do you mean like creating a field in the main query that would combine all the cpr's similar to how it was before? Not sure what you mean by aggregating them.

 

[Wolfroolz]

Ok, I have figured out how to do question 1. I created a new query which has two fields item, number and countOfCPR(decided a count of how many tags would be better then a yes or no because sometimes things get backordered) and altered my query to reflect this. New query below. However this seems to have made my query read only. Any ideas how to change that?

Second problem still exists, I am still not sure how to search for a CPR number since each cpr# is stored in a separate record. If I join it to my main search query when I search for anything other then a cpr # I get a duplicate result for each cpr #, which wont work. I need to have just one result per item no matter the quantity of CPR #'s. I can take the CPR search out of the main query add a separate search button to the search form which will search the cpr table and pull up the item info screen, but I would prefer it to show up in the query, not have to have a separate search.

New Query:

 SELECT Requests.[CGER Ticket], Items.Status, Items.Description, Items.[Budget Type], Items.[Budget Year] AS [Items_Budget Year], Items.[Special Features], Items.[Accesories / Options], Items.[Unit Price], Items.Quantity, Items.Manufacturer, Items.[Model #], Items.TotalCost, Items.[TotalCost w/OH], Items.RequisitionNumber, Items.ReqDate, Items.DeliveryDate, Items.Vendor, Items.PONumber, Items.PODate, Items.Buyer, Items.[CPRNum], Items.ReceiptedAmount, Items.ReceiptedDate, Items.[ReceiptedAmount w/OH], Items.ItemType, Items.SpecialProject, Items.Comments, Requests.Organization, Requests.Department, CheckTagged.[CountOfCPR], Requests.[Requester's Name], Requests.[End User Name], Requests.Section, Requests.Attention, Requests.[Budget Coordinator], Requests.Location, Requests.[MDS Code], Requests.RequestLink, Items.ID, Left(Items.[CPRNum],7) AS ShortCPR, Left(Items.[Description],200) AS ShortDesc
FROM (Requests INNER JOIN Items ON Requests.[CGER Ticket] = Items.[CGER Ticket]) INNER JOIN CheckTagged ON Items.ID = CheckTagged.ID
WHERE (((Requests.[CGER Ticket]) Like "*" & [Forms]![SearchForm]![RequestID] & "*" Or [forms]![SearchForm]![RequestID] Is Null) AND ((Requests.[Requester's Name]) Like "*" & [Forms]![SearchForm]![Requester] & "*" Or [forms]![SearchForm]![Requester] Is Null) AND ((Requests.[End User Name]) Like "*" & [Forms]![SearchForm]![EndUser] & "*" Or [forms]![SearchForm]![EndUser] Is Null) AND ((Requests.Section) Like "*" & [Forms]![SearchForm]![Section] & "*") AND ((Requests.Attention) Like "*" & [Forms]![SearchForm]![Att] & "*" Or [forms]![SearchForm]![Att] Is Null) AND ((Requests.[Budget Coordinator]) Like "*" & [Forms]![SearchForm]![BudgCo] & "*" Or [forms]![SearchForm]![BudgCo] Is Null) AND ((Requests.Organization) Like "*" & [Forms]![SearchForm]![Org] & "*" Or [forms]![SearchForm]![Org] Is Null) AND ((Requests.Department) Like "*" & [Forms]![SearchForm]![Dep] & "*" Or [forms]![SearchForm]![Dep] Is Null) AND ((Items.Description) Like "*" & [Forms]![SearchForm]![Desc] & "*" Or [forms]![SearchForm]![Desc] Is Null) AND ((Items.Comments) Like "*" & [Forms]![SearchForm]![comments] & "*" Or [forms]![SearchForm]![comments] Is Null) AND ((Items.Manufacturer) Like "*" & [Forms]![SearchForm]![cmbVendor] & "*" Or [forms]![SearchForm]![cmbVendor] Is Null)  AND ((Items.Vendor) Like "*" & [Forms]![SearchForm]![vendor] & "*" Or [forms]![SearchForm]![vendor] Is Null) AND ((Items.[Model #]) Like "*" & [Forms]![SearchForm]![Model] & "*" Or [forms]![SearchForm]![Model] Is Null) AND ((Items.[Budget Year]) Like "*" & [Forms]![SearchForm]![BudgetYear] & "*" Or [forms]![SearchForm]![BudgetYear] Is Null) AND ((Items.[Budget Type]) Like "*" & [Forms]![SearchForm]![BudgetType] & "*" Or [forms]![SearchForm]![BudgetType] Is Null) AND ((Items.RequisitionNumber) Like "*" & [Forms]![SearchForm]![ReqNum] & "*" Or [forms]![SearchForm]![ReqNum] Is Null) AND ((Items.PONumber) Like "*" & [Forms]![SearchForm]![PoNum] & "*" Or [forms]![SearchForm]![PoNum] Is Null) AND ((Items.[CPRNum]) Like "*" & [Forms]![SearchForm]![CPR] & "*" Or [forms]![SearchForm]![CPR] Is Null) AND ((Items.Status) Like "*" & [Forms]![SearchForm]![Status] & "*" Or [forms]![SearchForm]![Status] Is Null) AND ((Items.ItemType) Like "*" & [Forms]![SearchForm]![ItemType] & "*" Or [forms]![SearchForm]![ItemType] Is Null) AND ((Items.SpecialProject) Like "*" & [Forms]![SearchForm]![SpecProj] & "*" Or [forms]![SearchForm]![SpecProj] Is Null));

 

[Minty]

If you do a right join instead of an inner join you should be able to edit the main table query results.

Second part - Do you need to find each unique occurrence of the CPR per ID or the latest one or would you like a list of them? You can search for a CPR in the separate table and return the ID allowing you to view the top level information.

Could you post up you table layout with the relationships shown - as I'm not sure I've quite got the right data layout in my head... ( it is Friday afternoon after all )

 

[Wolfroolz]

When I try to change it to a right join I get "Join Operation not supported" error.

FROM (Requests INNER JOIN Items ON Requests.[CGER Ticket] = Items.[CGER Ticket]) RIGHT JOIN CheckTagged ON Items.ID = CheckTagged.ID

hopefully this link will work for the relationships
https://www.dropbox.com/l/s/nUz6EJn4W22pWcXh6TlUcu

 

[Minty]

Apologies I missed that you had two joins in your query - normally more that 2 table join makes updates difficult via a query.
You may need to display the information in one form but add/edit in another that you open based on the current record. (Its generally the accepted method, as it stops inadvertent deletion loss of data.)

The right join(sorry been on SQL Server all day) - is better known in access as an outer join - right click on the join in the design window you should see the options that are available.

In your relationship I think you Serials table should be on the end of a many relationship not a 1-1 one.

As a bit of advice looking at your field names please get rid of the spaces and especially get rid of the special characters ' / - etc. these all have other meanings in VBA and will cause you a whole world of pain later on.

It might be worth posting up a stripped down version of the database and an example of the actual data in the format you are trying to achieve.

 

[Wolfroolz]

Most of the edits shouldn't be done on the search screen, there is mainly one thing we edit, but I can add it to another area and would be ok without editing the query.

This database was built based off another database designed by someone even worse then me at this and so a lot of the names etc were from there. I have figured out that all the spaces/characters are a pita already, lol. But at this point am working with it till I really get time to do a massive revision, since now I have to squeeze time to work on this between about 4 million other things. It is on my list. I will try to take this home and put together a stripped down version this weekend(we are about to be buried in snow anyway)