Adding a Field to an existing table to add a new value

[PaulCL]

Hello,
How do I go about adding a field, to an existing table, that I can use to designate the highest value among a set of duplicate id's in a different field.
Thanks

 

[Joe4]

If it is a native Access table, you go to Design View of the table, you can add new fields.

However, the general rule of thumb is that you should never store a value in a table which can be calculated in a query. It ruins the dynamic nature of the database and violates the rules of normalization.
There usually isn't a reason to, as you can use a Query for just about anything that you can use a Table for.

 

[PaulCL]

If it is a native Access table, you go to Design View of the table, you can add new fields.

However, the general rule of thumb is that you should never store a value in a table which can be calculated in a query. It ruins the dynamic nature of the database and violates the rules of normalization.
There usually isn't a reason to, as you can use a Query for just about anything that you can use a Table for.[/]

ThanksJoe,

What would the query look like if my table looks like this. I want to add the text "HQ" for any duplicate ID's i.e.
Firm ID Volume Added Field

AK 1,000
AL 10,000
AL 19,000 HQ
AL 8,000
AR 5,000
AZ 20,000
CA 50,000 HQ
CA 45,000

 

[Joe4]

OK. I would do this in a series of two queries. The first, which returns all duplicate Firm IDs, and their maximum.
If your table was named "Table1", it would be an Aggregate Query that looks like this:

SELECT Table1.[Firm ID], Count(Table1.[Firm ID]) AS [CountOfFirm ID], Max(Table1.Volume) AS MaxOfVolume
FROM Table1
GROUP BY Table1.[Firm ID]
HAVING (((Count(Table1.[Firm ID]))>1));

Let's name this query MaxVolume.

Now, link this query back to your original table in a new query, and you can get what you want like this:

SELECT Table1.[Firm ID], Table1.Volume, IIf(Nz([CountOfFirm Id],0)<>0,"HQ","") AS [Added Field]
FROM Table1 LEFT JOIN MaxVolume ON (Table1.Volume = MaxVolume.MaxOfVolume) AND (Table1.[Firm ID] = MaxVolume.[Firm ID]);

 

[PaulCL]

OK. I would do this in a series of two queries. The first, which returns all duplicate Firm IDs, and their maximum.
If your table was named "Table1", it would be an Aggregate Query that looks like this:

SELECT Table1.[Firm ID], Count(Table1.[Firm ID]) AS [CountOfFirm ID], Max(Table1.Volume) AS MaxOfVolume
FROM Table1
GROUP BY Table1.[Firm ID]
HAVING (((Count(Table1.[Firm ID]))>1));

Let's name this query MaxVolume.

Now, link this query back to your original table in a new query, and you can get what you want like this:

SELECT Table1.[Firm ID], Table1.Volume, IIf(Nz([CountOfFirm Id],0)<>0,"HQ","") AS [Added Field]
FROM Table1 LEFT JOIN MaxVolume ON (Table1.Volume = MaxVolume.MaxOfVolume) AND (Table1.[Firm ID] = MaxVolume.[Firm ID]);

Thanks, I will give it a try.

 

[PaulCL]

What would the queries look like as excel query in a spreadsheet with the same data? Thanks

 

[Joe4]

I don't know. I don't do database queries in Excel. I do all mine in Access.