multiple records into one record where common field

[gsrai31]

In Access 2000, how can put various records from one table where one field contains same value, into one record adding extra fields. I have attached the example in an attached spreadsheet. First sheet describes what other sheets contain and what I want to achieve. thanks.

 

[WayneRyan]

gsrai31,

How do you know which ones you want to use as the new
"master" record? Earliest date, a specific office?

Need more info ...

Wayne

 

[nickcrisp]

Hi

Providing you only have 1 entry for each office type per envelope you can do this.

First create a reference table with the offices, agents, airports listed with 1 of the four types against each.

Create a group by query that joins the data table with the reference table above on location adn make this a 'group by' query.

Group by the parcel ID.

Then create expressions like these

UKOff: Max(IIf([Type]="Uk Offices",

.[Location],""))

UKOff First tReg: Max(IIf([Type]="Uk Offices",

.[First Reg],""))

UKOff Last Reg: Max(IIf([Type]="Uk Offices",

.[Last Reg],""))

for each type of office comparing it against each type in the reference table.

As the 3 expressions for each type will only have one value in 'Office', 'First Reg' and 'Last Reg', the max function will return this value. (Don't try if you data contains negative values)

The attached database includes the tables and gives the same result as the excel file.

Hope this works ok.

Nick

 

[gsrai31]

Nick

This is brilliant. Thanks for this. Is it possible to select, for the fourth type of office (Foreign Office), any office where Not Equal to the first three types?

Regards
GS

 

[Jon K]

A Totals query Group By chipcard_id can return only one record for each chipcard_id, but in your spreadsheet you required two records for chipcard_id 4000000011.

 

[nickcrisp]

GS

The function 'In' allows you to check to see if a value is in a list.

e.g iif([Text] In ("Apple", "Orange") , true statement, false statement)

You can then test the oppisite by using 'Not' before the 'In'

[Type] Not In ("Uk Offices", "AGENT", "UKAirport"),

Full expression below

ForOff Last Reg: Max ( IIf( [Type] Not In ("Uk Offices", "AGENT", "UKAirport"),

.[Last Reg] ,""))

Where UK Offices, AGENT, UKAirport are the 3 types defined in the reference table.

Can you confirm that each type will only have one entry per parcel.

Nick

 

[gsrai31]

line listing records into one record

Nick
Thank you for this. Yes, at this stage I only want to know where and when last entry took place for each chipcard_id at 4 types of locations. But if I do want to know any other entries, I can add another type for example foreign office 1 and foreign office 2. I can split two offices of one country into two types. And add extra fields as required.
At this stage, I think I am happy with this. Thanks again.
Regards
GS