I am creating a database in which right now I only have two tables. One table is Customer [information], the other is Trade References. I have 3 fields in the Customer table (Trade Reference 1, Trade Reference 2, and Trade Reference 3) that are pulled from the Trade References table (lookup).
I then created a form where the user can select which trade references they would like to pull. Then I created a report based on the form. This is where I am encountering problems. On the report it lists the correct names for the trade references, but it lists the same phone number and fax for each one.
A couple of things
1) Are you aware that your design violates first normal form? The below might look daunting but it should be the minimum you read before designing a database: http://r937.com/relational.html
2) I am not certain what you mean. Do you know the difference between joins and relationships? Relationships affect the data you can put into tables. Joins affect the data you see when getting them out. Your problem appears to be one of display so I would expect us to be talking about joins rather than relationships.
Good job on reading it - most people ignore the fundamentals
It was not specifically related to your problem however as soon as you have columns called Trade Reference 1, Trade Reference 2 and Trade Reference 3 in a table you are likely going to get yourself into a whole load of trouble in the future. I am happy to expand if you like but the bit about first normal form is the relevent part.
Anyway - point two was more germain to what you were asking. Are you talking about changing the relationships between the tables or joins in a query?
I have started over with the database and am just back to having the two tables. So now I need to figure out what my next step is in order to be able to list the Customer and 3 trades (being looked up from the trade reference table)....any ideas?
What I would like to do is have a form where the user can type in the customer information, and then select three separate trade references from dropdown boxes. And then I would like them to be able to print a report (Trade Reference Request) for each trade reference selected (a separate report for each trade reference) that can be printed and faxed.
I think the Trader could Customer
and a customer could be a trader
tradeID <= just a primerkey
CustomerID <= then customer number
Trader <= which is the CustomerID number form the Customertable
hope this help
See clear as mud
the aim is store once, not store multiple times
Remember... Optimize 'til you die!
Progaming environment: Access based on my own environment: DAO3.6/A97/A2000/A2003/A2007/A2010 VB based on my own environment: vb6 sp5 ASP based on my own environment: 5.6 VB-NET based on my own environment started 2007 SQL-2005 based on my own environment started 2008 MYLE YOUR PASSWORD IS JUST LIKE YOUR TOOTHBRUSH DON'T SHARE IT.