Comparing part data in columns

[djchapple]

I have a table in a database that contains the following columns:

1. ID (automatically generated)
2. Month/Year (contains month and year such as "Aug 2011")
3. Day (such as "Sunday")
4. Date (such as "28")
5. RealDate (such as "Sun 28 Aug 2012")

The first three columns were created by the orginal user of the daterbase, I know not why, as they have to be entered separately and cannot be used for sorting

I have inserted column 4 manually which is just a real date.

What I now want to do is to ensure that the RealDate column contains the same date as in colums 2, 3 and 4. As only one column contains a date the comparison must be doen on a text basis

So I need an expression in a query to see if the data held in columns 1, 2 and 3.

So I need I need an expression which is like "if =((column 5) contains (column 2) and (column 3) and column 4)) then"OK" else "Error"

Or I could concatenate columns 2, 3 and 4 and then compare with 5 but I would have to ensure that the layout of both colum was exactlt the same.

I have seen the Like criteria, such as Like "*2012*" but the number here varies from record to record. What I would need is Like "*Column 3*"

Can this be done without too much effort?

 

[jdraw]

I'm not sure why you entered data manually?

You could create the data you need from the data in the table. And you are going to do most of the work when you validate the data you have entered.

Here's the basis of a query to do some checking of the values involved.

SELECT dChapple.monthYear
, dChapple.myDayofWeek
, dChapple.myDate
, dChapple.rDate
, Trim([MyDate]) & "/" & Left([monthYear],3) & "/" & Right([monthyear],4) AS Expr1
, Format([expr1],"dd/mm/yyyy") AS exp2, Format(Right([rdate],11),"dd/mm/yyyy") AS Exp3
, IIf([exp3]=[exp2],"Yes","No") AS IsMatch, WeekdayName(Weekday(([exp3]))) AS exp4
FROM dChapple;

There are 3 jpgs attached.

TableDesign showing the fields
TableValues showing my test data
QueryResult show the query output

Also Day and Date are reserved words in Access.

 

[MarkK]

What is the data type of your RealDate column? Also is this all that is in this table: date information?

 

[djchapple]

I'm not sure why you entered data manually?

.

It's very simple. A few weeks ago I had never used Access and knew absolutely nothing about it. Now I have reached the dizzy height of knowing almost nothing about it.

I can construct a simple Table, Form, Query and Report but that is it. I know nothing whatsoever about SQL but I am willing to learn.

So what I need is to attend a basic but thorough course on Access. Until then I will be doing nearly everything wrong and asking very stupid questions.

If this offends you then please avoid all posts I may make in the near future.

If however you can bear with me then I will be very grateful for any help you can give me. Please remember you are communicating with an aboslute Access idiot.

 

[djchapple]

I'm not sure why you entered data manually?

You could create the data you need from the data in the table. And you are going to do most of the work when you validate the data you have entered.

Here's the basis of a query to do some checking of the values involved.



There are 3 jpgs attached.

TableDesign showing the fields
TableValues showing my test data
QueryResult show the query output

Also Day and Date are reserved words in Access.

Thank you very much for this in depth help. It certainly seems to be what i am looking for.

I will look into the suggestions and try to fully understand what is happening. Unfortunately I am starting from a very low base.

 

[djchapple]

What is the data type of your RealDate column? Also is this all that is in this table: date information?

The format is that of a date, specifically - mmm yyyy - ddd d (the same specifcation as the dates shown below)

The main core date in the table is
1. special info about that day (Christmas day, Special Event etc)
2. two volunteers on duty that morning
3. two volunteers on duty that afternoon

My main interest with this post is to get the date entered more easily - at the moment it is in three separate columns

1. Month year (Aug 2011)
2. day of week (Sun)
3. date of month (28)

 

[djchapple]

I entered the informatio below based on the example given above by jdraw.

