Query picking up more than one record, under unique identifier?

[DariusC]

Hi guys,
Darius here, my apologies for being the newbie and asking the stupid questions, but you don't ask, you dint learn, right?

I have a small database, its still in the development stages. What i'm trying to do, is to put a button on a form, that generates a report, based on a query - that uses the primary key of a membership number field, uses that to create a pdf file that is attached to an email, that in turn can be sent of to where needed.

The thing is that the database is a 1 to 1 relationship, but the query creates a report that contains more that just that one members report attached to it. Why could this be happening? The query criteria read:
forms![the form name]![the unique field] this is the membership nr

I hope all this makes sense... can someone tell me why this is happening.
All I want is for the generated report to only have the information of the current form on it based on the current membership nr.

I might also mention that there are 4 tables in the relationship and that they all use the same membership nr as the primary key, could this be where i'm going wrong.

Like I said, I'm new at this.
Thanks in advance for your time and patients.
Regards Darius

 

[pbaldy]

If I had to guess, one of the other tables in the query has more than one record for that unique field. Is that possible?

 

[DariusC]

Hi Paul,
Thanks for the reply, I dont think that that is possible as the query is only based on one table. Is there a setting or a peace of cade that I could run that could check for this?

Darius

 

[pbaldy]

Can you attach the db here? If the report is based on a single table with a single record per unique ID, I can't think of why it would show multiple on the report.

 

[jdraw]

I might also mention that there are 4 tables in the relationship and that they all use the same membership nr as the primary key, could this be where i'm going wrong.

Could be. It would be helpful for readers if you could post the database as Paul requested. Since you have 3 posts you should create a zip and attach that.

 

[DariusC]

Hey guys thanks for all the feedback.
I have attached a file I hope this can get us some more answers.
Its probably just something stupid I don't know about.
Kind regards
Darius

 

[pbaldy]

How does one replicate the problem? The section 1 report seems fine, the section 2 report refers to a form that doesn't exist (at least not in the sample).

 

[jdraw]

Your relationships window shows that your tables are not normalized. That could cause problems with any queries etc.

 

[DariusC]

Hi Paul, just had a look at your reply.

What do you mean by the form doesn't exist.
Did it not come up in the navigation pain that I sent you guy's?

If not, could it have something to do with the way that the naming has taken place? When I put in the name of the form in the query, it took away the spaces that was in the form name. I don't know is this is significant or not?

I have fixed that now, But as before, when you hit the email button on section 2 the generated pdf still contains the data of more than one record.

I want it to literally just contain what the one filled in form has on screen...

Any other suggestions as to what could be wrong?

 

[pbaldy]

The report is based on SQL, not one of the saved queries. That SQL does not include a criteria like they do.

 

[DariusC]

Hey Paul, good afternoon, how are you doing?

Just read your post, could you please elaborate on that, because at the moment you might as well be speaking chinese, and my chinese is not that great lol

 

[pbaldy]

Look at the record source property of the report. It isn't the name of one of your saved queries, it's SELECT...

 

[DariusC]

Hi ya can I show the work that I have done today. I think im a bit closer to getting it to do what I need it to do. Have to admit didnt know that access was this powerful a tool. just one thing, if you get a sec can you hit the print button on the last form, section 4 and tell me if you get everyting all squashed in, in if so why?

 

[pbaldy]

You're opening a query directly, rather than a report based on that query. You have a lot more control over a report.

 

[DariusC]

so how do I go about opening the query through the report?

 

[pbaldy]

Same as rptPrintingOneRecordPerForm; you make the query the record source of a report. You can start with the report wizard if you want.

 

[DariusC]

Just tried that what I got back was an error saying that the wizard cant handle that many fields. should I do just a view and copy/ past the rest ind set them to the appropriate data source?

 

[pbaldy]

You can start with a blank report in design view. Make that query the record source. You can use the Add Existing Fields window to click/drag fields to where you want them.

 

[DariusC]

I think I might have so switch access off for a second or 2 its starting to make everything look weird now. back in 5 min...

 

[DariusC]

Hi Paul, no luck mate.
Still getting the same unexpected results. Can I ask you a fuvour. Would you be able to give me an example of how you would go about doing it. I have done what you said, and changed the source to the query, and like I said still no luck. Sorry for being a pain!!
Would you be able to do it step by step though so that I can follow and understand in future where exactly Im cocking up, please.