Go Back   Access World Forums > Microsoft Access Discussion > Queries

 
Reply
 
Thread Tools Rate Thread Display Modes
Old 08-27-2019, 03:45 AM   #1
mikej0077
Newly Registered User
 
Join Date: Aug 2019
Posts: 15
Thanks: 0
Thanked 0 Times in 0 Posts
mikej0077 is on a distinguished road
Is there an Access genius out there that can solve this problem?

So I don't think this can be done... but then I am just a lowly novice when it comes to access. Is there one out there that can solve this seemingly impossible challenge?

So I am trying to upload our products on to shopify. After a lot of effort I have come so close, but there is one last obstacle that I have not been able to find a time efficient solution to.

We have over 12,000 products in the database from our main supplier. They post a stock feed online every day, which I want to download, put into our database and be able to generate the upload csv for shopify.

The problem:
All of the variations of a product in our database, have their own row, and own unique product code.
In the upload template for shopify, (found at help.shopify.com/en/manual/products/import-export/using-csv#get-a-sample-csv-file with a guide for the table), They have a separate row for the parent product, and then all the variants have the same 'handle' as the parent.
Fields that need to be filled in on the parent, but left blank in the variants:
Title
Tags
Body (HTML)
Vendor

I have provided a copy of our database, with some sample data. As you can see the variants have been done differently depending on brand/manufacturer.

As far as I can see this will need to be done manually for each record... I'm really hoping that there is a genius out there that can suggest a better/quicker way of doing it.
Attached Files
File Type: accdb Database1.accdb (812.0 KB, 8 views)

mikej0077 is offline   Reply With Quote
Old 08-27-2019, 05:07 AM   #2
Uncle Gizmo
Nifty Access Guy
 
Uncle Gizmo's Avatar
 
Join Date: Jul 2003
Location: Newbury Berks UK
Posts: 10,270
Thanks: 525
Thanked 926 Times in 878 Posts
Uncle Gizmo is a jewel in the rough Uncle Gizmo is a jewel in the rough Uncle Gizmo is a jewel in the rough
Send a message via Skype™ to Uncle Gizmo
Re: Is there an Access genius out there that can solve this problem?

I think you would be better off looking at VBA, queries like that with mind numbingly complicated IIf statements are best avoided!
__________________
Code:
                 |||||
               @(~^~)@
-------------oOo---U---oOo-------------
|                                     |
|      Uncle Gizmo              |
|                                     |
|                                     |
| Get $20 worth of "Nifty Code"       |
|      
To view links or images in signatures your post count must be 10 or greater. You currently have 0 posts.
| | Ooo | |_________________ooO____( )________| ( ) ) / \ ( (_/ \_)
Uncle Gizmo is offline   Reply With Quote
Old 08-27-2019, 06:09 AM   #3
The_Doc_Man
Happy Retired Curmudgeon
 
Join Date: Feb 2001
Location: Suburban New Orleans, LA, USA
Posts: 14,341
Thanks: 87
Thanked 1,641 Times in 1,523 Posts
The_Doc_Man is a splendid one to behold The_Doc_Man is a splendid one to behold The_Doc_Man is a splendid one to behold The_Doc_Man is a splendid one to behold The_Doc_Man is a splendid one to behold The_Doc_Man is a splendid one to behold The_Doc_Man is a splendid one to behold
Re: Is there an Access genius out there that can solve this problem?

Divide and conquer this one.

First, build an empty table with the required data types and column names. Erase its contents before each run. (More about this later.)

Then run a sequence of APPEND queries, each of which focuses on the correct transitions and computations and formatting for one and only one individual type of thing. Yes, having multiple queries is a pain in the toches, but it will also be simple to build, simple to maintain. No IIF needed anywhere if you instead focus via a narrowly selected WHERE clause. Yeah, you have to write a lot of queries. But you will be writing only very simple queries with simple WHERE clauses. You DON'T CARE if you have fifty of them once you have fifty of them done. (And you probably don't have as many as 50 to be written.)

Build a code module that activates each query in your sequence, with error checking and any other bells and whistles you need. In fact, since you import your data, you can easily include that import process (and any "data massaging") as part of the sequence.

When that code blip is finished, your previously empty table is good to go.

Now, as to erasing a table frequently: There is a down side to this approach that can be fixed by more work in the automation or by requiring occasional manual intervention. When you erase a table (or when you update it), you cause deleted records to start accumulating, which leads to database bloat. Bloat means that your db size grows even though you might have the same number of active records today as you had last week. This happens and is a part of every database system I've ever seen including Paradox, SQL Server, Access, Informix, and ORACLE.

