Printable Version of Topic

Click here to view this topic in its original format

UtterAccess Forums _ Access Queries _ Determin Whats Different In Text

Posted by: soggycashew Jun 24 2019, 09:45 AM

Hello, I have a table that I want to "run" a to and "from" criteria between (ProductID) and display only the "From" fields in a report. Below is example data...

 

Posted by: Daniel_Stokley Jun 24 2019, 10:19 AM

Sorry, but I don't understand what you want. Please explain again.

Posted by: theDBguy Jun 24 2019, 10:20 AM

Hi. Would you mind explaining it a little bit more? Thanks.

Posted by: soggycashew Jun 24 2019, 10:46 AM

Ok sure will.....

Alright if I had a form Form1 and on that form there were two combo boxes cbo1 and cbo2 which their source was the (ProductID) on the table image I showed. For example If I selected in the "From" cbo1 (ProductID2) and in the "To" cbo2 I selected (ProductID3) my results I would need would be whats not in the "From".

The example image is from the last image I posed. The ones with the red X are the same items so they wont display. The items that are circled are the ones I want to display in my report, they are the "To" in cbo2.


Hope that's better, if not let me know!

 

Posted by: theDBguy Jun 24 2019, 11:13 AM

Hi. Thanks for trying to explain; but sadly, it didn't help me understand what you're asking to do. Hopefully, someone else picked it up though. Good luck!

Posted by: soggycashew Jun 24 2019, 11:58 AM

Let me try again...

Ok, from my form I need to display in a report the difference of data from the two selections or combo boxes. If the data is the same then I don't want that fields data displayed but if they are different then I only want the second data displayed.

So if I selected Test1 in the first combo box
Then I selected Test 2 in the second combo box
and the results gave me the same in some fields I wouldn't display those BUT lets say the field steel the two were diferent text like 1.937 from the first combo boxes results and 1.437 from the second combo result.
I would want to display the 1.437

Is that better?

Posted by: Daniel_Stokley Jun 24 2019, 04:45 PM

OK, I think I understand now. Let me restate what you wrote, just to make sure I've got this.

You have two combo boxes on your form. Each combo box lets you choose a product. After choosing two products, you want to dynamically create a query that will show only those columns where the two products have different results and/or hide those columns that have the same values for the two selected products.

If I'm right, then the key part there is the dynamic query. That might be possible in VBA. A solution in VBA would be complicated. How are your VBA skills? If anyone sees things differently, please jump in.

SoggyCashew, please let us know if want to pursue a VBA solution.



Posted by: ITguaranteed Jun 24 2019, 06:54 PM

I am as confused as everyone else here.
Are you trying to select rows to display from a table AND which columns to display as well? (based upon your combo boxes)

Posted by: soggycashew Jun 25 2019, 05:28 AM

Daniel you got it thumbup.gif but where you say only show different results and/or hide those columns that have the same values. I only want to show the results from to "To" combo box not the "From" combo box. What im trying to do is show what components are needed to switch over to another product, kinda like a parts list but the reason I want to hide those columns that have the same values for the two selected products is they are already installed so I don't need those components.

VBA is good, whats next? Below is a query that gets me the 2 results from choosing each product.

CODE
SELECT tbl_Product.ProductID, tbl_Product.Product, tbl_Components.Steel, tbl_Components.Paper, tbl_Components.CutOffBlades, tbl_Components.FeedWheel, tbl_Components.FinalForm, tbl_Components.LastPass, tbl_Components.Straightner, tbl_Components.FingerStation, tbl_Components.GlueHead, tbl_Components.OilerWheel, tbl_Components.PinprickLowerPlate
FROM (tbl_Product INNER JOIN tbl_Components ON tbl_Product.ProductID = tbl_Components.ProductID) INNER JOIN tbl_SetUp ON tbl_Product.ProductID = tbl_SetUp.Product
WHERE (((tbl_Product.ProductID) Between [Forms]![frm_Switchboard].[cboComingFrom] And [Forms]![frm_Switchboard].[cboGoingTo]));






