updating a group query

  • Thread starter Thread starter Phil Jones
  • Start date Start date
P

Phil Jones

Can anyone tell me how I can group a set of records together and allow me to
update the table via the query window?

I have over 30,000 records in a table and need to group them by prodName and
description and need to add a prodID to each of these records.

i.e. Table has 30,000 records, when grouped we report 12,000 records and
need to add ID for each of these records, which when the table is viewed as
the full 30,000 records all grouped records would show same ID's

Hope that is clear

Many thanks

Phil
 
Phil,

Essentially you need an Update Query. Then the question becomes, where
does the ProdID come from? Do you have a master list which shows the
ProdID associated with each discrete ProdName? If so, you would create
a query based on your main table and the master list table, joined on
the ProdName field from both. Make it an Update Query (select Update
from the Query menu), and in the Update to row of the ProdID field of
the main table, you put [MasterList].[ProdID] (obviously substitute your
own actual field and table names). If this is off-base, please post
back with some more details of what you've got and what you want.
 
Phil-

As you have discovered, a "Group By" (Totals) query isn't updatable. If you
have 12,000 unique product names, why do you need a Product ID? And why do
you have duplicates? It sounds like the subject of this file is something
other than Product. You should have a separate Products table that links to
this one.

You could try using the Table Analyzer. Select the table in the database
windows and then choose Tools / Analyze / Table. In the fourth or fifth
panel of the Wizard, select No, I want to decide. The Wizard will give you
a graphical interface where you can drag and drop the product name field
into another table and give the table a name. The Wizard will create a
separate Products table with one row per product and generate a unique
numeric ID that links back into your original table. You can change this
from AutoNumber to Long Integer, edit relationships and turn on Cascade
Update, then open the new Products table and assign whatever ID you want one
row at a time -- Access will propogate the new number to the link in your
original table.

--
John Viescas, author
"Microsoft Office Access 2003 Inside Out"
"Running Microsoft Access 2000"
"SQL Queries for Mere Mortals"
http://www.viescas.com/
(Microsoft Access MVP since 1993)
http://www.deanforamerica.com/site/TR?pg=personal&fr_id=1090&px=1434411
 
Cheers Guys for the reply.

I know this not the normal method, but I have to produce a single csv file
to import into an EPoS system.

The duplicate prodName and Description is because the products are part of a
matrix system.

This means the product has a number of sizes and colours to it, each row
represents a colour or size option.

The purpose of the prodID is when the file is imported it looks for the
prodID and just imports the first prodName and decription and applies the
rest as product options.

Hope that explains the reason and I will try your solution and let you know

Thanks
Phil


Steve Schapel said:
Phil,

Essentially you need an Update Query. Then the question becomes, where
does the ProdID come from? Do you have a master list which shows the
ProdID associated with each discrete ProdName? If so, you would create
a query based on your main table and the master list table, joined on
the ProdName field from both. Make it an Update Query (select Update
from the Query menu), and in the Update to row of the ProdID field of
the main table, you put [MasterList].[ProdID] (obviously substitute your
own actual field and table names). If this is off-base, please post
back with some more details of what you've got and what you want.

--
Steve Schapel, Microsoft Access MVP


Phil said:
Can anyone tell me how I can group a set of records together and allow me to
update the table via the query window?

I have over 30,000 records in a table and need to group them by prodName and
description and need to add a prodID to each of these records.

i.e. Table has 30,000 records, when grouped we report 12,000 records and
need to add ID for each of these records, which when the table is viewed as
the full 30,000 records all grouped records would show same ID's

Hope that is clear

Many thanks

Phil
Click to expand...
Click to expand...
 
Phil,

Thanks for the further explanation, and it does indicate that your table
design is flawed. I know that is not your central question, but if you
are interested in reviewing this aspect you could post back with more
details of your tables/fields, and someone will be able to advise.
 
Back
Top