Wrox Programmer Forums
|
Access VBA Discuss using VBA for Access programming.
Welcome to the p2p.wrox.com Forums.

You are currently viewing the Access VBA section of the Wrox Programmer to Programmer discussions. This is a community of software programmers and website developers including Wrox book authors and readers. New member registration was closed in 2019. New posts were shut off and the site was archived into this static format as of October 1, 2020. If you require technical support for a Wrox book please contact http://hub.wiley.com
 
Old September 22nd, 2003, 11:36 PM
Con Con is offline
Registered User
 
Join Date: Sep 2003
Posts: 3
Thanks: 0
Thanked 0 Times in 0 Posts
Default Linking Tables

I have two tables that I am trying to link together. Table "A" has purchase records for year 2002 and table "B" has purchase records for 2003. Table "A" has some customer name & numbers that Table B does not have and vise versa. I need to link both tables (listing all the customers from BOTH table only once) and compare the items the customer purchased BEFORE December 31st. 2002 (in one column) and AFTER December 31st. 2002 (another column). The list of items have to include all purchases - old, new, or repeat purchases - for the BEFORE OR AFTER. The items are represented by code numbers. I want to have a table that I can review what each customer (new/old) is purchasing.

Table "A" Year 2002
Customer Name Number Items
Jane 112 1789
Jane 112 23885
Sam 399 2390
Eric 272 4711
Eric 272 3003
Eric 272 2878
Sandy 347 3003
Sandy 347 2390

Table "B" Year 2003
Customer Name Number Items
Ellen 561 3003
Jane 112 23885
Jane 112 4277
Jake 288 2878
Jake 288 1187
Eric 272 4066
Sandy 347 2390

The table that I want is this:

Customer Name Number Year 2002 Year 2003
Jane 112 1789 23885
                               23885 4277
Sam 399 2390
Eric 272 4711 4066
                               3003
                               2878
Sandy 347 3003 2390
                               2390
Ellen 561 3003
Jake 288 2878
                                            1187

Can someone kindly show me how to get the result that I wanted. Thanking you in advance for your kind assistance.

 
Old September 23rd, 2003, 06:04 AM
Authorized User
 
Join Date: Jun 2003
Posts: 11
Thanks: 0
Thanked 0 Times in 0 Posts
Default

Hi Con,

what you could do is create a temp tbl with a col for the year.
append all data from tbl A to temp with year 2002 and then do the same for tbl B (year 2003) then use a crosstab query on the temp tbl (group by cus name & number)

hth
alun
 
Old September 24th, 2003, 04:32 PM
Friend of Wrox
 
Join Date: Jun 2003
Posts: 308
Thanks: 0
Thanked 0 Times in 0 Posts
Default

Another possible option would be to make a union query that selects the people, eg:
Code:
SELECT [tblA].[Customer Name] FROM [tblA]
UNION
SELECT [tblB].[Customer Name] FROM [tblB]
This way you wouldn't have to have a temporary table.

Basically, if you can select the info you need from one table, you can union it with the same sort of thing from the other table

Steven

I am a loud man with a very large hat. This means I am in charge





Similar Threads
Thread Thread Starter Forum Replies Last Post
Linking pivot tables iacon Excel VBA 3 July 24th, 2006 01:25 AM
Access2000 Linking Tables Rchanga Access 1 December 21st, 2004 05:49 PM
Linking Tables usedcarsgbcom Beginning PHP 4 November 23rd, 2004 08:46 PM
Prevent linking to tables MG76 Access 2 March 12th, 2004 02:29 PM
Linking tables with multiple databases jlnash Access 1 August 14th, 2003 07:22 AM





Powered by vBulletin®
Copyright ©2000 - 2020, Jelsoft Enterprises Ltd.
Copyright (c) 2020 John Wiley & Sons, Inc.