Re: adding product qty fields from multiple records to transaction record

Tech-Archive recommends: Fix windows errors by optimizing your registry

It sounds like you need to implement a one-to-many relationship. IOW, create
a second table for transaction details. The foreign key (FK) would be the
order ID from the main table. Each record in the new table would be for an
actual line item in the order. You could also just add more fields to your
existing table (Product1, Product2, Product3...) to record purchase quantity,
but that would be only a limited solution (and bad form). HTH

Jesse wrote:
I need to take a field value from multiple records in one table and
append those values to the end of an associated single record in
another table. (Or, create a third table.) I am an Access newbie --
having gotten by fine with Excel until now. I have Access 2000.

I've been selling just one product from my webstore, but now that I've
started selling 6 products I have 2 data files to deal with.

I think I have a typical set of data files generated by my shopping
cart software. I get two files: A first file with one record per
transaction and a second file with one record per product code
ordered. So, if someone orders 3 different product codes, in the
second file there will be 3 records.

In order to work well with my postage/label printing software, I need
to get data from multiple records in the second file consolidated into
the single associated record in the first file. (This way I can print
my "pull list" of products right on the shipping/postage label.)

So, for my 6 products I want to add six columns/fields to the
transaction table -- and each one will tell me how many of each of
those six products were ordered (null or >0).

I have no trouble doing multiple flow charts as to how this process
can work. I understand the logic of conditional statements, etc. I
know that I need to set a flag to show that an order is new, clear the
flag after the order has been processed (I can do that manually), and
test for the flag when the data is being processed. But, I haven't
actually worked with databases.

In Access, how do I generate a table with one record per transaction
(sale) that includes fields for the quantities of each product code
ordered?

Any guidance on this would be appreciated. Thanks. -- Jesse

--
Message posted via AccessMonster.com
http://www.accessmonster.com/Uwe/Forums.aspx/access-queries/200611/1

.

Relevant Pages

  • Re: adding product qty fields from multiple records to transaction record
    ... a second table for transaction details. ... transaction and a second file with one record per product code ... second file there will be 3 records. ... know that I need to set a flag to show that an order is new, ...
    (microsoft.public.access.queries)
  • Re: Pervasive MS Cluster Active-Active-Passive works!
    ... The cluster contains 2 resource groups, each containing (besides Pervasive, ... configurable location for the transaction logs). ... The Pervasive.SQL database engine does not know how to "share" data ... own data files then you should be fine, ...
    (comp.databases.btrieve)
  • DB Restore with Transaction File Only
    ... I have a sql where data files and transaction files are ... attach the existing transaction files...? ...
    (microsoft.public.sqlserver.server)
  • XP Pro, VFP5, 1000 M Cards, 100 Router
    ... I am running a compiled App perfectly from each terminal with server based ... data files. ... But when multiple terminals access the data, ... sixth transaction never gets to the inventory file - run it alone, ...
    (microsoft.public.fox.programmer.exchange)
  • [PATCH v3 02/14] async_tx: kill ASYNC_TX_DEP_ACK flag
    ... In support of inter-channel chaining async_tx utilizes an ack flag to ... chain after adding this dependency". ... memcpy depends on the result of this transaction ... parameter to pass to the callback routine ...
    (Linux-Kernel)