Thanks,

Posted by: Daniel_Stokley Jun 25 2019, 07:28 AM

We might get to the VBA eventually, but I want you to first take a look at the following. I created a pseudo Product table and a few queries.



The table has a ProductID, ProductName, and four columns for various characteristics. For example: F1 could be "Steel", F2 could be "Paper", and so on...

Think of qry_Prod1 and qry_Prod2 as the record sources for your two combo boxes. Finally, qry_Prod1_vs_Prod2 compares the two items in the first two queries. It shows the word "Same" if the two data elements are equal. Here is the SQL for that query:

SQL
SELECT P1.ProductID, [p1].[ProductName] & " vs " & [p2].[ProductName] AS Comparing,
IIf([p1].[f1]=[p2].[f1],"Same",[p1].[f1] & " vs " & [p2].[f1]) AS M1,
IIf([p1].[f2]=[p2].[f2],"Same",[p1].[f2] & " vs " & [p2].[f2]) AS M2,
IIf([p1].[f3]=[p2].[f3],"Same",[p1].[f3] & " vs " & [p2].[f3]) AS M3,
IIf([p1].[f4]=[p2].[f4],"Same",[p1].[f4] & " vs " & [p2].[f4]) AS M4
FROM qry_Prod1 AS P1, qry_Prod2 AS P2;


Notice that the source for that query are the other two queries, and that they are not joined (Cartesian Product).

So, please take a look and let us know what you think. Next step would be a discussion of using VBA with loops and recordsets to dynamically build qry_Prod1_vs_Prod2.





Posted by: soggycashew Jun 25 2019, 08:41 AM

I understand what your doing and tried with my DB but couldn't get a compare query Query3 to work...

I created a query Query1 with a criteria linked to the first combo box then I created a query Query2 linked to the second combo box and each each is giving the data for the combo box chosen. Next I tried to compare the two querys, and the querys I only have two fields besides the ID for ease.

CODE
SELECT tbl_Product.ProductID, tbl_Components.Steel, tbl_Components.Paper
FROM tbl_Product INNER JOIN tbl_Components ON tbl_Product.ProductID = tbl_Components.ProductID
WHERE (((tbl_Product.ProductID)=[Forms]![frm_Switchboard]![cboComingFrom]));




CODE
SELECT tbl_Product.ProductID, tbl_Components.Steel, tbl_Components.Paper
FROM tbl_Product INNER JOIN tbl_Components ON tbl_Product.ProductID = tbl_Components.ProductID
WHERE (((tbl_Product.ProductID)=[Forms]![frm_Switchboard]![cboGoingTo]));

Posted by: Daniel_Stokley Jun 25 2019, 09:25 AM

Those two queries for the two selected products look fine. You said your "Compare" query didn't work. Please show us the SQL of that query.

Posted by: soggycashew Jun 25 2019, 09:38 AM

What I ment was I couldn't figure out how to do the compare query like you had shown. I did however by messing around fount the strComp so I messed with it with Query3. I just put Q1 and Q2 as my tables and for one field I used:

ComparisonSteel: IIf(StrComp([Query1].[Steel],[Query2].[Steel]),[Query2].[Steel])

If they were the same the field would be blank and if they were different the field would give me [Query2].[Steel] which was the result of my 2nd combo box.

Posted by: Daniel_Stokley Jun 25 2019, 11:02 AM

Alrighty then smile.gif , it sounds like you got it figured out. Just a parting comment thought. You wrote that you couldn't figure out how to do the compare query like I did. Actually, you did. The idea was to use the first two queries as the source for the compare query. In case you didn't know, the source(s) for a query can be tables or other queries. Anyway, the first two queries (for Product 1 and for Product 2) are not joined in the compare query. That is called a Cartesian Product.

In your compare query, you have the following: ComparisonSteel: IIf(StrComp([Query1].[Steel],[Query2].[Steel]),[Query2].[Steel])

