Multiple records in one form 2

[Mile0]

My user searches her database by the clients last name, but we have more than one (non-duplicate) record for the client. How do I show all of the appropriate client records on one screen? Better still how do I show just the information that is different?

 

[gallas]

Hi Mile0,

I'm not 100% sure what you mean. But it could be that your data model is not correct. To simplify I would have all the client data in one table and the other data in other tables. eg. All the clients names, addresses, and tel numbers etc in one table and all the different quotes/projects/orders or whatever for each client in another table. Then, on the main form have all the client's details, and in a subform all the different quotes/projects for the client. The main form and subform would have master-child linked fields. Then when your user filters on the client the relevant different quotes/projects will appear in the subform.

If you are feeling adventurous you could put all your subforms on a set of tab controls on the main form.

But maybe I've completely missed your point.%-)

G.

"If a job's worth doing, it's worth doing twice!"

 

[Mile0]

Well, I don't think I have ever created a subform. I didn't even know they existed until I started reading this FAQ's in the Access related areas. So I don't know really what they do. I am an old dBase IV person and used to coding directly and putting everything on one screen form.

My database comes from a weekly updated list sent by a third party. The list includes all of the old information and new information is not distinguished. Various fields are updated, new clients are added and the whole thing is sent to me weekly. Unfortunately, my actual activities rely on me knowing the details of clients who also have fallen off of the weekly list. This means I must compound the lists in Access, remove the duplicates (another problem) and perform some calculations on the screen form to help my user differentiate among a number of similarly named clients.

I could create a seperate database for clients and their base information and hook that to a table that showed their varible information, but then I have to use this weekly text file to update my information.

If I was in dBase I would code it by opening two databases, parsing the weekly text file, identify the client, test the varible data for changes and update the appropriate file as necessary.

Have I confused things enough?

JCB

 

[gallas]

Well, in principle you could build a new normalised database with all the relevant tables, etc. Then every week import and append the new data. This could still work with multiple tables in your main database. It would require append/update queries that append/updated the correct tables. You would probably need some VBA code to sort out duplications and flag up new entries, etc. An interesting project! There are a lot of ways to 'skin this cat'. Hope this helps.
G.

"If a job's worth doing, it's worth doing twice!"

 

[willir]

MikeO

This could be simple or a bear depending on the design and the consistency of the external data. And how much info are we talking about. Regardless, since this is a routine excercise, I think you are looking at developing a maintenance form and / or filtering or import program.

When reviewing / importing / coallating data from multiple datases, say different contact databases, I create two subforms -- one for the "live" contact info, and one for the "external" info. I will have to taylor subform for the new data to adjust for any database differences. In your case, since I expect you will be getting the external data in a consistent form, I hope this will not be necessary.

I then scroll through the external records. With each new record, logic for the on current event tries to find the "live" record using various searches -- phone number, name, address.

If a full match is found -- name matches, phone number matches, address matches, etc.

If a reasonable match is found, then differences are highlighted. The user then can click a button to update the "live" record if required.

If no match is found, a couple of combo boxes on the main form allow me to manually search the live data for a match (variation on the spellings, etc).

And another button allows the me to insert external record into the new record.


For larger messed up contact databases, I have written a verification / parsing program, probably similar to your dbase idea, that goes the external database, and tries to find the appropriate match, and then uses a best guess to update the live database -- basically, I try to automate the manual process. Statistically, I have got this routine to about 98 to 99% accurate.

Here the external data is written to a temp table. I then edit the temp table and use it to update the live data.


Another variation...
We have a asset manager utility program on the desktop that takes an inventory of the software and hardware on each desktop. It generates over 150,000 records for software.

The reports were overwhelming to management due to different versions, different spellings, etc. So I created a "live" database used for running reports with about 700 records. This database links to the raw data using a joining or intermediatory table. It uses the actual application name from raw data and links to applicaiton master file using the autonumber application ID. This way, several records from the raw data can point to one live data.

