Solved

How to Take data from one table, compare and put in another

Posted on 2004-04-15
6
341 Views
Last Modified: 2012-06-21
So far This is what I have, below is the code of what I have got, but before we get on to the code let me give you some background first.

Okay, this is going to sound a llittle bit complicated.

THis is for a inventory system for example a product such as a t-shirt, this will ultimately be used on a ecommerce based website.

What i want to be able to do display a query that will show the other available descriptions the product is available in, i.e. if product 22178A comes in two descriptions as below, it should show a third field in the query to show the other description.
a product may have 1 or more different colors up to a maxmium of 5, so i would create 5 more fields in my query called Color1,Color2,Color3,Color4
ACCESS TABLE AS FOLLOWS

REFRENCE NO.  | DESCRIPTION
22178A | WHITE PARROT DESIGN T-SHIRT
22178B | BLUE PARROT DESIGN T-SHIRT
22178C | GREEN PARROT DESIGN T-SHIRT

All the above t-shirts are the same design but different refernce no. The first part of the refrence number is alwasy the same apart from the last alpha digits. i.e. the numic digits are allways teh same for the same design number. So i guess in order to find out which designs belong to each other in the product list i would have to first find the matching numeric refrences only disregarding the alpha chr, there must be some seperation code to seperate the numeric from the alpha chrs, then after that it should compare the refrence numbers to the others in the list and then take their descrtiption and insert it in to the corresponding new field i.e. color1,color2, color So the new query result will look like this.

REFRENCE NO. | DESCRIPTION | COLOR1 | COLOR2 |
22178A | WHITE PARROT DESIGN T-SHIRT | BLUE PARROT DESIGN T-SHIRT | GREEN PARROT DESIGN T-SHIRT
22178B | BLUE PARROT DESIGN T-SHIRT | GREEN PARROT DESIGN T-SHIRT | WHITE PARROT DESIGN T-SHIRT
22178C | GREEN PARROT DESIGN T-SHIRT | WHITE PARROT DESIGN T-SHIRT | BLUE PARROT DESIGN T-SHIRT


Another Example :

I.E. THIS IS MY PRODUCTS TABLE WITH 2 FIELDS, REFRENCE AND PRODUCTNAME

REFRENCE NO.  | PRODUCTNAME
22178A |             WHITE
22178B |             BLUE
22178C |             GREEN

THEN AFTER RUNNING THE CODE OUTPUT SHOULD BE AS FOLLOWS :

TO A NEW TABLE

REFRENCE NO.  | PRODUCTNAME | COLOR1 | COLOR2
22178A             | WHITE             | BLUE      | GREEN
22178B             | BLUE                |GREEN     |WHITE
22178C             |GREEN              |WHITE     | LUE


OR I CAN JUST REDUCE THE TABLE EVEN FURTHUR TO SHOW AS FOLLOWS

REFRENCE NO.  | PRODUCTNAME | COLOR1 | COLOR2
22178A             | WHITE             | BLUE      | GREEN

THE ABOVE SHOWS ME THAT ITEM NO. 22178 IS AVAILABLE IN WHITE, BLUE AND GREEN

I WOULD LIKE TO OUTPUT IT TO A TABLE SAY PRODUCT_RESULTS

 Here is the code that I Got courtesy of rockiroads.  When it  runs on a onclick command, it runs for ever and then stalls, after restarting access it only creates tblproducts with many blank records... .  I belive the problem is becuase i have to create a index for the colors.. the table just seems to keep on growning and growing with blank data.. any help would be appreciated.


Option Compare Database
Dim m_iMaxColors As Integer
Private Sub Command0_Click()
Dim qdQryDef As QueryDef                  'Querydef used to create recordset
Dim rsRecSet As dAo.Recordset             'Recordset used to read data
Dim sSql As String
Dim sDesc As String
Dim sRefNo As String
Dim iPos As Integer
Dim sRowSource As String


'Create a table called tblProducts, by default it will have 5 color columns
    sSql = "CREATE TABLE tblProducts (Refrence Text, ProductName Text, Color1 Text, Color2 Text, Color3 Text, Color4 Text, Color5 Text)"

On Error Resume Next
'run the sql command to create the table
    DoCmd.RunSQL sSql
If Err.Number = 3010 Then   '3010 is table already exists
        MsgBox "Failed to create table" & vbCrLf & Err.Description
        Exit Sub 'Do no continue
    End If
    'Initalise colors count
    m_iMaxColors = 5



