On Jul 2, 12:23 am, Webbiz <nos***@forme.thanks.com> wrote:
Show quoteHide quoteThe reason why Im doing all this is that I have 4 different tables in
my database. Now those four tables have very different data but the
common fields in them are Date, ID, Type, Description. So I build a
temporary table (Not CREATE TEMPORARY TABLE because theyre not
supported by Jet), just to get all that data into one table so I can
sort them date-wise. After sorted, I put the data into recordset, then
I dont need the table.
Now alternate approach would be to load every table's data into a
control or array and then use coding to sort it. Well I did bit
testing and I found the sql/database technique more suitable and
hassle-free.

Why building a dedicated table at all - why not defining a View
instead, which Joins and Sorts all your Data from your 4 "real
tables" together - (or instead of a view-definition, why not
just use such a larger SQL-Statement directly).
This way you can retrieve an ADO-Rs without any temporary
steps "in one go".

As just said, that all should be possible with only one correctly formulated
SQL-Select (Join... Order By)-Statement, which is then able,
to deliver your Rs directly.

Maybe just post short Schema-descriptions of your 4 tables -
and what you currently expect/create in that (IMO avoidable) Temp-
Table (which Fields from your other tables - and what sort-order).

Olaf

Show quote Hide quote My purpose of creating table for this was because I couldnt figure out
the right SQL query to run. I tried a lot but couldnt make it up.
Here's my situation:

I have an inventory management (plus various other financial records)
database. The inventory portion consists of following tables:
  *  ProductList
  *  Purchases
  *  PurchaseDetails
  *  Sales
  *  SalesDetails

The ProductList comprises of 100s of products, storing the bar codes,
names, descriptions and cost data. The inventory is being managed on
Perpetual Inventory system (weighted average cost). That is, I need to
store the current Quantity, Total Cost, and Unit Cost (Total /
Quantity), and Selling Price of each product of course.

The Purchases table lists all the inventory purchased day to day. It
basically stores the purchase order list. It has PurchaseOrder number,
Date, reference number for PurchaseDetails table, then other details
such as payment on cash/credit, etc.

Now each purchase order can consist of more than one inventory item.
So obviously I need to store separately, the list of items purchased
in each purchase order. Purchase order numbers, date and etc are
stored in Purchases table. The PurchaseDetails table lists the details
of each order. It has PurchaseOrderNumber, ID (which is autonumber,
used for PurchaseDetails), inventory type, purchase cost, quantity
purchased, discounts, etc. Now whenever a purchase order is raised, it
updates the current status of inventory ProductList table. It updates
the quantity in hand, total cost, and then unit cost.

Same way the Sales portion works. You can see that the ProductList
table defines the inventory, barcodes and current status. The
Purchases records new inventory purchased, and PurchaseDetails is a
related as a child of Purchases, holds details. Same way, Sales
records the sales made, and SalesDetails records the inventory sold
and is a child table of Sales.

OK now the ProductList only holds the current status of inventory. My
objective here is to build a recordset, which enlists all the
purchases and all the sales, of a particular barcode, into one place..
which I can sort date-wise. Therefore I'll have a complete inventory
register for a particular barcode/item. I dont need to create an
actual table for each inventory, as there are hundreds of inventory
item and we're going to expand further more.. its just going to create
stress on database.

I cant figure out the SQL query to run which gives me the solution to
my problem.

I'll repeat in top-down manner.. for example i want to view the the
inventory register of a particular barcode (eg. 1003). The inventory
register does not exist in database, it will be built on runtime using
details extracted from purchases and sales made in respect of 1003
inventory item.

Now I need to the following fields: Date, voucher ID (purchase order /
sales order), Quantity, TotalAmount, UnitCost. The Date and VoucherID
fields are from Purchases/Sales register. The other details of
Quantity, TotalAmount, UnitCost are from the PurchasesDetails/
SalesDetails registers. Now besides extracting data, I am looking for
particular barcode 1003, which is also the field in the -Details
registers.

When I get both the purchases and sales data accumulated under the
desired fields of inventory register, I want to be able to distinguish
which line item is purchases and which is sales. So I want to add
another column to the inventory register which shows this. I call it
Type field. It should store "P" or "S" to mark which is which.

Its a pretty confusing SQL query. I may somehow be able to annex the
two purchases and sales register into one recordset, but then how do I
add another field into it (Type field) which marks which is sale or
purchase.

appending data from purchases and sales into that table. then after
showing it to user, i delete the table. thats it.

Show quote Hide quote Then I'd start with defining two views, which join together
the two related tables ...or maybe three tables, in case the
ProductList is needed too, to join everything nicely, but create
two different views first in either case ...
One view (vw_P_PD) that joins Purchases and PurchasesDetails
and another view (vw_S_SD) which joins Sales and SalesDetails.

Within that two View-Definitions you can already add a
Field called InvType with the content of either 'P' or 'S'...
vw_P_PD:
(Select Date, VoucherID, ..., ... 'P' As InvType From
Purchases Inner Join PurchasesDetails On ...)

vw_S_SD:
(Select Date, VoucherID, ..., ... 'S' As InvType From
Sales Inner Join SalesDetails On ...)

