Is there a way to do a flat join with an Access table?

[Enigman]

I have an app that manages inventory and sales data. It uses an MS Access database. There are multiple inventory tables and multiple sales tables with exactly the same structure for each type. All inventory is controlled with three part SKU numbers. The parts are a text prefix, a number sequence, and a text suffix. As part of entering new inventory records I must obtain the last number used for a given SKU prefix. My preference would be to join all the inventory tables together, end-to-end, and then run a query on the prefix. Once limited to records matching the prefix I can obtain the highest value for the sequence number and add one.

I know there is a dbpDefineRelationship command, but as far as I can tell that requires one table to be subordinate to the other through a common field and that's not what I want. I want them joined end-to-end like one long table. If I can do that then my process would be to join all inventory tables, join all sales tables, then query each joined set for the highest number for a given prefix, then take the higher value between sales tables and inventory tables and add one to get my next number.

I know this can be done by opening and querying each and every table one at a time, but that's much less efficient.

Does anyone know how to do a flat end-to-end join or if it is even possible with Access?

Thanks.

[Neosoft Support]

I'm not sure if MS Access supports this, but you could try using the SQL Union command (via dbpExecSQL) to create a temporary table containing all of the inventory data. More information about the Union command below:

http://www.w3schools.com/sql/sql_union.asp

You may want to backup your database before experimenting with something like this just in case.

[Enigman]

Thanks for replying.

I'm not sure if MS Access supports this, but you could try using the SQL Union command (via dbpExecSQL) to create a temporary table containing all of the inventory data.

Okay, I'll take that as unions specifically for Access are not directly supported. I don't know how comfortable I am invoking SQL against an Access file. But then, I don't know enough about it to judge. I'd have to do some offline tests.

I did write the table-by-table code and it looks like this;

Code: Select all

SetVar "[SKUNumberTables]" "Inventory_EC|Inventory_PRS|Archive_EC|Archive_PRS|Sales_2014|Sales_2013|Sales_2012|Sales_2011|Sales_2010|Sales_2009|Sales_2008|Sales_2007|Sales_2006|Sales_PRS"
GoSub "invGetNextSKU"



:invGetNextSKU
.......... Check all tables and determine the next sku number
StrParse "[SKUNumberTables]" "|" "[skutables]" "[tablecount]"
SetVar "[skumaxvalue]" "0"

. Setup the progress ruler
Math "[RulerMax] / [tablecount]" "0" "[RulerMove]"
GoSub "RulerShow"

. Loop through all tables
Loop "1" "[tablecount]" "[tableiter]"

     SetVar "[Status]" "Checking [skutables[tableiter]] ..."

     . Create and open a virtual view
     dbpCreateView "db" "[skutables[tableiter]]" "SKUMax"
     dbpOpenTable "db" "SKUMax" ""

     . Find the highest number for the sku prefix
     dbpQuery "db" "SKUMax" "SKUPrefix = [#34][db.Inventory.SKUPrefix][#34]"
     dbpMax "db" "SKUMax" "SKUNumber" "[thismax]"
     IfEx "[thismax] > [skumaxvalue]"
          SetVar "[skumaxvalue]" "![thismax]"
     EndIf

     . Close and drop the virtual view
     dbpCloseTable "db" "SKUMax"
     dbpDropTable "db" "SKUMax"

     . Expand the progress ruler
     IfEx "[tableiter] < [tablecount]"
          GoSub "RulerMove"
     Else
          GoSub "RulerMax"
     EndIf

EndLoop

. Send the next number to the record
Math "[skumaxvalue] + 1" "0" "[db.Inventory.SKUNumber]"

. Shut down the progress ruler
Delay "500"
GoSub "RulerHide"
SetVar "[Status]" ""

Return


The response time right now is a few seconds, but then I don't have the tables fully populated. I only have to do this when I add new SKU numbers which is about six times per year, so I could afford some processing time. I just know that a table union would be more efficient.

Thanks.

[Neosoft Support]

I don't know how comfortable I am invoking SQL against an Access file.

Many of the things DBPro does behind the scenes are done with SQL, so it's not a problem with Access.

[Enigman]

Interesting twist. I looked into using an SQL UNION command, but the NB documentation says that dbpQuery cannot be used on the resulting temporary table. That defeats the whole point of using the UNION command in my current thinking. What I wanted to do was UNION the tables ONE time, then do repeated queries against the UNION temporary table to narrow the records to a specific person, add up their purchases across all sales years, then re-query the UNION temporary table and move on to the next person.