To counteract this bloat, you need to occasionally perform a process of compacting and repairing the db. That gets rid of the obsoleted records marked as "deleted." If you do this manually, you need to make a backup copy of the db BEFORE you attempt the C&R because, well... stuff happens. The backup copy made beforehand lets you just toss the damaged copy and start another C&R.

However, there is another approach that programmatically is harder but which prevents the bloat from being an issue. This table used for gathering records is actually a scratch table that has only one purpose in its existence - a staging area from which a single export will occur. There IS such a thing as making an empty version of this table in a third file that you will eventually use as a back end. (Bear with me here...)

When launching the db, make a batch file to delete a working copy of that third file and make a new version of that file from the empty (and virgin) template file. The virgin copy has zero bloat. You CAN statically link to the working copy from the FE. You can erase this copy and replace it without harm because native access linked tables operation through file specs, not internal file ID numbers. Therefore, if you replace the file using the same name, the FE doesn't know the difference.

So... launch via a script. Have the script maintain this working third file, after which the same script can launch access when all files are ready. You'll see a momentary blip while the file stuff happens, but that will be quick because it is purely internal to your workstation, no network involved.

__________________
I'm a certified grandpa (3 times now) and proud of it.
Retired over one year and survived being home all day with the wife. She must really love me.
If I have helped you, please either click the thanks or click the scales.
The_Doc_Man is offline   Reply With Quote
Old 08-27-2019, 07:22 AM   #4
mikej0077
Newly Registered User
 
Join Date: Aug 2019
Posts: 15
Thanks: 0
Thanked 0 Times in 0 Posts
mikej0077 is on a distinguished road
Re: Is there an Access genius out there that can solve this problem?

Quote:
Originally Posted by The_Doc_Man View Post
Divide and conquer this one.

First, build an empty table with the required data types and column names. Erase its contents before each run. (More about this later.)

Then run a sequence of APPEND queries, each of which focuses on the correct transitions and computations and formatting for one and only one individual type of thing. Yes, having multiple queries is a pain in the toches, but it will also be simple to build, simple to maintain. No IIF needed anywhere if you instead focus via a narrowly selected WHERE clause. Yeah, you have to write a lot of queries. But you will be writing only very simple queries with simple WHERE clauses. You DON'T CARE if you have fifty of them once you have fifty of them done. (And you probably don't have as many as 50 to be written.)

Build a code module that activates each query in your sequence, with error checking and any other bells and whistles you need. In fact, since you import your data, you can easily include that import process (and any "data massaging") as part of the sequence.

When that code blip is finished, your previously empty table is good to go.

Now, as to erasing a table frequently: There is a down side to this approach that can be fixed by more work in the automation or by requiring occasional manual intervention. When you erase a table (or when you update it), you cause deleted records to start accumulating, which leads to database bloat. Bloat means that your db size grows even though you might have the same number of active records today as you had last week. This happens and is a part of every database system I've ever seen including Paradox, SQL Server, Access, Informix, and ORACLE.

To counteract this bloat, you need to occasionally perform a process of compacting and repairing the db. That gets rid of the obsoleted records marked as "deleted." If you do this manually, you need to make a backup copy of the db BEFORE you attempt the C&R because, well... stuff happens. The backup copy made beforehand lets you just toss the damaged copy and start another C&R.

However, there is another approach that programmatically is harder but which prevents the bloat from being an issue. This table used for gathering records is actually a scratch table that has only one purpose in its existence - a staging area from which a single export will occur. There IS such a thing as making an empty version of this table in a third file that you will eventually use as a back end. (Bear with me here...)

When launching the db, make a batch file to delete a working copy of that third file and make a new version of that file from the empty (and virgin) template file. The virgin copy has zero bloat. You CAN statically link to the working copy from the FE. You can erase this copy and replace it without harm because native access linked tables operation through file specs, not internal file ID numbers. Therefore, if you replace the file using the same name, the FE doesn't know the difference.

So... launch via a script. Have the script maintain this working third file, after which the same script can launch access when all files are ready. You'll see a momentary blip while the file stuff happens, but that will be quick because it is purely internal to your workstation, no network involved.
Thanks for this. I will do my best, but I'm still a bit of a novice. I don't suppose there's any chance you could do an example of what you mean, and I can try to use that to figure out how to do the whole lot?
mikej0077 is offline   Reply With Quote
Old 08-27-2019, 08:06 AM   #5
MajP
Newly Registered User
 
Join Date: May 2018
Location: USA baby
Posts: 1,782
Thanks: 34
Thanked 536 Times in 509 Posts
MajP will become famous soon enough MajP will become famous soon enough
Re: Is there an Access genius out there that can solve this problem?

