UtterAccess.com
X   Site Message
(Message will auto close in 2 seconds)

Welcome to UtterAccess! Please ( Login   or   Register )

Custom Search
 
   Reply to this topicStart new topic
> Determin Whats Different In Text, Access 2013    
 
   
soggycashew
post Jun 24 2019, 09:45 AM
Post#1



Posts: 325
Joined: 23-April 13
From: WV, USA


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...
Attached File(s)
Attached File  sample_data.JPG ( 87.29K )Number of downloads: 21
 
Go to the top of the page
 
Daniel_Stokley
post Jun 24 2019, 10:19 AM
Post#2



Posts: 312
Joined: 22-December 14
From: Grand Junction, CO, USA


Sorry, but I don't understand what you want. Please explain again.
Go to the top of the page
 
theDBguy
post Jun 24 2019, 10:20 AM
Post#3


Access Wiki and Forums Moderator
Posts: 75,724
Joined: 19-June 07
From: SunnySandyEggo


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

--------------------
Just my 2 cents... "And if I claim to be a wise man, it surely means that I don't know" - Kansas
Access Website | Access Blog | Email
Go to the top of the page
 
soggycashew
post Jun 24 2019, 10:46 AM
Post#4



Posts: 325
Joined: 23-April 13
From: WV, USA


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!
This post has been edited by soggycashew: Jun 24 2019, 10:48 AM
Attached File(s)
Attached File  Image_2.JPG ( 39.91K )Number of downloads: 13
 
Go to the top of the page
 
theDBguy
post Jun 24 2019, 11:13 AM
Post#5


Access Wiki and Forums Moderator
Posts: 75,724
Joined: 19-June 07
From: SunnySandyEggo


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!

--------------------
Just my 2 cents... "And if I claim to be a wise man, it surely means that I don't know" - Kansas
Access Website | Access Blog | Email
Go to the top of the page
 
soggycashew
post Jun 24 2019, 11:58 AM
Post#6



Posts: 325
Joined: 23-April 13
From: WV, USA


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?
Go to the top of the page
 
Daniel_Stokley
post Jun 24 2019, 04:45 PM
Post#7



Posts: 312
Joined: 22-December 14
From: Grand Junction, CO, USA


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.


Go to the top of the page
 
ITguaranteed
post Jun 24 2019, 06:54 PM
Post#8



Posts: 29
Joined: 19-June 19
From: Tasmania, Australia


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)
Go to the top of the page
 
soggycashew
post Jun 25 2019, 05:28 AM
Post#9



Posts: 325
Joined: 23-April 13
From: WV, USA


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,
Go to the top of the page
 
Daniel_Stokley
post Jun 25 2019, 07:28 AM
Post#10



Posts: 312
Joined: 22-December 14
From: Grand Junction, CO, USA


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.

Attached File  SoggyCashew01.png ( 38.79K )Number of downloads: 2


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.





This post has been edited by Daniel_Stokley: Jun 25 2019, 07:30 AM
Go to the top of the page
 
soggycashew
post Jun 25 2019, 08:41 AM
Post#11



Posts: 325
Joined: 23-April 13
From: WV, USA


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]));
Go to the top of the page
 
Daniel_Stokley
post Jun 25 2019, 09:25 AM
Post#12



Posts: 312
Joined: 22-December 14
From: Grand Junction, CO, USA


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.
Go to the top of the page
 
soggycashew
post Jun 25 2019, 09:38 AM
Post#13



Posts: 325
Joined: 23-April 13
From: WV, USA


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.
Go to the top of the page
 
Daniel_Stokley
post Jun 25 2019, 11:02 AM
Post#14



Posts: 312
Joined: 22-December 14
From: Grand Junction, CO, USA


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.
This post has been edited by Daniel_Stokley: Jun 25 2019, 11:02 AM
Go to the top of the page
 
soggycashew
post Jun 25 2019, 12:54 PM
Post#15



Posts: 325
Joined: 23-April 13
From: WV, USA


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

Thanks Again!
Go to the top of the page
 
Daniel_Stokley
post Jun 25 2019, 02:07 PM
Post#16



Posts: 312
Joined: 22-December 14
From: Grand Junction, CO, USA


Excellent! Glad to help.
Go to the top of the page
 
soggycashew
post Jun 26 2019, 12:22 PM
Post#17



Posts: 325
Joined: 23-April 13
From: WV, USA


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)
This post has been edited by soggycashew: Jun 26 2019, 12:58 PM
Go to the top of the page
 


Custom Search


RSSSearch   Top   Lo-Fi    21st July 2019 - 07:47 AM