Two simple questions

[Psychobabble]

I'm really bad with queries. There's two I'm having problems with.

1) I want the value of a combo box to be made up of all the distinct entries in two seperate fields. The following query works, but it only gets values from .judge, not .judge2

SELECT DISTINCT Cases.Judge, Cases.Judge2 FROM Cases;

I tried:

SELECT DISTINCT Cases.Judge FROM Cases; SELECT DISTINCT Cases.Judge2 FROM cases

But it said there was text after the SQL operator and I tried replacing the semi-colon with a comma but that didn't work either.

I'm sure this is something simple, any ideas?

Something weird also happens with this lookup, it only gets new values if I save/quit the table and reload it. Is it possible to manually run the lookup?

2) I have a field "year" and a field "citation". The citation always begins with "[XXXX]" where XXXX is the year. Is it possible to have a query which automatically selects the year from "citation" and inputs it in the year field?

 

[Rich]

It looks as tough you have separate fields for each judge, if that's so you have a design problem, you only need one field for judge and another which identifies the judge

 

[Psychobabble]

It looks as tough you have separate fields for each judge, if that's so you have a design problem, you only need one field for judge and another which identifies the judge

No, the cases I'm doing oten have more than one judge and I'd like for all of the combo boxes to have all of the judge's names which I have entered in any of the fields.

 

[Nouba]

You have repeating groups in your table which violates the rules of data normalisation. What will you do if you have a case with 3 or more judges?

In the sample you can use a Union Query for getting all the judges in the combo box.

SELECT Judge FROM Cases
UNION SELECT Judge2 FROM cases

 

[Psychobabble]

Ahhh... UNION, that's what I needed. Thanks.

Originally posted by Nouba You have repeating groups in your table which violates the rules of data normalisation.

I have no idea what you're talking about

Ideally I would be able to select three or more judges in seperate combo boxes on my form and have all three selections be saved in an array or something in the judges field. I don't think that's possible though.

What will you do if you have a case with 3 or more judges?

I'm allowed to use UNION more than once aren't I

And is anyone able to answer my other questions? In case you don't remember...

Something weird also happens with this lookup, it only gets new values if I save/quit the table and reload it. Is it possible to manually run the lookup?

I have a field "year" and a field "citation". The citation always begins with "[XXXX]" where XXXX is the year. Is it possible to have a query which automatically selects the year from "citation" and inputs it in the year field?

 

[Nouba]

I have no idea what you're talking about

I suggest you do a search on Google: data+normalisation - you'll get tons of results.

Create two other tables. Firstly a table Judge with an autonumber field as PK and all the needed attributes for your judges. Secondly build a table CaseJudges where you put a FK to your cases table and a FK to the judge table. For each case you can have none (not really), one or more judges involved.

I'm allowed to use UNION more than once aren't I

That's true.

Something weird also happens with this lookup, it only gets new values if I save/quit the table and reload it. Is it possible to manually run the lookup?

Do a Me!YourComboOrListbox.Requery for updating the contents of the control.

I have a field "year" and a field "citation". The citation always begins with "[XXXX]" where XXXX is the year. Is it possible to have a query which automatically selects the year from "citation" and inputs it in the year field?

This is another violation of the rules of data normalisation. All attributes should be atomar. It is better to store the data in separate fields which you can join together for reporting purposes.

In the QBE-Editor you could write in the field row ExtractedYear:CInt(Left$(YourField;4)) to get the first four letters converted into an integer number. Watch out for the right list separator - it is defined by the regional and language settings in the control panel. In my case it is a semicolon.

 

[Psychobabble]

Note: I'll understand if you declare me too noobish to try and help. I definately in territory where I don't have a clue what's going on.

Create two other tables. Firstly a table Judge with an autonumber field as PK and all the needed attributes for your judges. Secondly build a table CaseJudges where you put a FK to your cases table and a FK to the judge table. For each case you can have none (not really), one or more judges involved.

I only vaguely understand what you mean here. The thing is that a) my judges don't have any "attributes" beyond their name and b) I don't want to have to open up the judge table every time I want to enter a new judge. I just want to enter my judge's name in the field in and not have to enter it again later. I don't really know how to do what you're talking about, or why, so I'll stick with what I've got I think.

Do a Me!YourComboOrListbox.Requery for updating the contents of the control.

See this is how little I know. I put this code into the "after update" section of the combo box:

Form_Cases!Judges.Requery

and it doesn't do anything. The form's name is Form_cases and the combo box name is judges.

This is another violation of the rules of data normalisation. All attributes should be atomar. It is better to store the data in separate fields which you can join together for reporting purposes.

But if I have seperate tables for everything I have to enter data in all over the place, don't I? In any event "year" is an attribute of the particular case, so why not keep it in the same table?

In the QBE-Editor you could write in the field row ExtractedYear:CInt(Left$(YourField;4)) to get the first four letters converted into an integer number.

Doing that gives me a syntax error, so I replaced the ";" with a "," which got rid of the syntax error. It doesn't do anything though. I'm assuming "yourfield" is just the name of the field I'm extracting from (citation) and I just pasted that in the right place.

 

[Mile-O]

You really need to look up relational database design.

Here's some links from a quick Google search.

#1

#2


#3

#4

 

[Mile-O]

I only vaguely understand what you mean here. The thing is that a) my judges don't have any "attributes" beyond their name and b) I don't want to have to open up the judge table every time I want to enter a new judge. I just want to enter my judge's name in the field in and not have to enter it again later. I don't really know how to do what you're talking about, or why, so I'll stick with what I've got I think.

a) Still, keep a table for judges only.
b) That's the wodner of queries and basing form's off queries rather than tables.

See this is how little I know. I put this code into the "after update" section of the combo box:

Form_Cases!Judges.Requery

and it doesn't do anything. The form's name is Form_cases and the combo box name is judges.

Click on the button at the end of the event line (it has 3 dots) and select Code Builder - put the code on the module you are presented with.

But if I have seperate tables for everything I have to enter data in all over the place, don't I? In any event "year" is an attribute of the particular case, so why not keep it in the same table?

Again, the magic of queries.

Doing that gives me a syntax error, so I replaced the ";" with a "," which got rid of the syntax error. It doesn't do anything though. I'm assuming "yourfield" is just the name of the field I'm extracting from (citation) and I just pasted that in the right place.

Nouba did say that in his case it was a semi-colon, but to watch out for regional settings. I don't understand why it isn't working.

 

[Psychobabble]

Originally posted by Mile-O-Phile a) Still, keep a table for judges only.
b) That's the wodner of queries and basing form's off queries rather than tables.

What's the point of having a table for judges if there's no data entered in it!

And I'll check out those articles you linked, thanks.

 

[Mile-O]

What's the point of having a table for judges if there's no data entered in it!

There is data entered in it - you are holding the judge in it.

By keeping a judges table and using a judge's unique ID as a foreign key in another table you are reducing the need for possible alterations in the future.

If, for example, you had a judge and their name changed (marriage?) then you would only have to change it once in the judges table rather than go through all instances of the judge in a cases table.