Unable to sort records in database

[SiyarK]

Hello everyone! Let me first introduce myself, I am SiyarK and have just registered to this forum. I am a beginner with Microsoft Access so please bear with me I have a little challenge for which I haven't been able to find a solution...


I have made a database for the start-up company where I'm doing my internship at; a database with (potential) customers and competitors. It's a split form so there is a table on the bottom of the screen with all the records. (see attachment)


And now we arrive at the problem. I want to be able to sort the companies according to company type, product, status etc. But I am not able to do that. On the other hand, I am able to sort the company names in an alphabetical order. For the record: the boxes for type/product/status/competitor are combo boxes, you can only select one of the options and not type in it.


Could someone please tell me how to sort these companies according to type/product/status/competitor? Many thanks in advance!


And oh yeah, nevermind the query on the left, it's empty. Haven't used a query for this form.




SiyarK

[ssanfu]

I want to be able to sort the companies according to company type, product, status e

Haven't used a query for this form.

The form record source should be a query. In a query, you can use the ORDER BY clause to sort on multiple fields. If the record source is a table, you can only sort on one field using the ASC/DESC buttons on the tool bar.

[SiyarK]

The form record source should be a query. In a query, you can use the ORDER BY clause to sort on multiple fields. If the record source is a table, you can only sort on one field using the ASC/DESC buttons on the tool bar.

Thanks ssanfu for replying on such a short notice!

The strange thing is that even though my record source is a table, I can only sort the field with the company names with the ASC/DESC buttons. But you're probably right, a query would make life easier. So I've tried to make one but it doesn't really work.


Because the type/competitor/products/status are numerical values in the ''Company table'', I've made separate tables for each one of them so the database would know that if Company A is Type 1, that it should show Pharmaceutical in the records (instead of 1), and if Company B is Type 2, that it would show Biotechnology in the records, etc... Therefore I also made a ''Connection table'' so the database would understand these relationships. But when I run the query, the fields are still empty



What am I doing wrong?

[ssanfu]

"Type" is a reserved word in Access and shouldn't be used for object names. You have "Type" as a field in the Company table and as a table name and as a field name in table "Type". Here is a list of reserved words: http://allenbrowne.com/AppIssueBadWord.html
Also, it is better to not use spaces in object names.

I think it is your join types. Since every field in "Connection table" is a foreign key, every field needs to have data. In design view, open the properties for each join and select the 3rd option (I think):

"Select All records from 'Connection table' and only those records from 'XXXX' where the joined fields are equal". ("XXXX" would be the different table names.)

[SiyarK]

"Type" is a reserved word in Access and shouldn't be used for object names. You have "Type" as a field in the Company table and as a table name and as a field name in table "Type". Here is a list of reserved words: http://allenbrowne.com/AppIssueBadWord.html
Also, it is better to not use spaces in object names.

I think it is your join types. Since every field in "Connection table" is a foreign key, every field needs to have data. In design view, open the properties for each join and select the 3rd option (I think):

"Select All records from 'Connection table' and only those records from 'XXXX' where the joined fields are equal". ("XXXX" would be the different table names.)

No it works just fine, I now know what the mistake was First of all, in the company table the type/sector/etc were text values instead of numerical. So when building relations I could not connect the TypeID to Type (I was making a one-to-many relation from my Company Table). So I got that sorted out. Also, in the advanced option I changed the ''Defaul record locking'' to ''No locks''.

That has solved it all! So I've got a nice, functional query in my database!

Still, many thanks for you help ssanfu!

[SiyarK]

I have solved it!

First of all, in the ''Company table'', type/product/competitor etc.. were text values. therefore, I had a mismatch when I wanted to make a one-to-many relation and couple for example TypeID to Type. I have changed it to numerical values, so the relations are fine now. I'm not using the connection table anymore, because I don't need to.

And also, in the advanced options I have changed ''Default record locking'' to ''No locks''. Now my query works fine and I can sort everything the way I want!!

Thanks for all the help ssanfu ;-)

[ssanfu]

Wonderful. Glad to be of help.