CODEWITHSUNDEEP
sqlms-accessvbams-access-2010
ATH
ATH

Reputation: 3

Matching records from several tables in access

I'm a newbie in access. Hope you guys can enlighten me with my problems.

I have several tables which stored records according to the years. All the tables have the same set of fields. I am wondering if it is possible to search for a particular customer id, and produce me all his purchased records across the tables in my database. In a year, he may have several records while another year he may have none. If I use join function, it does not seem to be able to produce the results that I want.

examples:

Table 1 - 2001 the fields are as following: Customer ID | Purchased Date | Purchased Time | Item Purchased |

Table 2 - 2002 Customer ID | Purchased Date | Purchased Time | Item Purchased |

Table 3 - 2003 Customer ID | Purchased Date | Purchased Time | Item Purchased |

Table 4 - 2004 Customer ID | Purchased Date | Purchased Time | Item Purchased |

I want the results to be as following (which means all compile under same column for the same type instead of creating new columns for the particular year):

Customer ID | Purchased Date 2001 | Purchased Time 2001 | Item Purchased 2001|

Customer ID | Purchased Date 2002 | Purchased Time 2002 | Item Purchased 2002|

Customer ID | Purchased Date 2003 | Purchased Time 2003 | Item Purchased 2003|

Customer ID | Purchased Date 2004 | Purchased Time 2004 | Item Purchased 2004|

Upvotes: 0

Views: 33

Answers (2)

Kelly
Kelly

Reputation: 649

I don't think you want to join the tables. I believe what you want is a "union all" query.

Given this dataset...

Table 2001
CustId | Item
101      Widget A
101      Gizmo B
201      Widget A
201      Wedge C

Table 2002
CustId | Item
101      Widget A
201      Widget A
201      Wedge C
201      Wedge C

Table 2003
CustId | Item
101      Widget A
101      Gizmo B
101      Gizmo B
201      Gizmo B

The query would look like this...

SELECT item
FROM 'Table 2001'
WHERE CustId = 101

UNION ALL

SELECT item
FROM 'Table 2002'
WHERE CustId = 101

UNION ALL

SELECT item
FROM 'Table 2003'
WHERE CustId = 101

Be sure to specify the same columns in the same order for each individual query so that the data will be matched up correctly in the query results.

Upvotes: 0

JBC
JBC

Reputation: 112

You would be best off to create a new Table, leave out the year in column headers:

Customer ID | Purchased Date| Purchased Time | Item Purchased

You could merge your data via a query.

Upvotes: 0

Related Questions