Imported Data usiing ODBC

[Guest]

I import data from an external database using ODBC. Into tables. Access is
used as a report writer. I have no control over the way the data is imported
into the tables. The problem is some of the tables have been set up to import
multiple lines of text as individual records. i.e. Table A has the main data
about the record. Primary being the record numer entry. Table B has the same
primary Key with 12 records for each line of text. (There are ten tables like
this) They have to be joined together in a query to get the relevent data for
reports. When a report is generated I have multiple lines for each Record
from table A. The more tables I join the worse the report looks. I have hid
Duplicates in the report. It helps. Is there a way of taking each record in
table B and creating a single line of text (memo). I hope this makes sense.
The way its set up is a nightmare to get a decent report

 

[Guest]

Hi Mr Sock,

If I understand you correctly, yes, there is a way if Table B is a child
table to Table A (ie. related 1:M). Please check out the following Knowledge
Base article. Note that it applies equally well to Access 2002 and 2003 (and
likely Access 97, as well):

ACC2000: How to Concatenate Data from the 'Many' Side of a Relationship
http://support.microsoft.com/?id=210163


Tom Wickerath
Microsoft Access MVP

http://www.access.qbuilt.com/html/expert_contributors.html
http://www.access.qbuilt.com/html/search.html
__________________________________________

 

[Pat Hartman \(MVP\)]

Tom's suggestion to concatenate lists of items is a useful solution to some
problems of this type. If you want to show more than a single field from
the child tables, you might have better luck using sub-reports.

Just because a table has several tables related to it 1-m doesn't mean that
you can join all the tables in a single query and obtain a recordset that
makes sense. In a query you can have only a single 1-m join unless the
relationship is hierarchial. For example Client-Order-OrderDetails would
work fine. However Student-Class-Parents would not because although Class
and Parents are both related to Students, they have no relationship to each
other. To create a report that showed this information, you would create a
main report that showed Student information and two subreports. One for
classes and the second for Parent contact information. To depict the
hierarchial relationship, you could use a single query and hide duplicates
but again subreports would solve many problems but in this case you would
have a main report showing Client info with a single subreport showing Order
info. The order subreport would have its own subreport showing OrderDetails
so instead of being side-by-side as with the student example, the Orders
example would show nested subreports.

 

[Guest]

Hi Tom,
Do not think concatenate will work in this instance. (The import database
treats the input information as 12 individual lines of text. with the record
number tagged on to each line) The data is exported into table B as 2 fields
(both text) 'number' and 'description' (field lenght 60) it creates a record
for every line of text (including blanks). i.e
Line 1;00101: 'received 10/09/06'. Line 2;00101;'moved to location xyz'

 

[Guest]

I'm not understanding your example:

Line 1;00101: 'received 10/09/06'. Line 2;00101;'moved to location xyz'

Of this data, which portion is in the text field named "Number", and which
is in the text field named "Description"? What field(s) are you currently
joining the tables on? By the way, both of those names are reserved words,
which should be avoided when naming anything in Access:

http://allenbrowne.com/AppIssueBadWord.html


Tom Wickerath
Microsoft Access MVP

http://www.access.qbuilt.com/html/expert_contributors.html
http://www.access.qbuilt.com/html/search.html
__________________________________________