How do I stop name being repeated? (1 Viewer)

kimberlin · Jul 31, 2011

Hi everyone, I'm trying to build up a database for work to replace the old multiple simple tables that we were using. The tables that I've now constructed are relational but I seem to have hit a snag with my very first query (see attached). The data I've entered into the various tables so far is all dummy stuff so that I could check and see if everything runs ok. The problem is that when I run a query to check on basic worker details, instead of it telling me that a particular person can work in either of two jobs and giving their name and contact details once, it is listing all that person's other details twice.

Any ideas on how I could adjust things (I haven't got a clue about programming but am waiting for a book to be delivered) would be most welcome.

Galaxiom · Jul 31, 2011

I have not looked at your sample but I expect you have a join between Worker and Jobs tables on WorkerID. This will cause exactly the effect you describe. It is bcause you are trying to do it all on one query where you really need a form.

Your form should have the Worker table (or a query based on it) as the RecordSource. Then add a subform with the Jobs table (or a query on it) as the RecordSource.

Set the LinkMasterFields and LinkChildFields of the subformcontrol as the WorkerID. Just carefully follow the Wizard when you add the subform and this is what you should get.

This will give you a form with the Worker and their Jobs listed just once.

kimberlin · Jul 31, 2011

Thank you very much for your help. I'd been trying to restrict the number of queries I was using as boss (who hasn't used access before) told me last week that he doesn't want to see loads of different queries etc when he opens the database up as he thinks it will be too confusing and take extra space to back up!

If this is the only way to get the desired result then he'll just have to lump it!

Galaxiom · Jul 31, 2011

kimberlin said:
... boss told me he doesn't want to see loads of different queries etc when he opens the database up as he thinks it will be too confusing and take extra space to back up!

There are a couple of easy way around this.

1. Copy text from the SQL View of the query into the RecordSource Property of the form. Then you can delete the query. The SQL text in there will even open for editing in the query designer when you click the right hand end of the RecordSource property box.

2. Use VBA to copy the SQL text into the RecordSource property of the form in the OnLoad Event Procedure.

Code:

Private Sub Form_Load()
 
Dim strSQL As String
 
   strSQL = "SELECT blah blah"
   Me.RecordSource = strSQL
 
End Sub

This second one is great when you want to use the same form with different RecordSource queries because you can include logic to enter whatever text you want in strSQL.

You can even use the code in an ordinary sub (eg on a button) to change the RecordSource any time you want.

stopher · Jul 31, 2011

kimberlin said:
Thank you very much for your help. I'd been trying to restrict the number of queries I was using as boss (who hasn't used access before) told me last week that he doesn't want to see loads of different queries etc when he opens the database up as he thinks it will be too confusing and take extra space to back up!

If this is the only way to get the desired result then he'll just have to lump it!

As Galaxiom suggests, users should not need to be exposed to the list of queries so they should never know they are there. Instead, use forms to allow your user to navigate and view data.

Personally I would not spend any time trying to hide your queries until you are quite competent. There are other more important bridges to cross.

The number of queries you have in your database will have a negligible effect on your database size compared to the data stored. So your boss should not worry about this.

Chris

Galaxiom · Jul 31, 2011

stopher said:
Personally I would not spend any time trying to hide your queries until you are quite competent. There are other more important bridges to cross.

Agreed, but when the boss's wishes are so easily achieved it is usually easier to shut up and give them what they want.

You could always give him a shortcut to his copy of the front end including the /runtime switch. This way he won't even get the navigation window so he definitely won't have to see any queries.

kimberlin · Aug 1, 2011

I hadn't thought about that!

How do I stop name being repeated? (1 Viewer)

kimberlin

Registered User.

Attachments

Galaxiom

Super Moderator

kimberlin

Registered User.

Galaxiom

Super Moderator

stopher

AWF VIP

Galaxiom

Super Moderator

kimberlin

Registered User.

Similar threads

Users who are viewing this thread