The Design of Software (CLOSED)

A public forum for discussing the design of software, from the user interface to the code architecture. Now closed.

The "Design of Software" discussion group has been merged with the main Joel on Software discussion group.

The archives will remain online indefinitely.

Retrieving rows from multiple tables to fill MS Access Report..

Hi,

I am using VB.NET 2003 and MS Access XP for a desktop application. While developing the application we have a reached a situation where we want to print a report which retrieves records from four tables. Till here it is easy to think that it can be done by a simple SQL JOIN query, but following is the complexity:

The first table stores a single row.

The second table stores multiple rows related to the Primary Key field defined in Table One.

The third table stores a single row related to the Primary Key field defined in Table One.

The fourth table stores a single row related to the Primary Key field defined in Table One.

The above SAVE RECORD option is performed when a user fills a Form of my application. As stated above, all the four tables are inter-related with a Primay Key field (TNo) defined in table one.

I also have a MS Access Report that will print information retrieved from all the four tables. The Report has some of the fields from each of the above table. The SAVE operation is performed in this way:

(1) A unique TNo is generated for a new record that is about to be created.
(2) All the entries are saved in their respective tables (mentioned above).
(3) An access query will fetch the records pertaining to this TNo from all the tables to fill the report.

I want to know how to write such a query when I have to fetch multiple rows of a table in between. Is there any way that I can pass the TNO as a parameter to this query that is saved in MS Access?
RK Send private email
Wednesday, September 20, 2006
 
 
Is there a particular reason why the query and report need to be created & stored in the Access database? What about creating and displaying the report within your .NET app? The SQL statement which would have been in your Access query would reside within your .NET app as well, and you could simply add a Where clause to which you would provide the TNo value the user wanted to view the report for.
Tim Send private email
Wednesday, September 20, 2006
 
 
Link below might help to pass parameter to query in Access/Jet mdb.  Describes what I think is little-known ability of Jet to do something like stored procedures:

http://support.microsoft.com/default.aspx?scid=http://support.microsoft.com:80/support/kb/articles/Q202/1/16.asp&NoWebContent=1
Herbert Sitz Send private email
Wednesday, September 20, 2006
 
 
Should have added: another alternative is just to connect to Access via COM and set parameters of query that way.  Or do something like this:

http://www.codecomments.com/message216650.html
Herbert Sitz Send private email
Wednesday, September 20, 2006
 
 
Perhaps it would be better to retrieve the single rows in Table 1, 3 and 4 into arrays...
Ezani Send private email
Wednesday, September 20, 2006
 
 
Where's Albert Kallal when he's needed? This one is right up his street.

RK: yes, you can pass the TNO as a parameter to a stored query in Access. Look in Access help for "Parameter queries".  If you are developing your front end in Access then you can also apply filters programatically when opening a report. Look in the Access help, and also check out some of the handy examples and code snippets at http://www.mvps.org/access/

btw, your query shouldn't be too tricky. If I understand you right, you have two one-to-one relationships (T1:T3, T1:T4) and a one-to-many relationship (T1:T2). Build your query with inner joins, and then make use of the grouping properties in the report (group by T1 PK then T2 PK) so that information from T1, T3 and T4 appear once, and the records from T2 appear as many times as you need them.

Have fun
Bunch of Access
Thursday, September 21, 2006
 
 
Alternatively, have a main report working off T1, T3, T4, and use a sub-report for T2. Sub-reports can be quite handy...check out the Access help
Bunch of Access
Thursday, September 21, 2006
 
 
If all those tables have the same number of fields to be retrieved, you can use a UNION statement.

Something like this:

SELECT FirstName, LastName, City, State FROM Table1
UNION
SELECT FirstName, LastName, City, State FROM Table2
UNION
SELECT FirstName, LastName, City, State FROM Table3

The fields don't have to be the same name or type.  But you do have to have the same quantity of fields.  Of course, you can use a static value if you have different quantity of fields.

Your SELECT statement can be as simple/complex as you want.  All the UNION does is combine multiple results into a single recordset.
Eric D. Burdo Send private email
Thursday, September 21, 2006
 
 
You really don’t need any code here, well, ok..one line to open/print the report.

For your 3 related tables (2 of which only have one record, and the 3rd that has many) you can use a sub-report to pull in the “many” side. Just drop in the sub-report. If you use the wizard, it will even setup the relations for you. This means you don’t even have to write any sql, or even setup a join. For each one main record, the sub-reports will display the “many” records side (and, even for your 2 that only return one record..you can still use sub-reports if you are lazy).

Now, all you need to do is filter the main report to one record you want. All of the sub-reports will automatic be restricted to this one “main” record, but display as many child records as needed.

Are you launching the report from a ms-access form? If yes, then
Use the following code to filter to the one record you are looking at:

To filter the main report,
Me.Refresh              ‘ fore disk write of data before
                                ‘report sees it
Docmd.OpenReport “yourMainReportName”,acViewPreview,,”id = “ & me!id

Ok…the above is two lines of code. The rest can be done with drag and drop….

However, you hint that you are using ms-access + .net.

Perhaps you actually mean you are using a JET mdb file, and that means you don’t have the ms-access report writer at your disposal? (I am thinking this is your case).

Anyway, using sub-reports, no code, or sql or filters need be written to pull the data from multiple tables. 

However, if you are not actually using ms-access, then my advice is really not much help...

Albert D. Kallal
Edmonton, Alberta Canada
kallal@msn.com
Albert D. Kallal Send private email
Friday, September 22, 2006
 
 

This topic is archived. No further replies will be accepted.

Other recent topics Other recent topics
 
Powered by FogBugz