After defining these two Views, you should be able, to do
something like that now:
Select * From
(
  Select * From vw_P_PD Where Barcode = 1033
  Union All
  Select * From vw_S_SD Where Barcode = 1033
)
Order By YourOrderFieldList

Of course that is maybe somewhat "oversimplified" -
but defining some views beforehand, which hide
most of the (intermediate) Statement-complexity
already, will ease your final query to a larger extent.

HTH - if the suggestions do not help you, you should post
a link to a somewhat "reduced" Demo-*.mdb, which
contains a smaller demo-subset - and the queries (the code)
you already tried against that smaller table-structure.
Always a good idea, to post concrete examples - these
are usually much shorter (and easier to read and understand)
than all these longer descriptions in "plain-text", especially when
the problem is a bit more complex - just post some (demo-)
data you currently  have (reduced to the principle) - and the
data-results you want to see finally in your recordset - and a
smaller code-example for what you currently use to achieve that -
or alternatively the currently non-satisfying SQL-Statements
which fail to deliver the desired result.

Olaf

Show quote Hide quote Alright. I will post a blank smaller version of my database online,
give me a day .. Im out of city currently. Then we'll try to come up
with the solution. Thanks a million !

On Jul 3, 5:33 pm, Faraz Azhar <itzfa***@gmail.com> wrote:
Show quoteHide quoteOk .. Ive put my database here:
http://cid-8cc77f39e2f65a74.skydrive.live.com/self.aspx/.Public/DB%20SQL%20Problem.zip

Ive also put my code in a word file to show you how Ive been
accomplishing this.

Show quote Hide quote http://cid-8cc77f39e2f65a74.skydrive.live.com/self.aspx/.Public/DB%20SQL%20Problem.zip

Ok - the above advise is perfectly doable as shown below...

'***Into a Form (add a VB-DataGrid, named DataGrid1 -
'***                    and a reference to ADO to your project)
'***Then click the Form
Option Explicit

Private Cnn As ADODB.Connection

Private Sub Form_Load()
Dim SQL As String
  Caption = "Click the Form"

  Set Cnn = New Connection
  Cnn.CursorLocation = adUseClient
  Cnn.Open "Provider=Microsoft.Jet.OLEDB.4.0;Data Source=c:\db1.mdb"

  'our first view-definition
  SQL = "SELECT Date As dDate, 'P' As Type, VoucherID As Voucher, " & _
               "Quantity, UnitCost, Amount As Total, ProductCode " & _
        "FROM Purchases INNER JOIN PurchasesDetails " & _
        "ON Purchases.VoucherID = PurchasesDetails.PO"
  CreateOrOverwrite_View "vw_P_PD", SQL

  'define the second view now
  SQL = "SELECT Date As dDate, 'S' As Type, VoucherID As Voucher, " & _
               "Quantity, UnitCost, Amount As Total, ProductCode " & _
        "FROM Sales INNER JOIN SalesDetails " & _
        "ON Sales.VoucherID = SalesDetails.SO"
  CreateOrOverwrite_View "vw_S_SD", SQL
End Sub

Private Sub Form_Click()
Dim T As Single
  T = Timer 'let's time our results

    Set DataGrid1.DataSource = GetRsForProductCode(1003)  'visualize it

  Caption = Format$((Timer - T) * 10, "0.00msec") 'timing-output
End Sub

Private Function GetRsForProductCode(ByVal ProductCode&) As Recordset
Dim SQL As String
  With New Command 'a Select using the Union-Operator and a Command-Object
    SQL = "Select * From " & _
          "  (Select * From vw_P_PD Where ProductCode=@PrCode) " & _
          "   Union All " & _
          "  (Select * From vw_S_SD Where ProductCode=@PrCode) " & _
          "Order By dDate"

    Set .ActiveConnection = Cnn
    .CommandText = SQL
    .Parameters("@PrCode") = ProductCode
    Set GetRsForProductCode = .Execute
  End With
End Function

Private Sub CreateOrOverwrite_View(ViewName As String, SQL As String)
Dim ErrNr As Long, ErrDesc As String
  On Error Resume Next
  Cnn.Execute "Create View [" & ViewName & "] As " & SQL
  If Err.Number = &H80040E14 Then 'view already exists
    Cnn.Execute "Drop View [" & ViewName & "]"
    Err.Clear
    Cnn.Execute "Create View [" & ViewName & "] As " & SQL
  End If
  If Err Then
    ErrNr = Err.Number: ErrDesc = Err.Description
    On Error GoTo 0
    Err.Raise ErrNr, , ErrDesc
  End If
End Sub

Olaf

Sorry, forgot to adapt the line:
Caption = Format$((Timer - T) * 10, "0.00msec") 'timing-output

back to:
Caption = Format$((Timer - T) * 1000, "0.00msec") 'timing-output

(... had it running in a small loop (1 to 100) before in my tests...)

But that's only the timing - the rest of the functionality
is not touched by that of course.

Olaf

Removing too many hyphens
Printing contents of a picture box
Package & Deployment
Perplexing Problem - Need Help
Copy & Move files
Remote-Desktop "client/ server"
Date and regional settings problem
Type comparison
Problem with DIR function.....
Need help retrieving a udp response after sending a udp command