sRowSource = ""
    sSql = "SELECT Refrence, ProductName FROM products"  'Returns RefNo and Descriptio
    Set qdQryDef = CurrentDb.CreateQueryDef("", sSql)
    Set rsRecSet = m_qdQryDef.OpenRecordset()
    While rsRecSet.EOF = False
        sRefNo = rsRecSet.Fields("Refrence")
        sDesc = rsRecSet.Fields("ProductName")

        'Append rowsource here with the found row
        sRowSource = sRowSource & ";" & sRefNo & ";" & sDesc
       
       


        'Now check for space, if space exists, update rowsource with other descriptions
       
        sSql = "INSERT INTO tblProducts (Refrence, ProductName) VALUES ('" & sRefNo & "','" & sDesc & "')"
        DoCmd.RunSQL sSql

       
       
        iPos = InStr(1, rsRecSet.Fields("Productname"), " ")
        If iPos > 0 Then UpdateRowSource sRefNo, sDesc, sRowSource

        rsRecSet.MoveNext
  Wend


rsRecSet.Close
  qdQryDef.Close

End Sub



'Procedure UpdateRowSource
Private Sub UpdateRowSource(ByVal sRefNo As String, ByVal sDesc As String, ByRef sRowSource As String)

Dim qdQryDef As QueryDef                  'Querydef used to create recordset
Dim rsRecSet As dAo.Recordset             'Recordset used to read data
Dim sSql As String
Dim sRowSource As String
Dim iCnt As Integer   'Add this in
Dim iPos As Integer

iCnt = 1 'Add this in


    'Look for entries which have the passed in description but dont pick the one with the passed in refno
    sSql = "SELECT Refrence, ProductName FROM products WHERE Refrence <> '" & sRefNo & "' AND INSTR(1,Productname,'" & sDesc & "') > 0"
    Set qdQryDef = CurrentDb.CreateQueryDef("", sSql)
    Set rsRecSet = m_qdQryDef.OpenRecordset()
    While rsRecSet.EOF = False

       'Append current description with other matching description, split it by a | (pipe symbol)
        sRowSource = sRowSource & " | " & rsRecSet.Fields("ProductName")
If iCnt > m_iMaxColors Then
           'We need to modify table as we have a new color
           sSql = "ALTER TABLE tblProducts ADD Column Color" & iCnt & " Text"
           DoCmd.RunSQL sSql
           m_iMaxColors = iCnt
        End If

        'Add in color - first we need to extract it from the product name
        iPos = InStr(1, rsRecSet.Fields("ProductName"))
        If iPos > 0 Then sDesc = Left(rsRecSet.Fields("ProductName"), iPos) Else sDesc = rsRecSet.Fields("ProductName")

        'Now update that colour field (update the record of the refno passed in)
        sSql = "UPDATE tblProducts SET Color " & iCnt & " = '" & sDesc & "' WHERE RefNo = '" & sRefNo & "'"
        DoCmd.RunSQL sSql
        iCnt = iCnt + 1
        rsRecSet.MoveNext
  Wend
rsRecSet.Close
  qdQryDef.Close
End Sub
0
Comment
Question by:Ricky11
6 Comments
 
LVL 10

Expert Comment

by:Shailesh15
Comment Utility
Assuming... Field first 5 letter are ref no 's & last letter is alphanumeric character for colorcode. Colorcodes are ABCDE

Try running this querry on table

SELECT
left(TableShirts.[REFRENCE NO],5),
max( IIf(Right(TableShirts.[REFRENCE NO],1)='A',TableShirts.DESCRIPTION,'')  ) as col1,
max( IIf(Right(TableShirts.[REFRENCE NO],1)='B',TableShirts.DESCRIPTION,'')) AS col2,
max( IIf(Right(TableShirts.[REFRENCE NO],1)='C',TableShirts.DESCRIPTION,'')) AS col3,
max( IIf(Right(TableShirts.[REFRENCE NO],1)='D',TableShirts.DESCRIPTION,'')) AS col4,
max( IIf(Right(TableShirts.[REFRENCE NO],1)='E',TableShirts.DESCRIPTION,'')) AS col5

FROM TableShirts
group by left(TableShirts.[REFRENCE NO],5)
0
 
LVL 119

Expert Comment

by:Rey Obrero
Comment Utility
CHECK this ;