If you like the result from that, fine, but my version would have been something like: ComparisonSteel: IIf([Query1].[Steel]=[Query2].[Steel],"Same",[Query1].[Steel]&" vs "&[Query2].[Steel])

That would show the values for both Product 1 and for Product 2.

Posted by: soggycashew Jun 25 2019, 12:54 PM

Thanks for the help! I'm sticking with mine since I only need the info from Q2....

Thanks Again!

Posted by: Daniel_Stokley Jun 25 2019, 02:07 PM

Excellent! Glad to help.

Posted by: soggycashew Jun 26 2019, 12:22 PM

NVM, I figured it out.... All I had to do was use the field instead of the string... Thanks


Daniel, I'm having trouble with the results because Fingerstation is a Yes/No field... What am I doing wrong?

FingerStation: IIf(StrComp([qry_PartsListComingFrom].[FingerStation],[qry_PartsListGoingTo].[Fi
ngerStation]),-1,0)

Posted by: soggycashew Aug 5 2019, 07:11 AM

Hello again, I'm having trouble with this again since I normalized my tables. The issue I'm having is I have 3 tables [tbl_product] junction table [tbl_ProductComponents] and [tbl_ComponentVar]. Now before I just had all my data in one table named and it was easy to pull data but now I have ComponentName and ComponentVar which is the part name and its length or size.

The way I have it previously and now is there are 3 querys [qry_PartsListComingFrom], [qry_PartsListGoingTo] and [qry_PartsList]. The "Coming from" pulls the product name from my form and gives me a list of ComponentName and its ComponentVar that is associated with that product and same goes for the "Going To". Next I needed to compare the two querys and get what ComponentName and its ComponentVar are the same or different and if the same say same and if different display the data from the "Going To" for the ComponentName and its ComponentVar.

Here is what I have, just don't know how to compare the two and display in query...The ??? is where I'm stuck. Thanks....

CODE
Component: IIf(StrComp([qry_PartsListComingFrom]???,[qry_PartsListGoingTo]???),[qry_PartsListGoingTo]???,"Same")


Here is how the data is for example in the coming from and going to and would be the same for parts list query:








 

Posted by: soggycashew Aug 5 2019, 08:43 AM

Ok, made a little headway I needed a join. Is this correct? It gives me the results I'm looking for

CODE
SELECT qry_PartsListGoingTo.ProductID, qry_PartsListGoingTo.ComponentName, IIf(StrComp([qry_PartsListComingFrom].[ComponentVar],[qry_PartsListGoingTo].[ComponentVar]),[qry_PartsListGoingTo].[ComponentVar],"Same") AS ComponentDifferences
FROM qry_PartsListComingFrom RIGHT JOIN qry_PartsListGoingTo ON qry_PartsListComingFrom.ComponentName = qry_PartsListGoingTo.ComponentName;

Posted by: Daniel_Stokley Aug 5 2019, 11:22 AM

QUOTE
Is this correct? It gives me the results I'm looking for


I think you answered your own question. If it gives you the results you are looking for (and expect), then it is correct. Right?



Posted by: soggycashew Aug 5 2019, 12:07 PM

Daniel_Stokley, just wanted to make sure.... I do have one more issue since I normalized and changed relationships. My form that I use to Add/Change Product Components values. I just cant figure out how I can do it now. There are 11 components (as of now could change in time) and each component has a size as shown below in this example. How could I set up a form to show a component name and in a combo box show all the componate var for it like:

(Text Box = Feed Wheel) = (Combo Box = 125, 90, 84,146)


CODE
tbl_Product
(pk)ProductID   Product    IsInactive
-----------------------------------------
     1          B1OS           No
     2          B2OS           Yes
     3          B2             No

tbl_ProductComponents Junction Table
(pk)ProductComponentsID   ProductID    ComponentVarID
-----------------------------------------------------------
            1                 1                 2
            2                 1                 7
            3                 1                 8
            4                 3                 6