I do not really understand the issues. How do you tell a Parent and a variant? Can you give an example. Is your body query working as needed? Is it complete? It could be simplified quite a bit using a user defined function, but it appears to do what you need. Can you explain this in more detail, and maybe provide an example of what you have and what you would want.
MajP is online now   Reply With Quote
Old 08-27-2019, 10:15 AM   #6
MajP
Newly Registered User
 
Join Date: May 2018
Location: USA baby
Posts: 1,782
Thanks: 34
Thanked 536 Times in 509 Posts
MajP will become famous soon enough MajP will become famous soon enough
Re: Is there an Access genius out there that can solve this problem?

Do not think this answers your question, but this could potentially have saved you hours and hours of work. Those queries are amazing that you could get them to work. Try this code and in a query. May need some tweeking, but appears to give the same results as your Body.
Code:
select GetHTML([ProductCode]) as Body from [Product Data]
Code:
Public Enum TagType
  P_BR = 1
  P_P = 2
  end_P = 3
  end_BR = 4
End Enum

Public Function GetHTML(ProductCode As String) As String
  Dim rs As DAO.Recordset
  Dim fld As DAO.Field
  Dim comp As String
  Dim i As Integer
  
  Set rs = CurrentDb.OpenRecordset("Select * from [Product Data] where ProductCode = '" & ProductCode & "'")
  
  GetHTML = Tag(rs!ShortDescription, P_BR) & Tag(rs!longDescription, end_P)
  
  For i = 11 To 66 'these are the COMP fields
    'Debug.Print rs.Fields(i).Name
    If Not IsNull(rs.Fields(i).Value) Then
      comp = rs.Fields(i).Name
      comp = Trim(Replace(comp, "comp", ""))
      comp = StrConv(comp, vbProperCase)
      comp = comp & " - "
      comp = comp & rs.Fields(i).Value
      comp = Tag(comp, end_BR)
      'Debug.Print comp
      GetHTML = GetHTML & comp
    End If
  Next i
  If Right(GetHTML, 4) <> "</p>" Then GetHTML = GetHTML & "</p>"
  For i = 67 To 73
    If Not IsNull(rs.Fields(i).Value) Then
      comp = rs.Fields(i).Value
      comp = Tag(comp, P_P)
      Debug.Print comp
      GetHTML = GetHTML & comp
    End If
  Next i
  If Right(GetHTML, 4) <> "</p>" Then GetHTML = GetHTML & "</p>"
End Function

Public Function Tag(ByVal Text As Variant, TagType As TagType) As String
  Const P = "<p>"
  Const endP = "</p>"
  Const BR = " <br />"
  Text = Nz(Text, "")
  Select Case TagType
  Case P_BR
    Tag = P & Text & BR
  Case P_P
    Tag = P & Text & endP
    Debug.Print Tag
  Case end_P
    Tag = Text & endP
  Case end_BR
    Tag = Text & BR
  End Select
End Function
MajP is online now   Reply With Quote
Old 08-27-2019, 10:23 AM   #7
arnelgp
error reading drive A:
 
arnelgp's Avatar
 
Join Date: May 2009
Location: somewhere out there
Posts: 8,357
Thanks: 67
Thanked 2,682 Times in 2,568 Posts
arnelgp is just really nice arnelgp is just really nice arnelgp is just really nice arnelgp is just really nice arnelgp is just really nice
Re: Is there an Access genius out there that can solve this problem?

the link says the Body (HTML) can also be a plain text.

__________________
"Never stop learning, because life never stops teaching"
arnelgp is offline   Reply With Quote
Old 08-27-2019, 10:45 AM   #8
mikej0077
Newly Registered User
 
Join Date: Aug 2019
Posts: 15
Thanks: 0
Thanked 0 Times in 0 Posts
mikej0077 is on a distinguished road
Re: Is there an Access genius out there that can solve this problem?

Quote:
Originally Posted by MajP View Post
Do not think this answers your question, but this could potentially have saved you hours and hours of work. Those queries are amazing that you could get them to work. Try this code and in a query. May need some tweeking, but appears to give the same results as your Body.
Code:
select GetHTML([ProductCode]) as Body from [Product Data]
Code:
Public Enum TagType
  P_BR = 1
  P_P = 2
  end_P = 3
  end_BR = 4
End Enum

