adding product qty fields from multiple records to transaction record

[Jesse]

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

 

[kingston via AccessMonster.com]

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]

Thanks for the reply. Problem is, what you describe (if I understand
it) is what I have already - 2 tables with a 1-to-many relationship.
(Actually, I s'pose I have a 1-too-many table situation.)

I need to do what you call the "bad form" approach. And, it is only
to be a limited solution. I need to take the existing data from every
record in the many-table (in my case, "many" is not more than 6) and
get it all into the corresponding record in the one-table with
additional fields. -- Jesse

 

[kingston via AccessMonster.com]

Then you need to create an update query. The data sources will be the One
and the Many tables (properly joined, e.g. by Order ID). The output will be
one of the individual fields from the One and it's Update To: property will
be the corresponding field from the Many table. So, the new field from the
One table will be overwritten with the data from the joined record in the
Many table.

However, you probably don't need to do this at all since you can create a
query (and a report) that presents the data in this format without actually
writing/moving data.

Thanks for the reply. Problem is, what you describe (if I understand
it) is what I have already - 2 tables with a 1-to-many relationship.
(Actually, I s'pose I have a 1-too-many table situation.)

I need to do what you call the "bad form" approach. And, it is only
to be a limited solution. I need to take the existing data from every
record in the many-table (in my case, "many" is not more than 6) and
get it all into the corresponding record in the one-table with
additional fields. -- Jesse

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

[quoted text clipped - 38 lines]