The raw data is created very couple of weeks. I run a program that runs through the raw data looking for matches in the joining table. If one is not found, a record is created in the joining table and a new application master record is created in the live data.

Since the name rarley changes for on the raw data for existing records, relatively few updates are encountered.

The operator can then review the generated log and make any appropriate corrections. Although this is not using a contact database, I defintely prefer this last approach since the live data remains intact, and is fully normalized. But it requires for the external data to be consistent.


In review
I have used three variations to "match" new data from external sources. The external data is imported into the "live" data which has been normalized. Flipping through each record by record is tedious, and I only use this approach as a one time shot when porting to a new and improved database. Although it is tedious, it is still the best approach when you have little control with the external data.

For routine and ongoing updates, I create a filtering / parsing program to match external data to the live database. This took a bit more coding, especially when trying to match variations with names, phone numbers and addresses.

Hope this late night rambling helps.

Richard

 

[Mile0]

Looks like I have a lot to learn.
Just to start off my education, how can I code more than one record on a screen form? So that my user sees:

Blow, Joe 2323 Main Street Aug-10-1927 Eligible
Blow, Joseph 3223 Main Street Feb-09-1977 Ineligible
Blow, J. 1515 Oak Street Jan-22-2001 Formerly Eligible

and chosing one expands a new screen with further information?

 

[Mile0]

Looks like I have a lot to learn.
Just to start off my education, how can I code more than one record on a screen form? So that my user sees:

Blow, Joe 2323 Main Street Aug-10-1927 Eligible
Blow, Joseph 3223 Main Street Feb-09-1977 Ineligible
Blow, J. 1515 Oak Street Jan-22-2001 Formerly Eligible

and chosing one expands a new screen with further information?

MileZero

 

[willir]

Mile0

You can use a continuous form.

Create a normal (single) form the full record and what else is appropriate.

Then create a continuous form to (briefly) display the names, etc. Place a command button next to the name, on the right or left side. Use the command button wizard to open a single form, specify you want to find a record and then use the proper key field to link the forms. For example, ContactID.

Hints...
- The default size of the command button is huge for this purpose. Select a small image, such as Binnocular 2, and then shrink the command button so that just the image shows. The button should be square, and about the height of a text box.
- You can place more than one command button on the continuous form. One takes you to the contact info. Another takes you to the elibility screen, etc.

Richard

 

[gator9]

Hey Mile

Here is a script that I use now trick is to us a list box my list box name is projectlist then put the list box on a for with a text box and unbound with a comand button. Now when you type your search and hit the search command button it will find all records and list them in the list box. then you click on the name below and it will show the record. Hope I made since

txtcriteria is: that is the text field name where you type what you are searching for.

TxtFind is: that is the command button name.

projectlist is: that is the list box name


###################

Private Sub TxtFind_Click()
Dim strSQL As String, strOrder As String, strWhere As String
Dim dbNm As Database
Set dbNm = CurrentDb()

strSQL = "SELECT Projects.ProjectID, Projects.ProjectName " & _
"FROM Projects"
strWhere = "WHERE"
strOrder = "ORDER BY Projects.ProjectID;"

' set where clause conditions

If Not IsNull(Me.ProjectID) Then
strWhere = strWhere & " (Projects.ProjectName) & (Projects.ProjectID) & (Projects.SSN) Like '*" & Me.txtcriteria & "*' AND"
End If

strWhere = Mid(strWhere, 1, Len(strWhere) - 5) ' remove ' and'

Me.projectlist.RowSource = strSQL & " " & strWhere & "" & strOrder
End Sub

Sincerely,

Charles

http://gator9.com

 

[Mile0]

Thanks Gator9:

That looks like good information. Manipulating this data is an enormous problem for me.

I think part of my problem is understanding the basic essentials of Access. My approach is to slurp all of the data into a single table and then crunch away. Crunching I formerly did with code in a program now must be tucked inside of an object that I am unfamiliar with.

I will use your code to see if I can get a handle on things. Thank you for responding.

Black Eagle at Mile 0