Duplicate entries on forms queries and reports

[PROJECTS]

I desperately need someone's help with the following problem:

BACKGROUND:

I am creating a database to manage information about various projects a certain organization is/will be funding. I have set up my tables with 1 to many relationships as one project may have many characteristics (i.e. take place in more than one city or region, fall into more than one category,etc.). I've attached an Excel Spreadsheet with all of the data so that it may be easier to get an idea of what I'm talking about.

THE PROBLEM:

When I created the form, I ended up with several records for the same project (80 records in total though I only have 20 projects).

Similar problems resulted when I ran a query. I asked to see all CULTURE or PR projects in either . When I ran the query I got several projects listed several times (i.e. Project 6 was listed 4 times... because it takes place in two different cities and is both a CULTURE and PR project) and what I need is to have the project listed once, but with the two cities listed.

The exact same thing happens when I run a report. This report was to group all projects by city. If you notice that the Athens group has two projects with the same ProjectID: JFIT003 (Project 4). Had I made a summary of the budgets, the total would not be correct because there aren't two projects in Athens with a $100,000.00 budget.

Maybe I have the concept of unique identifier/primary key wrong or I'm doing something wrong with my joins ...

Can someone please help me as I'm really new at this and under an extremely tight deadline.

P.S. Don't laugh at my form design, its not finnished yet.

 

[KenHigg]

Sounds like you need a form/subform.

 

[PROJECTS]

Ken,

Thanks for replying so quickly... I used the wizard to create the form with tblProjects as the main form and the other tables as the subforms... is tha incorrect? Should I have done it manually?

 

[KenHigg]

Yeah, wizards and macros will get you bashed on this forum. You should have a good book that covers these topics in depth. One point, on the sub forms, set the deault view to 'continuous forms', and to 'single form' on the main form. If you monkey with these settings, you'll see why... Looks like an interesting project...

 

[Mile-O]

Yeah, wizards and macros will get you bashed on this forum.

The wizards in Access are hopeless. When they generate an action then they are typically generating code that worked in Access 95 but has since become defunct. MicroSoft just hasn't ever updated its wizards. The wizards do okay in some places but the can leave a lot of reworking to pick up and play with.

Macros are awful, useless, desperate little things within a database that you should avoid at all cost - inflexible, contemptible, etc.

 

[PROJECTS]

Thanks for the advise guys...

I re-did the form and subforms as Ken suggested and it worked!!

But, the queries and reports still come up with duplicate entries

Any ideas???

 

[Mile-O]

There's no duplications - your design is just off.

I don't understand why you've put the project id into Category, City, and Considerations tables. These should be keys to the Project table.

You also have many-to-many relationships which haven't been factored in.

 

[KenHigg]

Try adding another grouping section in the report driven off the project ID and put project level flds there.

???

 

[PROJECTS]

I was told to use do so by two people in another forum. All the information in the project table only has one answer to each project (e.g. each project has only one title, termination date, etc.). The project ID is what I use to link (1 to many) to the tables where there are several answers cuz its the unique idenitifier... am I way off base on this?

 

[PROJECTS]

Ken,

What do you mean by project level flds???

I'm sorry, but could you talk to me as if I were blonde cuz I'm really new at this

 

[PROJECTS]

Mile-o-Phile,

Are you suggesting that I make many-to-many relationships between the various sub-tables (categories, cities and considerations)?

 

[Mile-O]

Look at this:

 

[KenHigg]

Never mind my suggestions, just follow the other advice, sounds like having two of us replying is only making matters worse for you.

 

[PROJECTS]

Advice from both of you has been very useful to me, but everythings not worked out yet. We're on holiday until Thursday... I'm going to do some reading and experimenting in between. Mile-o-Phile, I know I've taken up a lot of your time, but I already know that I'm going to need some more clarification on Thursday, if you don't mind.

 

[PROJECTS]

Mil-o-Phile,

For the past week I've been playing around with the suggestion you gave me and I have to admit that I'm lost or maybe just way over my head. I now have subforms which don't correspond with the main form. Could you please elaborate?

Also, I should probably make clear that the information on the City, Considerations and Consideration Tables is not stagnant. To be more precise, so far I only have Athens, Hambourg, Dallas, Cape Town and London listed as cities, but tomorrow we can have a project that could take place in Honolulu or even Preston.

If you have the time to respond I would be more than grateful!!!