Making a query

  • Thread starter Thread starter Peter
  • Start date Start date
P

Peter

I have table A and table B related 'one to many'.
Table A contain fields name, surname, date, and other fields.
Table B contains number fields B1, B2, B3, ...
I want to make query to show me data from this tables in specific order.
I have made query but dont get result as I want to.
For example,
Table A has 2 records, and table B contains 3 records for each record in
table A.
I would like to have this result of query:
name (1 record), surname (1 record), date (1 record), B1, B2, B3, B1 (2
record), B2 (2 record), B3 (2 record), B1 (3 record), B2 (3 record), B3 (3
record)
name (2 record), surname (2 record), date (2 record), B1, B2, B3, B1 (2
record), B2 (2 record), B3 (2 record), B1 (3 record), B2 (3 record), B3 (3
record).

I hope you understand my question.

Thanks in advance
 
Peter

Your desired result is pretty complex, but it can be done

The first thing you need to look into is Cross-Tab Queries. Access does a pretty good job of explaining them, so I won't bother here.

After you figure out Cross-Tab Queries, you will need to make 3 Cross-Tab Queries

CT1: Name | Surname | Date | Cross-Tab on B
CT2: Name | Surname | Date | Cross-Tab on B
CT3: Name | Surname | Date | Cross-Tab on B

After that, you will need a query to join these all three of these queries together. Join your master query on whatever your primary key is (I'm assuming it's Name and Date) and order your columns accordingly

Hope this works

Ji
 
Back
Top