Counting a number of entries in a query

[plog]

"Databases" have a lot of tentacles: Table structure, SQL writing, form and report layout, and scripting. I suggest you start with normalization:

Database normalization - Wikipedia

en.wikipedia.org

That's how you set up your tables/fields so it can accomodate your data. Then move to SQL:

W3Schools.com

W3Schools offers free online tutorials, references and exercises in all the major languages of the web. Covering popular subjects like HTML, CSS, JavaScript, Python, SQL, Java, and many, many more.

www.w3schools.com

That will help you write queries to get your data out.

 

[CelevGadol]

Thanks for your observations. There are some interesting points here.

The surveyor first and last names is a good one. There is absolutely nothing to stop a user from selecting a first name of one surveyor and a last name from another. Perhaps that needs a rethink. My original thinking was that a table should have the data broken down as much as possible so the table would contain a field for first names and last names rather than both names in one field. This, I guess, is the problem with drop downs or at least part of it.
The same thing goes for the project names and project number. The main thing is that each company requires a list of sites with the corresponding project so that they can cross charge their respective clients and perhaps analyse which projects are profitable and which are not. It doesn't matter whether the companies use the same system or not. It's just a report of which projects each site is chargeable to.

tblSurvey2 was created for the screenshot which doesn't contain any data. It's not in use in the actual database.

I'm going to look at the link that you have included now

I know it's been a while but I've finally had a chance to revisit my db. I'm having trouble deciding whether to start from scratch, which means having to find out how to import the data from one db to another, or to rehash the current db to rectify the faults. I have discovered (thanks to you. actually) that it's not so much an excel head that I have but I'm treating tables like forms because I enter data directly into the tables. That's why my tables have drop downs! I now have some forms which have the drop downs.
Anyway, if I were to fix the faults with the current database, one of the first things I would need to do is to remove the drop down thing from the tables. I can't find a way to do this. Is it a straightforward process and if it is, what is the process?

 

[Gasman]

To get rid of the table lookups, just remove everything on the LookUp tab.
Then you will see the true value in the table. Recreate the lookups on your forms.

 

[CelevGadol]

To get rid of the table lookups, just remove everything on the LookUp tab.
Then you will see the true value in the table. Recreate the lookups on your forms.

Thanks Gasman
I looked at this and the look ups refer to another table so I think I would have to delete the table to remove everything in the lookup tab. That means I would lose all the data. I've been dicking about with this for a while now and I think the most straightforward method would be to start from scratch and enter the data all over again. Don't forget that my knowledge of databases is just below very basic. I think I'll just have to bite the bullet(s) and resign myself to a few hours of data entry

 

[CJ_London]

I think I would have to delete the table to remove everything in the lookup tab. That means I would lose all the data.

no, you won't - deleting the lookup will not delete the underlying data in the field - unless you are using a multivalue lookup?


all you are doing is changing this

to this

if you are not sure, take a copy of your db and play around with that.

If you are using as multivalue lookup, there are ways to preserve the data by creating a joining table and populating that with a query before removing the lookup - come back if you need to do this

 

[Gasman]

Thanks Gasman
I looked at this and the look ups refer to another table so I think I would have to delete the table to remove everything in the lookup tab. That means I would lose all the data. I've been dicking about with this for a while now and I think the most straightforward method would be to start from scratch and enter the data all over again. Don't forget that my knowledge of databases is just below very basic. I think I'll just have to bite the bullet(s) and resign myself to a few hours of data entry

No, as CJ_London mentions, you will not delete the data. I only have 2007, so no multi value fields, but will remember that in future.
It is called a 'lookUp' for a reason.

I would probably copy the source sql to notepad as well, so you do not need to create from scratch on your forms? You can then just paste that in your form control properties.
A backup before all this is a very good idea as well, especially with your experience.

 

[GPGeorge]

I can't argue with that. I've worked with software engineers who constantly use temporary workarounds which tend to become permanent fixes which breed constant updates in the form of patches which is a pseudonym for workarounds

I use the term "code wadding" to describe that phenomenon in an Access relational database application. The analogy is to grade school when the teacher told students to throw away their gum, but instead they simply added it to the growing wad of gum on the underside of the desk. When you leave inappropriate table design in your Access relational database application, you constantly have to add bits and pieces of code to the growing wad to keep it going....

 

[Pat Hartman]

did you ever try @bastanu 's solution.