SELECT [Table Rota].ID (Rem Correct name – not used)
, [Table Rota].[Month/Year (Rem Correct name – used)
, [Table Rota].Day (Rem Correct name - Compare with Expr4)
, [Table Rota].Date (Rem Correct name – used)
, [Table Rota].RealDate (Rem Correct name – used)
, [Table Rota].CalculatedDate (Rem Correct name – not used)
, Trim([Date]) &"/"& Left([Month/Year],3) &"/" & Right([Month/Year],4) AS Expr1
, Format([Expr1],"dd/mmm/yyyy") AS Expr2
, Format(Right([RealDate],11),"dd/mmm/yyyy") AS Expr3
, IIf([Expr3]=[Expr2],"Yes","No") AS IsMatch (Rem IIf – capital i capital i lowercase f)
, WeekdayName(Weekday(([Expr3]))) AS Expr4
FROM [Table Rota];

To my amazement i found that ir worked - after correcting several typos.

It's amazing what a team effort can do - 99.9% jdraw and 0.1% me!!


It was mentioned right at the start that I could have used something similar to populate a field in the original table.

Say I calculate some expression (say Expr2) from certain fields in a table then how do I insert the values, stored in Expr2, into an existing field in the table.

For example my new query calulates a date in Expr2. How would I insert that date into a field called (say) CalulatedDate.

I am raising this point as I would like to automatically calulate a date in the table rather than manually inserting it.

 

[jdraw]

You would use an Update query to modify a field in an existing table.

 

[MarkK]

The format is that of a date, specifically - mmm yyyy - ddd d (the same specifcation as the dates shown below)

But how a date is formatted for display purposes and how it is stored in a table are different things. In a table you can create a date field, so the datatype of the field itself is DateTime. In this case the field will not allow entry of information that is not a valid date.
In addition, if you are storing a date in a field, it is not recommended to store other versions or formats of the same information in a different field. If you have two versions of the same data and an update fails or someone not familliar with the system changes something you run a risk of having data in your system be in conflict with itself. This is a kind of error you want to avoid at all costs, and the solution is simple: never store duplicate information. Take a look at this...

1. ID (automatically generated)
2. Month/Year (contains month and year such as "Aug 2011")
3. Day (such as "Sunday")
4. Date (such as "28")
5. RealDate (such as "Sun 28 Aug 2012")

See how there are four fields that contain different versions of the same data point? Which one is authoritative? If there is a conflict, which one is right? And if you update one, you will definitely and tediously have to update one or more of the others. So store a single date in a single field. If when you retrieve it you want it formatted a certain way, do that then, at retreival time, not when you store it.
Just a few thoughts,
Mark

 

[djchapple]

But how a date is formatted for display purposes and how it is stored in a table are different things. In a table you can create a date field, so the datatype of the field itself is DateTime. In this case the field will not allow entry of information that is not a valid date.
In addition, if you are storing a date in a field, it is not recommended to store other versions or formats of the same information in a different field. If you have two versions of the same data and an update fails or someone not familliar with the system changes something you run a risk of having data in your system be in conflict with itself. This is a kind of error you want to avoid at all costs, and the solution is simple: never store duplicate information. Take a look at this...

See how there are four fields that contain different versions of the same data point? Which one is authoritative? If there is a conflict, which one is right? And if you update one, you will definitely and tediously have to update one or more of the others. So store a single date in a single field. If when you retrieve it you want it formatted a certain way, do that then, at retreival time, not when you store it.
Just a few thoughts,
Mark

Thanks for the guidance on dates. I understand your concerns but would like to point out that the table is in a transitional stage. The three separate fields containing the fragmented date are the original method for storing the date. The originaa designer of the database was (like me) a real novice and did not understand date formatting so three columns were used to p[resent the date in the preferred format.

The fourth column storing the complete date is my idea of simplifying data entry into the table.

Once I am satisfied that the new version historic dates have been entered correctly I will remove the three fields containing the fragmented dates. This will then leave only one field containing the date.

We are interested in doing some research into the volunteering and have a field with a seachable date will be very beneficial.

 

[jdraw]

If you look at the jpgs I sent earlier, you see that the last field in the desing is mDate -- a date/time datatype to hold the date involve in date time format. I even put the description in to highlight this.

You can create an update query, once you have resolved/reconciled NoMatch entries, then update the mDate field and adjust how you save the Dates involved.

But the original question was how to compare the various fields.
So, reconcile the differences until you are happy, then set up an Update query to update mDate. At that point you'll have to adjust how Dates are entered --adjust the process.....

Good luck.