Union of 2 tables and then search

[gameover]

Hi everyone,

I have got a form in which I have got some fileds. The form as of now is not connected to any table. I have got 2 tables also which are not interconnected to each other. These tables have some common values in 1st column only. The common column is Customer's names column(1st column)

1st table 1st column has got Customer name 2nd column Customer Address and 3rd column amount spent by customer in year 2007-2008.

2nd table 1st column has got Customer name (may or may not be same as 1st table) 2nd column Customer Address and 3rd column amount spent by customer in year 2008-2009.

What I need here to make a form in which we can search for Customer's name. The customer name should be searched in both Table 1 and Table 2.
The result window then should come out with Customer's name, his address and the amount spent by him in both 2007-2008 and 2008-2009 years and the total amount spent by him from 2007-2009 (addition of both 2007-2008 and 2008-2009).

If the customer is present only in 2007-2008 then 2008-2009 box can be empty and vice-versa also.

I hope I have made myself clear also. Please revert in case of any questions.

Please suggest me how to proceed here as I am very new to Access.

Thanks in advance for your help.

 

[jackd]

I think a more basic problem facing you is the fact that the records in the 2 tables can not be linked together by some unique identifier such as CustomerNumber.

If you are linking tables on Customer name only, your results may not be what you expect.

Name matches are rarely sufficient.

Can you put unique identifiers on your Customer records? If this is real data for a business report, look very seriously at structuring your tables. The sooner, the better.

Here is a link that explains unique identifiers etc.; and the references are good also.

http://lists.evolt.org/archive/Week-of-Mon-20000501/100519.html

 

[gameover]

Hi Jack,

Sorry for my limitation of knowledge of Access I could not figure out what exactly should I do? May be you yourself can elaborate a little bit!!!

Thanks again for your help!!!!

 

[jackd]

Hi Jack,

Sorry for my limitation of knowledge of Access I could not figure out what exactly should I do? May be you yourself can elaborate a little bit!!!

Thanks again for your help!!!!

You say your form is not connected to any table; then you say you have 2 tables?

Where did the form come from-- who built it? Did it ever work?

What are your table structures- field names , types etc.?

 

[SydneyGeek]

Elaborating a bit on Jack's comments...

Access and other databases work best when information is separated into topics. One table, one topic, so you need a Customers table with the information split further to make it easier to search. So names typically are split into FirstName and LastName. Addresses are broken into Address1, Address2, Address3 (the different lines to allow for apartment addresses, for example) then City, State, PostCode (or Zip). All of these should be text fields. You may also need several phone number fields -- Home, Business, Mobile, as well as EmailAddress.
I left it to last but it really should be first -- every table needs a primary key, a unique identifier for each record. This should be an Autonumber field, and will usually start from 1 and increase as you add records.

So, Customers has
CustomerID
FirstName
LastName
Address fields
Phone fields
Email

Orders needs
OrderID
CustomerID (a Number field)
OrderDate
OrderAmount

You need to join Customers to Orders by linking the CustomerID fields.

The topic is a big one but if you go to http://www.gpcdata.com and check the links in the top right corner, you will be able to download a file with a very detailed descrption of what to do with table design.

Take a look, then come back for help on how to move your data into the new structure. It will make a big difference to the usability of the database.

Denis

 

[Norie]

Do these tables have the same structure apart from the first refers to 2007-20008 and the second 2008-2009?

If they do then I see no need to have separate tables.

In fact having them is just going to cause the problems you describe.

Create 1 table with the same structure as the others plus a new field for that identifies the period each record belongs to.

 

[gameover]

Hi All,

Thanks for your kind response. I'll check the link and then come back to you Dennis. While combining the two tables in 1 can be other option.

The form has not been yet made but I messed up in posting the question. The real question was I wanted to make the form like what I posted. Sorry for creating such a huge confusion. Anyways I'll be back on Monday and tel you people how did it go!!!!

Thanks again for all your help!!!!