duplicates in query result

[sueviolet]

Ok thanks so much Norbert

I will try it out and let you know how its goes.


So according to table logic... I should append data to 'one' tables before I append to the related 'many' tables.


Thanks again for you help. I will post back and let you know if I was successful

 

[cinders]

Sue, I noticed your reference to Gold Creek and Alouette Lake in your data. I am assuming Maple Ridge. I am a former resident and this caught my attention.

I never quite expect to find a forum to be a small world.

 

[sueviolet]

Norbert,

I will manually fix the author query and get back to you.



I ran the append queries for the following tables in this order:

tblProvince
tblRegion
tblLakeSequence
tblWaterBody
tblSpecies
tblOccurance
tblReportLocation
tblDepartment
tblPublisher
tblReport
tblReportOccurance (the append query for this table was not successful - I had to shut down Access because it just kept running and nothing was happening)

What this sequence correct?

Also, I need to put the field for the original PUBID values (ie: 'LM305') back into a table. What would you suggest?

As always, thanks!

 

[Nouba]

Sue,

if the db's relationships are set before appending any data you couldn't do it wrong.

Add the field PUBID to tblReport in table design mode. Then you have to add a column for the field PUBID in qselReport. In qappReport add the PUBID into a new column and adjust the append to field accordingly.

 

[sueviolet]

Nouba,

A couple questions:



1) I seem to have to add records little by little from the original tables - running 'qappReportOccurance' with too many records crashes Access on my computer. (I only have 3165 records in tbl_FW_Main, 6951 records in tbl_waterbody and 5627 records in tbl_species.

2)Sorry, but I don't 100% understand what you want me to do with authors table. when I tried to manually have changes to "qselAuthorSplit_I_can_t_find_a_rule_needs_handwork" it wouldn't let me

I don't want duplicate values in tblReport - how do I stop this? For example, One report (ie"LM506.3") is repeated 80 times in tblReport

When I used 'group by' in qappReport, it says "Invalid use of null'

3) Also, still have same problem as originally posted. when querying for PUBID 'LM506.3' - still getting repeating values. Maybe I am not putting the query together correctly.



Thanks again for your help

I am unbelievable thankful on all the time you have spent on this project.

 

[Pat Hartman]

I know you have spent a lot of time re-working your tables but I believe the problem to be as I originally stated.

 

[sueviolet]

Ok thanks Pat,

How do I fix my problem? You told me what my problem was (I agree with you), but how do I solve?

I am learning alot here, but I still haven't solved my problem

Thanks,

Sue

 

[sueviolet]

Sorry Pat,

I should have mentioned that I am outputting my queries results using Cold Fusion, not form/reports.

 

[Pat Hartman]

You simply cannot produce a single query that includes data from unrelated tables if you expect your results to make sense. You need a minimum of two queries. I don't know what Cold Fusion is so I can't offer any alternative.

 

[sueviolet]

Thanks pat