repetition in a query

[gout]

i am new to Access DB's and have to build a DB for college
ive modellled and built the DB but when i create a query to extract any information the inf requested is repeated once, twice sometimes three times.... my frustration is growing rapidly ....can any one plz help

i'll attach the DB so anyone can take a take a look at it.

any help would be gratefully appreciated.

 

[Tay]

Sounds as though it's what you're asking the query to do, and it's not too happy. Attach the db and say which one is the problematic query.

 

[gout]

tay

the db is 2 meg and the forum limit it below this
cant seem to post it .... any ideas

 

[Tay]

Can you zip the db and then post it?

 

[Mile-O]

If it's 2 meg and for college - make a copy of it, remove any graphics you may have in it and compact it before trying to .zip it.

 

[gout]

zip

i zipped the DB but it s coming up with incorrect format

you can only download certain file types and zips appear not to be one of them

can i email you ????



thanx in advance

 

[Tay]

Try what Mile suggested to reduce it's size. I have PM'd you

 

[gout]

DB whoes

The DB has been built using Access 2000

so anyone wishing to help presumably needs that ver installed

thanx in advance

 

[Mile-O]

I'm sure Access 2000 gives you the option to save as Access '97.

 

[gout]

access 2000

if i save the DB as an Access 97 file can i convert it back should any changes be made to it
because if so im happy to convert it and send

 

[Mile-O]

I'm not sure as I don't use 2000, but I think it will create a new database in Access '97 format leaving your original still a 2000 file.

 

[gout]

conversion

if i can convert it to a 97 DB will you allow me to email it to you
so you can take a look and maybe tell me where im going wrongplz

 

[Mile-O]

Sorry, but I can't accept email due to my work recently blocking the site.

You can still send it to MileOut@aol.com and, if the problem's not resolved by the end of hours today, I'll give it a look when I get home.

 

[gout]

email

many thanx

i've emailed it to you.

 

[rpadams]

Check your joins

Rather than get into a long discussion on how to send your project to this forum, the answer to your original question is to examine your join types in your queries. Altho you frequently use the first type, if you have a many to many relationship (or even one to many) this gives you duplicate values in the data retrieved. Try changing the join type by clicking on the relationship arrow in the query and selecting another.

 

[gout]

joins

ive tried all the variation of join possible and still get the same results sometimes differing amounts of repetition

only one name gives a single and true result and whichever way i look at it i cant seem to see the difference between that and any other name

i have spent two days on this and seem to be getting no where

 

[Mile-O]

I just had a look at your database and was wondering if it might be better idea to restructure - I think you have too many tables as joins.

Also, the grade table is surely calculable rather than store the pass/fail information in a table, as by making a query you can determine whether a case is a pass or fail.

Structurewise I was thinking making an extra table called tblCases and having these fields:


CaseID
StudentID
LecturerID
CourseID
GradeID


and in your relationships link these to your different tables in one to many relationships.

This way, you have one table to query on and can link easier into the extra details you may want from the others.

 

[gout]

reply

the problem with that is we had to make ERD's of the original scenario and then normalise to 3NF so im stuck with the way it is or i have to completely redesign the whole thing

if you take the name of Moira sands her information doesnt double or triple yet the others do....what can be affecting her details and not the others?? if i can get my head around that maybe i have some chance

the assignment needs to be handed in soon and i dont have time to remodel and evidence everything for handin.