tbl_ComponentSizes
(pk)ComponentVarID   ComponentName    ComponentVar
------------------------------------------------------------
           1            Feed Wheel          125
           2            Feed Wheel          90
           3            Feed Wheel          84
           4            Feed Wheel          146
           5            Cut-Off             84
           6            Cut-Off             90
           7            Cut-Off             125
           8            P Plate             2-1/2
           9            P Plate             3
           10           P Plate             2-1/16
           11           P Plate             3-1/2

Posted by: NimishParikh Aug 6 2019, 07:46 AM

Is it worth revisiting table structure? Shouldn't be columns like "CutoffBlades", "Steel", "Paper" etc. should be a value in a field that can be identified as something like "Item" and have data in the following format. Not sure how to handle Boolean Field "FingerStation" though.

Just a thought.

CODE
ComponentID
ProductID
Item
ItemValue


CODE
1,1,CutOffBlades,"125"
1,1,"Steel","1.25"
1,1,"Paper", "2P"
...
...

Nimish

Posted by: Daniel_Stokley Aug 6 2019, 08:03 AM

QUOTE
How could I set up a form to show a component name and in a combo box show all the componate var for it


Here is just one possibility. Create a pair of cascading combo boxes on your form. The first (I will name it cboComponentName) will show only the component names. The Row Source would be:

CODE
SELECT [qry_ComponentNames].[Component Name] FROM [qry_ComponentNames] ORDER BY [Component Name];


That combo box would have an After Update Event to set the Row Source of the second combo box (I will name it cboComponentSize) to show sizes for the selected Component name. Here is the After Update VBA:

CODE
Private Sub cboComponentName_AfterUpdate()
   ' Set the Component Size combo box to be limited by the selected Component Name
   Me.cboComponentSize.RowSource = "SELECT ComponentVar FROM tbl_ComponentSizes " _
                                 & "WHERE ComponentName = '" & Nz(Me.cboComponentName) & "' ORDER BY ComponentVar"
   End Sub




Posted by: soggycashew Aug 6 2019, 09:54 AM

Daniel_Stokley, I didnt have that query and it gave me the name for every time I entered it so I added distinct so it only displayed once

CODE
SELECT DISTINCT tbl_ComponentVar.ComponentName
FROM tbl_ComponentVar
ORDER BY tbl_ComponentVar.ComponentName;


 

Posted by: Daniel_Stokley Aug 6 2019, 11:56 AM

Ah yes, I forgot to include the SQL for qry_ComponentNames:

CODE
SELECT DISTINCT tbl_ComponentSizes.ComponentName AS [Component Name]
FROM tbl_ComponentSizes
ORDER BY tbl_ComponentSizes.ComponentName;


But it seems you figured out that you needed DISTINCT.

So, is it all working for you now?



Posted by: soggycashew Aug 6 2019, 01:10 PM

Daniel_Stokley, the example I included has 5 products and "Product 1" is the only one that has data. Im not getting this to work correctly at all. On my frm_AddDeleteProdComponents if you open it up in form view and select product 1 it displays the data I manually entered into the tbl_ProductComponents to get it to show data.

If you try and change data it wont, if you try to add FingerStation as a new it will allow you to select it but wont show cascading items to its right cbobox. If you try and change an existing component it will but again wont change data in its cascading combo box. This form is used to add data (Component/ComponentVar) for each product selected. I also needed the component list to only show whats left in the list of components. For example if in the first record I used CutoffBlades then it would no longer display in the list so I sont have two or even three of the same components.

 ForumTEST.zip ( 71.88K ): 2
 

Posted by: Daniel_Stokley Aug 6 2019, 02:39 PM

I'm sorry Soggy but now you are into an area where I am weak.

Can anyone here help Soggy?



Posted by: soggycashew Aug 8 2019, 04:23 AM

I got it sorted out Daniel_Stokley, Thanks for the help!