Public Function GetHTML(ProductCode As String) As String
  Dim rs As DAO.Recordset
  Dim fld As DAO.Field
  Dim comp As String
  Dim i As Integer
  
  Set rs = CurrentDb.OpenRecordset("Select * from [Product Data] where ProductCode = '" & ProductCode & "'")
  
  GetHTML = Tag(rs!ShortDescription, P_BR) & Tag(rs!longDescription, end_P)
  
  For i = 11 To 66 'these are the COMP fields
    'Debug.Print rs.Fields(i).Name
    If Not IsNull(rs.Fields(i).Value) Then
      comp = rs.Fields(i).Name
      comp = Trim(Replace(comp, "comp", ""))
      comp = StrConv(comp, vbProperCase)
      comp = comp & " - "
      comp = comp & rs.Fields(i).Value
      comp = Tag(comp, end_BR)
      'Debug.Print comp
      GetHTML = GetHTML & comp
    End If
  Next i
  If Right(GetHTML, 4) <> "</p>" Then GetHTML = GetHTML & "</p>"
  For i = 67 To 73
    If Not IsNull(rs.Fields(i).Value) Then
      comp = rs.Fields(i).Value
      comp = Tag(comp, P_P)
      Debug.Print comp
      GetHTML = GetHTML & comp
    End If
  Next i
  If Right(GetHTML, 4) <> "</p>" Then GetHTML = GetHTML & "</p>"
End Function

Public Function Tag(ByVal Text As Variant, TagType As TagType) As String
  Const P = "<p>"
  Const endP = "</p>"
  Const BR = " <br />"
  Text = Nz(Text, "")
  Select Case TagType
  Case P_BR
    Tag = P & Text & BR
  Case P_P
    Tag = P & Text & endP
    Debug.Print Tag
  Case end_P
    Tag = Text & endP
  Case end_BR
    Tag = Text & BR
  End Select
End Function
You're right, that did take me hours! I'm only just learning access so, SQL is a bit beyond me at this moment in time. Thanks for looking at this though!
mikej0077 is offline   Reply With Quote
Old 08-27-2019, 10:47 AM   #9
mikej0077
Newly Registered User
 
Join Date: Aug 2019
Posts: 15
Thanks: 0
Thanked 0 Times in 0 Posts
mikej0077 is on a distinguished road
Re: Is there an Access genius out there that can solve this problem?

Quote:
Originally Posted by arnelgp View Post
the link says the Body (HTML) can also be a plain text.
It can, but I can't put it in a csv to upload as plain text, because everything will be on the same line. Or at least it was the first time I tried.
mikej0077 is offline   Reply With Quote
Old 08-27-2019, 02:03 PM   #10
Uncle Gizmo
Nifty Access Guy
 
Uncle Gizmo's Avatar
 
Join Date: Jul 2003
Location: Newbury Berks UK
Posts: 10,270
Thanks: 525
Thanked 926 Times in 878 Posts
Uncle Gizmo is a jewel in the rough Uncle Gizmo is a jewel in the rough Uncle Gizmo is a jewel in the rough
Send a message via Skype™ to Uncle Gizmo
Re: Is there an Access genius out there that can solve this problem?

Although it's possible to do this sort of concatenation in MS Access, and indeed I've also done it in a Google spreadsheet!

But I've got the distinct impression when I looked into this in the past that a far better way of handling text manipulation like this is to use Python. I understand it has some impressive libraries just for this sort of thing.

Sent from my Pixel 3a using Tapatalk

__________________
Code:
                 |||||
               @(~^~)@
-------------oOo---U---oOo-------------
|                                     |
|      Uncle Gizmo              |
|                                     |
|                                     |
| Get $20 worth of "Nifty Code"       |
|      
To view links or images in signatures your post count must be 10 or greater. You currently have 0 posts.
| | Ooo | |_________________ooO____( )________| ( ) ) / \ ( (_/ \_)
Uncle Gizmo is offline   Reply With Quote
Reply

Thread Tools
Display Modes Rate This Thread
Rate This Thread:

Forum Jump

Similar Threads
Thread Thread Starter Forum Replies Last Post
Genius required - #error in field ryetee Forms 9 02-27-2014 02:20 PM
Really need help to solve a problem martixz General 4 10-29-2013 04:50 AM
Employee Absences - Can Access Solve This Problem ? Arundeln General 2 02-13-2013 11:56 AM
How to solve this problem? Tsung90 Queries 7 04-23-2012 03:10 PM
[SOLVED] rows into columns- only a genius could solve this willhays Forms 2 09-07-2000 06:31 PM




All times are GMT -8. The time now is 07:47 AM.


Microsoft Access Help
General
Tables
Queries
Forms
Reports
Macros
Modules & VBA
Theory & Practice
Access FAQs
Code Repository
Sample Databases
Video Tutorials

Featured Forum post


Sponsored Links


Powered by vBulletin®
Copyright ©2000 - 2019, Jelsoft Enterprises Ltd.
(c) copyright 2017 Access World