Or, am I thinking about this backwards. Should I be using the SQL UNION as the query to narrow down to each person, and then do the dbpSum on their purchases? That would mean having to do the full UNION command for each iteration of the buyer name loop. The query to narrow down to each person is based on a lot of qualifiers so a UNION statement that includes that same set of qualifiers repeated for each sales year would be ENORMOUS and difficult to maintain and I would think that would be really slow.

As it is now without using UNION, the buyer loop queries each sales table in sequence to get the sum for each year, then it adds the yearly sums into one sum for all years. That works without being too slow, but it is a batch process that takes several minutes.

P.S. - The above is in reference to a different query function than the one I mentioned in the beginning of this thread. The original query was solved by sequentially checking each table. That turned out to be much cleaner than using one ginormous UNION command holding the repeating query statements for all the tables in one long line. My reference above is talking about creating a report on "who bought how much with a given date range" which covers more than one sales table.

[Neosoft Support]

There are multiple inventory tables and multiple sales tables with exactly the same structure for each type.

Why is it necessary to have multiple inventory and multiple sales tables? Couldn't you combine these into single tables and solve the problem? If you need to differentiate items somehow you could add a field to serve as a category. Having all of the data in one table, would make finding what you're looking for much simpler.

[Enigman]

Why is it necessary to have multiple inventory and multiple sales tables? Couldn't you combine these into single tables and solve the problem? If you need to differentiate items somehow you could add a field to serve as a category. Having all of the data in one table, would make finding what you're looking for much simpler.

There are many reasons why I use separate inventory tables and separate sales years.

Both inventory and sales are separated first by business entity, so the inventory is separated by who owns it. The sales tables are separated both by whose sales and then by year. On the surface it would seem like it would be easier to have one big table, but Access performs poorly on very large tables and I have 7 years worth of sale data with several thousand records each. Also, there are only two functions that need to cross sales year tables. There are literally hundreds of functions that rely on seeing only a given year's sales and a specific inventory set. So, if I lumped everything into one hombotious table, then every single button and function would first have to narrow the data down to a specific year or inventory set, and THEN do what it normally does. (Not to mention that date queries are a pain in the backside.) That would multiply my development and maintenance efforts for the sake of only two functions that cross year boundaries.

I already have many differentiations of item types. I have three levels of categories, a sales venue field, inventory type, such as limited, unique or unlimited, and others. It is differentiated every which way, but the year and owner is a concrete barrier that only needs to be crossed by two batch reports, so I opted for the better performance of smaller tables and simpler interactive functions.

By the way, you should document the use of the # character surrounding dates in queries to make them work with operators like > < and =. I coudn't find that anywhere in the help files, or any mention of dates at all in the dbpQuery command help, but I stumbled on it in the online forums for SQL and it works in dbpQuery. Without the #, a query like "SaleDate > 9/30/2013" does not work at all and doesn't generate an error, but if you make it "SaleDate > #9/30/2013#" then it works perfectly.

Oh, and another dbpQuery problem is that if I have a buyer name field, and the actual data for the buyer name contains an apostrophe, such as "John O'Brien", then running a query filtering on that exact name, with the name fed to the query inside a variable, generates a weird error talking about invalid parameterization and data types, and the query returns no records. It is consistent and reproducible, at least in my tables.

Thanks.

[Neosoft Support]

The parts are a text prefix, a number sequence, and a text suffix. As part of entering new inventory records I must obtain the last number used for a given SKU prefix...

What if you keep track of the last numbers used in a separate table? Then you could just query that table when you need a new number.

By the way, you should document the use of the # character surrounding dates in queries to make them work with operators like > < and =...

This is a quirk of Access and other databases work differently, but we can certainly mention this in the help file.

Oh, and another dbpQuery problem is that if I have a buyer name field, and the actual data for the buyer name contains an apostrophe, such as "John O'Brien", then running a query filtering on that exact name, with the name fed to the query inside a variable, generates a weird error talking about invalid parameterization and data types, and the query returns no records. It is consistent and reproducible, at least in my tables.

I wasn't able to reproduce that problem here. I added a record for "John O'Brien" to the sample address book database included with DBPro, but I didn't have any trouble performing a query on that name.

[Enigman]

What if you keep track of the last numbers used in a separate table? Then you could just query that table when you need a new number.

