And since the last time I commented was about considering locale in questions... ;-)
I'll add that to internationalise the expression - you can use
Me.Fiscal_ID = Dlookup("Fiscal_ID", "qryFiscal", "[AuditDate]=" & Format(Me.AuditDate,"\#yyyy\-mm\-dd\#"))
(That way we non-US types get the date internationalised and then nobody "wins" ;-)
Though for some locations, you'll again no doubt need those pesky semi colons as parameter separators. :-s
Ooooo.. so I might have had it right?.. but since I didnt CHANGE the field..
Set the After Update property of the Quantity text box to [Event Procedure].
Click the Build button (...) beside this. Access opens the Code window.
Enter this line between the Private Sub... and End Sub lines:
Private Sub Quantity_AfterUpdate()
Me.Fee = Round(Me.Quantity * Me.UnitPrice * 0.1, 2)
End Sub
Set the After Update property of the UnitPrice text box to [Event Procedure], and click the Build button.
Enter this line:
Private Sub UnitPrice_AfterUpdate()
Call Quantity_AfterUpdate
End Sub
Now whenever the Quantity or UnitPrice changes, Access automatically calculates the new fee, but the user can override the calculation and enter a different fee when necessary.
I've not looked at your sample - but upon summary glance here it looks like you're performing the quintessential violation of normalisation and storing a calculated (and therefore dependant) field.
You can reproduce that calculation at any time onscreen, in a report or in a query by using that same calculation in an expression which references the calculating fields.
But you don't then have to worry about this process of ensuring the calculated value is stored (/updated) in a timely manner.
I'm not saying normalisation is never departed from, but it's for good reason when it is (once you're already fully aware of it and know when the alternative is better).
All right. I will do some research today, but if i could just get someone to look at the sample db, they would understand why I'm confused. I just do not know how to do what I'm trying to ask... I've got a query on a table.. that selects a record based on a >= startDate, and a <= endDate criteria, and I'm trying to ensure that the result is linked to the query to which the original selection was based on.
UPDATE:.. wow.. now I feel stupid.. maybe a good night's sleep?.. but I just figured it out, first try.
Code:
SELECT tblDataEntry.Unique_ID, tblDataEntry.Auditor_ID, tblAuditors.AuditorName, tblAuditors.AuditorType, tblDataEntry.Series_ID, tblSeries.SeriesDesc, tblSeries.Department_ID, tblDataEntry.QtyMade, tblDataEntry.QtyRejected, tblDataEntry.Defect_ID, tblDefects.DefDesc, tblDefects.DefType, tblDataEntry.SO_Nbr, tblDataEntry.AuditDate, tblDataEntry.AuditTime, tblDataEntry.Comments, tblDataEntry.Dispo_ID, tblDispo.DispDesc, tblDispo.DispSev, tblFiscalCalendar.FiscalStart, tblFiscalCalendar.FiscalStop, tblFiscalCalendar.Fiscal_ID
FROM tblFiscalCalendar, tblSeries INNER JOIN (tblDispo INNER JOIN (tblDefects INNER JOIN (tblAuditors INNER JOIN tblDataEntry ON tblAuditors.Auditor_ID = tblDataEntry.Auditor_ID) ON tblDefects.Defect_ID = tblDataEntry.Defect_ID) ON tblDispo.Dispo_ID = tblDataEntry.Dispo_ID) ON tblSeries.Series_ID = tblDataEntry.Series_ID
WHERE (((tblFiscalCalendar.FiscalStart)<=[AuditDate]) AND ((tblFiscalCalendar.FiscalStop)>=[AuditDate]));
WHERE (((tblFiscalCalendar.FiscalStart)<=[AuditDate]) AND ((tblFiscalCalendar.FiscalStop)>=[AuditDate]))
I don't know why, I just for some reason was convinced last night that you could not do it that easily. Problem solved. On to next challenge.
Whoa.. I am back in the frmDataEntry again, which is based on qryDataEntry, and although everything ~looks~ perfect -- when I try to modify a record it won't let me.
You're including a table without it being joined to any other table. That renders your resultset non-updatable.
What data do you want included from tblFiscalCalendar and why? (And how does it relate to the data selected from the other tables?)
I've got a query on a table.. that selects a record based on a >= startDate, and a <= endDate criteria, and I'm trying to ensure that the result is linked to the query to which the original selection was based on.
The DataEntry table contains AuditDate. The qryFiscal was originally created to determine what FiscalMonth to assign the audit date to. I used to have two sepearate queries, but today (with my ephipany) I thought I solved the problem. I do not know any way to actually link the fields (because they are based on a >= startDate, and a <= endDate criteria), which is why I asked my original question earlier today, and several times last week.
Essentially I want to ability to filter by FiscalMonth (in a report), and also show FiscalMonth on the frmDataEntry.
Yeah I could see all that in the example MDB.
But why are you merging the results. In what way are they related?
Do you always expect only one result from tblFiscalCalendar to be returned into the query? Or multiple results - which would cause a cartesian result in your query.
You can never expect updatablility in such circumstance. How can the DB engine know what it's to update with many copies of the same row?
Yes. My intent is to lookup the correct FiscalMonth based on the AuditDate.
Ah, there is a problem though. I have 214 records in tblDataEntry, and only 195 records in qryDataEntry.
So I am back to my original request. Please see post #17. How do I get the FiscalMonth linked to the tblDataEntry so I can filter by FiscalMonth (in a report), and also show FiscalMonth on the frmDataEntry. .. Is there a way to code the request without have the "record is not updateable" error?
And whatever is causing:
214 records in tblDataEntry, and only 195 records in qryDataEntry
--- has nothing to do with this discussion, because I took the offending content out of the qryDataEntry is based on. It appears at first glance that none of the "99 - No Defect Found" codes show up in the qry for some reason.
What about Join Properties?.. when you have so many tables linked together.. is there something I should have done here?
Ok.. I am still "record is not updateable" but .. I do at least have the 214 vs 195 fixed. None of the "99 - No Defect Found" had the Dispo code in the table.
It's weird -- I removed the fields from the query. Do I have to run some fix routine? Is it corrupted or something?
Sorry but I feel that in situations like this I think you'll need to step back a bit.
You're coming across as just throwing things at the problem until something sticks.
You'll get yourself more and more into a pickle - when it's the underlying concepts that you need to become familiar with. Then everything will make more sense with regards to implementation.
What I was saying earlier about joining an updatability.
I'll re-iterate and you'll need to have a think about it.
Take two tables.
TABLE1 and TABLE2.
If they have a key in common then if you join the tables on that key field the database engine is able to maintain updatability in a joined query (as it knows how one relates to the other and therefore the whole resultset is updatable).
If they are not related - then the database engine can infer nothing about one from the other. It makes no guesses, interprets nothing.
It will display the data - but it will be read only because it produces a cartesian product result of rows (i.e. if you had 10 rows in TABLE1 and 3 rows in TABLE2 then instead of 10 rows of both sets of fields you'll get 30 rows of both sets of fields).
As soon as you introduce a non-joined table to the query the results are non-updatable.
It doesn't matter that the rest of your tables are joined. They're now in a cartesian product result with that single non-joined table.
I see you've understood why the number fell to 195 (joining does that - INNER JOINS are a default way of limiting results).
But if you happened to have 2 rows in the FiscalMonth table then you'd have 390 rows showing instead! (It just so happens that 1*195 is 195 - but the result is still a cartesian product).
If you want a row's results from the FiscalMonth table to display on your form - then include it as a subform, or lookup values from it in code.
Including it in the query is going to affect updatability.
Now looking at your query in detail - you're including parameters for the FiscalMonth table using fields in tblDataEntry.
This is what's called a Theta Join and in Jet is as non-updatable as no join at all.
(SQL Server, for example, offers some updatable support for them - clever sod that it is).
But your joins must still ithen involve a key field for updatability to be maintained.
In this case you're just using standard fields - or partial PKs.
For example - your query could be restructured to involve an ANSI join...
Code:
SELECT
tblDataEntry.Unique_ID,
tblDataEntry.Auditor_ID,
tblAuditors.AuditorName,
tblAuditors.AuditorType,
tblDataEntry.Series_ID,
tblSeries.SeriesDesc,
tblSeries.Department_ID,
tblDataEntry.QtyMade,
tblDataEntry.QtyRejected,
tblDataEntry.Defect_ID,
tblDefects.DefDesc,
tblDefects.DefType,
tblDataEntry.SO_Nbr,
tblDataEntry.AuditDate,
tblDataEntry.AuditTime,
tblDataEntry.Comments,
tblDataEntry.Dispo_ID,
tblDispo.DispDesc,
tblDispo.DispSev,
tblFiscalCalendar.Fiscal_ID
FROM
(tblSeries
INNER JOIN
(tblDispo
INNER JOIN
(tblDefects
INNER JOIN
(tblAuditors
INNER JOIN
tblDataEntry ON tblAuditors.Auditor_ID = tblDataEntry.Auditor_ID
) ON tblDefects.Defect_ID = tblDataEntry.Defect_ID
) ON tblDispo.Dispo_ID = tblDataEntry.Dispo_ID
) ON tblSeries.Series_ID = tblDataEntry.Series_ID
)
INNER JOIN
tblFiscalCalendar ON (tblFiscalCalendar.FiscalStart<=tblDataEntry.[AuditDate]
AND tblFiscalCalendar.FiscalStop>=tblDataEntry.[AuditDate])
This will give you the same results back (with perhaps slightly better performance under Jet - perhaps not).
But they will still be read only.
It may be that your requirements offer a WHERE clause method of returning your records and maintaining updatability. But who knows what those requirements are.
Restating the fundamental requirements and the objects involved - in a slow, considered manner, will get you further than firing things off and hoping to hit.
The problems as I see it are that you're using a field in a table to determine the limits for the tblFiscalCalendar table. And yet you're expecting only one row back from that table - yet the criteria is drawn from many rows in the related table. :-s
So you're requiring that every row providing criteria in tblDataEntry has the same date entered in it?
Leigh, I did step back, and I am man enough to admit I was wrong.
In my effort to get the project done, I spent a good deal of my time with a migraine headache, convinced that I needed to set up some sort of relationship between tblDataEntry and tblFiscalCalendar, knowing all along that no such relationship existed.
It finally dawned on me that figuring out some way to calculate a field in a query, or do some sort of fancy SQL was totally not required. Here's how I solved the problem.
If you only needed single values from the Fiscal table to act as parameters then form control parameters are absolutely standard. (Or a function call in the query which retrieves those single values - there's no real penalty for doing that instead).
Glad you got your requirements sorted and up and running.
