View last updated record by customer

[eibooo]

Table Name:Customer Status
Fileds:
: SID - (Auto Number)
: CID - (Customer ID is Link to customer Details table's CID)
: Name
: Status (Suspended / Active / Terminated)
: Date

Above are the fields of my customer table and i just wanted to know how to get lastest status of each customer.

Eg:Customer A might have many records such as suspended on 20th may and again active on 25th may like so on...

AND ALSO finally i want to view this information in Customer Details Table form which will include all customer details.

eg: the form will look like this

CID (Customer ID)
Customer Name
Address
phoneNumber
Email
STATUS (LAST UPDATED STATUS FROM CUSTOMER STATUS TABLE)


Hope i have explained well what i need
Pls help me on how to do this...

thanks in advance
ibrahim

 

[wiklendt]

you're better off to make a subform for your customer status - then you can view their entire history while looking at just them - makes for easy judgement on them - e.g., repeat offenders?

to do this, you need to make a form "sfrmCustomerStatus" ("sfrm" means it will be sub form), make it display in datasheet view, then just place it onto your Customers form (which i think you have called "Customer Details Table" - a bit of a misnomer, no? better to call it "frmCustomers") using a subform control (just like you add a combo box, say, but it will insert a form instead). it will ask you what you want to relate between the two (this will be "CID" for yours), and viola. you can get the sub form to display the status records in descending date order, that way the newest will always be on top, and you can, if you want to, scroll down and check out their history.

(whenever you have a PK/FK (1-many) table structure, the "may" table is like a 'sub' table of the "1" table (ie., one customer can have many status records), and so data in the many table is best displayed as a 'sub' form/report)

- and you should always base your forms off a query.

HTH. and post back if you have issues.

 

[eibooo]

thank u so much and it works well

 

[wiklendt]

glad you got it to work and thanks for the feedback, too.