Working with flat files

[Ted Giles]

I am importing a flat file which is supplied as a list of data;
Example

Road Name
House Number
Number of residents in the House
Number of occupants in the Road

On a single form, I need to display - by Road - the House Numbers and number of occupants in each house as well as the summary of occupants for the Road.

Example

Cornwall Road
House 1
Residents 3
House 2
Residents 4

Road Total = 7

Ideally, the form will be in 2 sections, the Road selector at the top and the House metrics below, so each road will have all of the details shown in the lower panel.
Silly thing is that I can do it if the tables were separate, but I am struggling with a simple flat file which I don't want to split into two for various reasons.

Any ideas?

[ljwilson]

Ted, I would try to normalize the data into two tables, one of which could be an imdb table depending on size. If you are showing on a form the road and then the children of it, and the number of children is relatively small then an imdb table for the children is a possibility. Creating two classes for each of them is also a possibility, using a master query on the primary file for each to logically divide them into parent/children.

If the number of records is in the thousands, I would simply create two other db tables on the backend and load the primary file into them as needed, but you say you don't want to do that. If the reason is you don't have control over the backend database then you could create your own sqlite db stored on the server and use it instead for the additional tables.

...jack

[Ted Giles]

I will try creating 2 tables, Jack.
I am responsible for the extract, but it makes more work.
Still, that might be the best option.

[john w]

If the flat file works for various reasons. I would think you could split the form create two panels. Then as may be appropriate drag the table to the top panel and hide those fields you do not want to show. Repeat for the bottom panel. I suspect you would not need to relate the panels. The same rows, or parts of the row, show both places.

Sums should be straight forward.