sRowSource = ""
    sSql = "SELECT Refrence, ProductName FROM products"  'Returns RefNo and Descriptio
    Set qdQryDef = CurrentDb.CreateQueryDef("", sSql)
    Set rsRecSet = m_qdQryDef.OpenRecordset()
----------------------^^^^^^^ this is not define change to

    Set rsRecSet = qdQryDef.OpenRecordset()

Same here

     'Look for entries which have the passed in description but dont pick the one with the passed in refno
    sSql = "SELECT Refrence, ProductName FROM products WHERE Refrence <> '" & sRefNo & "' AND INSTR(1,Productname,'" & sDesc & "') > 0"
    Set qdQryDef = CurrentDb.CreateQueryDef("", sSql)
    Set rsRecSet = m_qdQryDef.OpenRecordset()
----------------------^^^^^^^^ change to

   Set rsRecSet = qdQryDef.OpenRecordset()


Now try again please post the results

Rey:-)


0
 

Author Comment

by:Ricky11
Comment Utility
Not all the refrence no are the same lenght same are long and some are short.. Bascially it should be able to get simialr refrence no. and take the description from each and put them in a new table as i mentioned above..

so
6692A APPLE
6692B BLUE
6692C CREAM
LK108-1C CREAM
LK108-1W WHITE

AFTER I RUN SOME CODE IT SHOULD OUTPUT AS FOLLOWS

6692A APPLE, BLUE, CREAM
LK108-1C CREAM, WHITE

SOMETHING LIKE THAT..
THANKS.
0
Backup Your Microsoft Windows Server®

Backup all your Microsoft Windows Server – on-premises, in remote locations, in private and hybrid clouds. Your entire Windows Server will be backed up in one easy step with patented, block-level disk imaging. We achieve RTOs (recovery time objectives) as low as 15 seconds.

 

Author Comment

by:Ricky11
Comment Utility
Okay 1 thing i rezlied which may have been partly myfault.

 Set rsRecSet = m_qdQryDef.OpenRecordset()
                        ^^^ I remove the "m_" becuase it was not defined at m_ only as qdqrydef.

so after i removed 2 instances of the m_ and ran the code it ran fine with one error because a duplicate variable was defined (Dim sRowSource As String) was defined twice in updaterowsource, so i removed that, and then ran the code again.. it ran fine and no error this time.

I looked at the tblproducts and found this :

Refrence      ProductName      Color1      Color2      Color3      Color4      Color5
49917B      BLACK TABLECLOTH                              
49917G      GREEN TABLECLOTH                              
49917R      ROSE TABLECLOTH                              
89917B      BLUE TABLECLOTH                              
89917G      GREEN TABLECLOTH                              
89917P      PINK TABLECLOTH

Not exacly what we needed, it has just bascially duplicated the products table...

Then i added the other 2 pcs of code you told me today for the indexes, and the result is the same, i.e. tblproducts looks like it does above...

HTH
Thanks.                              
0
 
LVL 65

Accepted Solution

by:
rockiroads earned 500 total points
Comment Utility
0
 

Author Comment

by:Ricky11
Comment Utility
Great. Thanks

I have paritially tested the code and it works.. some small fixes that i will work on shortly..

I am accpeting this answer too..

I will be in touch again

Thanks.
0

Featured Post

How your wiki can always stay up-to-date

Quip doubles as a “living” wiki and a project management tool that evolves with your organization. As you finish projects in Quip, the work remains, easily accessible to all team members, new and old.
- Increase transparency
- Onboard new hires faster
- Access from mobile/offline

Join & Write a Comment

Suggested Solutions

Most if not all databases provide tools to filter data; even simple mail-merge programs might offer basic filtering capabilities. This is so important that, although Access has many built-in features to help the user in this task, developers often n…
Experts-Exchange is a great place to come for help with solutions for your database issues, and many problems are resolved within minutes of being posted.  Others take a little more time and effort and often providing a sample database is very helpf…
Learn how to number pages in an Access report over each group. Activate two pass printing by referencing the pages property: Add code to the Page Footers OnFormat event to capture the pages as there occur for each group. Use the pages property to …
In Microsoft Access, learn the trick to repeating sub-report headings at the top of each page. The problem with sub-reports and headings: Add a dummy group to the sub report using the expression =1: Set the “Repeat Section” property of the dummy…

762 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question

Need Help in Real-Time?

Connect with top rated Experts

9 Experts available now in Live!

Get 1:1 Help Now