query problem

[maxx3]

this should be access 2000

[maxx3]

I really don't now why but I can't upload this file it keeps saying invalid file I'm sorry but Ican't figure it out what it is because I have done it before I think it's ok now it is access 2000

[CraigDolphin]

Ok, that worked. See the attached version of your query demonstrating what I mentioned before.

[maxx3]

Really thanks for your help but right now I"m at work and I can't see it because I don,t have access here but I sure do as soon as I get home thanks again for your help how did you find the tables are they desiged good? by the way isn't it night now in USA.

[CraigDolphin]

The tables are not normalized: you have put more than one item of data into a single field (Months), and you have repeating information (like 'Notary', 'Testment') so I can't say that the table design is 'good' sorry.

Moreover, you are storing 'date' information as a text string and your table setup limits the data you can store un-necessarily.

For example, imagine a record with 'Months' with a value of 21 Dec - 8 Feb. If the december is in 2001, and the Feb is in 2002, what do you put in the 'Year' field?

Since it seems that most records have a date range in your Months field, I would get rid of your year and months fields, and replace them with StartDate and EndDate fields with the correct data type (date). Then you can simply store the actual dates and retrieve your 'year' information using the built-in Year function on both the start and end date fields, and restrict your records as before using the appropriate criteria to list records where the start year is <= the user-input year, AND the end year is >= user-input year.

You also need to create a table to store notary names, and store the key field value of that table rather than storing the name in this table. Likewise for whatever the Testment is.

I cannot comment on the 'Gozo' and 'Served as Notary to Gov Between' fields as there is no example data in those fields. However, you should never use spaces or other special characters in field names or other database object names (like tables for example).

Also, you should examine the list of reserved words in access List of reserved words in Access 2002 and in later versions of Access and ensure that you do not use these words as field names or you may run into errors caused by ambiguity. In your case, 'Year' is a reserved word and should not be used as a field name.

Finally, you have no primary key field in the 'Dates and Volum' table.

Also, in your other table, you have the text field 'Notary' as your primary key but what will you do if you get two different notaries with the same name? The other field, 'Index number' is also not a candidate for a primary key because not all records have a value in that field.

You really need to spend some time reading up on the topic of database table normalisation, and apply what you learn to this situation.

[maxx3]

I've made some changes as Ithink you suggested would you mind giving it a look 'Index number' is a candidate for a primary key because yes all have index numbers the thing is i didn't imput all of them yet now if you don't mind con you write the creteria for me under startdate and enddate because yhe query is not working good as before thanks

[maxx3]

This database is working a little better but i still have problems with dates query

[CraigDolphin]

No...you haven't implmeented the changes I suggested. For example, you do now have a field called startdate but it is still a TEXT field datatype, whereas it must be a date/time datatype and contain only valid dates. At the moment, the field contains text strings that YOU can interpret as dates, but Access does not understand without you explictly telling it to convert the text value into a date using the CDate function, or some other method.

You need to go into design view of the table, and change the data type from text to Date/Time for both date fields. Then you need to populate the fields with valid dates.

Once you've done that, I'll show you how to redo the criteria. But you should pay attention to all of the other issues I mentioned also.

[maxx3]

I made the changes to date/time please have a look what do you mean by valid dates

[CraigDolphin]

Hi again.

Here's your db back with the criteria. Note: I have ensured that any null reocrds will show also up in the query.

I looked at the comment in your "Served as Notary to Gov Between" field and again I come back to the need for you to read up on normalisation and correct tbale design.

You should have a table for notaries. That table should have a unique id field, their first name, last name, starting notary date, ending notary date, etc.

You should also have a table for "Volum's"... whatever those are (I'm guessing you're not a native english speaker?).

In addition to the information about 'volums', that table should only store the id of the notary....not the name and other information about the notary which is already present in the other table.

Unless you are prepared to do the research on the subject of database normalisation there's nothing further I can assist you with.

[maxx3]

Hi thanks for every think I'll do my best to research on the subject of database normalisation but this the first time I have ever worked with access and I was quite sure I was going to do a hell of a lot of mistakes, regarding my language no I'm not an enghlish speak but again I'll try to do my best as always, thanks very much for your help maybe this is a little to difficualt for me to start with.thanks again maybe you here from me again maybe not but I really appriciate your help thanks one thing regarding the volumes each notary has several volumes and the dates you are seeing are the begining and ending dates of each volume so if someone ask for a certain date will know in which volume to search in that is if some ask for a deed of lets say john smith dated 15 Jan 1990 will go to john smith volumes and find that that date is in volume lets say 25 which is located in room 48 that is all

[CraigDolphin]

So the date range of each volume goes into the volumes tables since it is about volumes.

The date range of notaries goes into table notary since it is about the notary.

The reason I am trying to get you to read about database normalization is to help you start from a solid foundation. Your table design is the single most important aspect of your database. If you don't do that well, then everything you wish to do with your database becomes MUCH harder (including data entry), and potentially will eventually unnecessarily limit what data can be stored in ways that will hinder your work.

[maxx3]

Hi I don't know what to say now I tried what you said about the date range of each volume goes into the volumes tables but i'm not sure I did the right thing to be onest I feel lost and I don't know how to continue but at least the program work the think is if I have to add more info which I'm sure I will at one time are another and now I'm afraid that would not be possible and the whole data i would have entered will be invain

[maxx3]

Hi my friend I have been searching the web Like you suggested do you mind having a look at this and see if Have registered I any progress Please thanks

[CraigDolphin]

Hi,

Not really. Take a look at this example. This shows you the basic concept of using a normalized table design, and bring all the elements together in a query, using the parameters we've already discussed.