February 6th, 2013, 07:50 AM
Report Duplicating in Access - Desperate need of help
Help!! I know this is going to be so simple to someone but i need help. I am self taught on access and my database has been perfect. Recently i have had to add another field into a table and it is now throwing out my reports. Basically before, i had one account number per customer but now that is not the primary key as i now have one account number with several branches so the only thing that can be the primary key now is the customer name. I have changed the primary key, but now all of my reports are duplicating the data. I can get around this by removing duplicates in excel, but i know there must be a simple stupid thing i have done that can be rectified. I just cant work out what!!
February 6th, 2013, 08:01 AM
Using the customer name as a primary key is not a good idea, although it may be a rare occurance, you may have two customers with the same name. Instead of using the account number or the customer name as the primary key, consider using a new field with a datatype of autonumber as your primary key.
Changing the primary key on a database is not a simple matter. it's most likely that most of your queiries and reports either use the old primary key as part of a relationship or as some type of filter to limit your results. Basically when you change a primary key in some cases you almost endup reworking the entire database. Which is why selecting the correct primary key to begin with is so important. (When in doubt, use an autonumber field.)
Without seeing your specific applicaiton there is no way to know how wide spread the change has effected your application. There's also no way to tell you how to fix it. If you want to post it to a file shareing site so that we can take a look, then someone may be able to help you. (remove all confidential information and compact and repair it.)
February 6th, 2013, 08:33 AM
Ok, I understand what you are saying. My problem is that for example I have a report that is not duplicating information but if i recreate that report in exactly the same way, it duplicates things. Basically i have 4 tables, one is customer details, one is invoices etc. Those are the two that i use. I am asking the design wizard to give me 2 pieces of information from the customer details and the rest from the invoice. For some reason it is duplicating the information, so it will give me the details for some invoices 20 times? I cannot for the life of me work out why? I have now done what you suggested and used auto number in my customer detail table to differentiate and use as a primary key but in my invoice table, there is always only one invoice per number so the invoice number is the primary key.
February 6th, 2013, 08:52 AM
Also, please can you tell me a file sharing site so that i can post it?
February 6th, 2013, 09:49 AM