I thought about that initially, but then that means that every function that touches the SKU number would have to be constantly updating the max number table, and in some cases, in order to ensure accuracy, it would have to run a script just like the one I am using now. Also, my SKU numbers have prefixes like "CP" and "ST" and each prefix has it's own set of numbers from 000001 thru 999999 and there are hundreds of prefixes, That would require maintaining hundreds of max numbers accurately. Instead, I can skip all that overhead and just have one button that takes the displayed prefix and searches out the highest number used under that prefix. Much easier.

This is a quirk of Access and other databases work differently, but we can certainly mention this in the help file.

If so, do please add documentation of date usage in general in dbpQuery since there is no mention of dates now. And also add the use of parentheses in setting the execution order of queries. Very useful but not currently mentioned.

I wasn't able to reproduce that problem here. I added a record for "John O'Brien" to the sample address book database included with DBPro, but I didn't have any trouble performing a query on that name.

Don't know what to say. I can turn it on and off all day long. I just went in and added an apostrophe to a name and ran the report and got this error:



The dbpQuery lines used are:

Code: Select all

dbpQuery "db" "Points1" "(Buyer = [#34][buyernames[iter]][#34] AND NOT Notes LIKE [#34]%Reward Code:%[#34] AND NOT Notes LIKE [#34]%KEYSTONECP%[#34]) OR (Buyer = [#34][buyernames[iter]][#34] AND Notes IS NULL)"

dbpQuery "db" "Points2" "(Buyer = [#34][buyernames[iter]][#34] AND NOT Notes LIKE [#34]%Reward Code:%[#34] AND NOT Notes LIKE [#34]%KEYSTONECP%[#34] AND SaleDate > #9/30/2013#) OR (Buyer = [#34][buyernames[iter]][#34] AND Notes IS NULL AND SaleDate > #9/30/2013#)"

The error happens once for each query. Both queries are being used in a loop. "[buyernames[iter]]" is an array slot. The list of names was derived using dbpFieldtoVar, then parsed into the array. I am using records from all dates in the first table, and only those after 9/30/2013 for the second table. (That's when the reward program started)

Maybe the array variable is doing something weird with the embedded apostrophe that Access doesn't like.

... and remember it is not a direct table open, it is a virtual view of the table.

By the way, did you know that virtual views are physically created in the database and left there if the program does not explicitly close and delete the virtual view table? They can survive across program loads.

Thanks.

[Neosoft Support]

Of course, you are the person most familiar with the design of your database and the person best suited to find a solution. It's difficult for any of us here to step in and make recommendations for such a complex project.

Don't know what to say. I can turn it on and off all day long. I just went in and added an apostrophe to a name and ran the report and got this error...

I don't know that there is anything that we can do about that error message. All DBPro does is pass your query as is along to the database engine (in this case MS Access) which does the actual work. The error message comes from Access (or possibly ADO or the ODBC driver). DBPro simply displays the error message. I can say that the presence of an apostrophe in a query by itself will not normally cause an error. However, your query is very complex and is being performed on a virtual view which may behave differently. Again, DBPro doesn't perform any queries on its own. All database processing is done by the database engine whether it's MS Access, mySQL, Oracle, etc. Each does things slightly differently.

By the way, did you know that virtual views are physically created in the database and left there if the program does not explicitly close and delete the virtual view table? They can survive across program loads.

Yes, that's how it's supposed to work. Once created, the view is added to the database's list of tables and acts and behaves exactly like a normal table. A view can be opened with dbpOpenTable, displayed with dbpShowGrid and deleted with dbpDropTable. Deleting a view with dbpDropTable does not affect any of the data which is stored in the source table.

[Enigman]

I don't know that there is anything that we can do about that error message. All DBPro does is pass your query as is along to the database engine (in this case MS Access) which does the actual work. The error message comes from Access (or possibly ADO or the ODBC driver). DBPro simply displays the error message. I can say that the presence of an apostrophe in a query by itself will not normally cause an error. However, your query is very complex and is being performed on a virtual view which may behave differently.

Yeah, that's what I figured. Error messages are shut off in NB so the fact that it popped up kinda points to another origin. It's okay, my workaround is to eliminate apostrophes from names. It doesn't break my heart.

Yes, that's how it's supposed to work. Once created, the view is added to the database's list of tables and acts and behaves exactly like a normal table.

I thought about leaving them intact, but I open so many views under many different names that I thought it would be safer and cleaner to remove them when the program closes or no longer needs temporary views. That makes MDB file backup better and smaller.

Thanks for all your help. DBPro is a great add-on.