I have a select query that uses an "OR" join. With that join in place, I cannot edit the data in the query datasheet; if I delete the "OR" and only use one join, then I can edit the data. I read this article that explains I can't edit because of the SQL specific statement. Is there a way around this so that I don't have to open this query, look for what I want to change, then go back and open the table to edit? Thanks.
-
-
Please post the sql statement and any relevant table structures.
Regards,
Scott -
Code:SELECT Harrison.[Transaction Date], Harrison.[Transaction #], Harrison.Customer, Harrison.Aircraft, Harrison.FuelType, Harrison.Quantity, Harrison.[Unit Price], Harrison.Extended
Code:FROM Harrison LEFT JOIN [Aircraft Table] ON Harrison.Aircraft = [Aircraft Table].TailNumber OR Harrison.Aircraft = [Aircraft Table].NTailNumberN
The query works great. Without the second join of NTailNumberN, I can directly edit the Aircraft cell in the query, which changes the value on the table. But with the OR join, Access beeps at me when I try to edit the cell.Code:WHERE ((([Aircraft Table].TailNumber) Is Null));
Comment
-
Why two Tailnumber and NTailNumberN fields?
Is Tailnumber the foreign key that forms the basis for the relationship between these two tables? If so, what is the NTailNumberN field doing?
I assume that Harrison.Aircra ft refers to the primary key of the Harrison table?
Using a left join can be problematic when one side of the join is null, as you are finding out. Can you give me a better idea of the larger picture of what you are trying to accomplish with this?
Regards,
ScottComment
-
Sorry my post wasn't detailed in the purpose of the query; the query is working fine, so I didn't think to explain. Basically, I have fuel purchase data coming from a vendor (Harrison) and I need to check the tail numbers of the aircraft against a list. That list has two forms of tail numbers (one without a prefix "N", one with). I have both because sometimes the data has the N deleted, sometimes not. Rather than scrub the data, I wanted to just have a flexible query. With this query, I find the tail numbers that do not match our list. I then log it as an actual error, or I edit the tail number data from the Harrison table (often the number is transposed, or slightly off). It's much easier to edit the data from the query rather than going to the table, searching, and editing.
So I need to check the aircraft tail numbers, that they match the list in TailNumbers or NTailNumbersN. I used the OR Join, which works, but it does not allow me to edit data directly from the query datasheet.Comment
-
Well, usually speaking the 'easiest way' comes back to bite you in the hinder parts later on. As you already have seen, a database is only as good as the validity of the data you give it. Designing a database with validation rules in place to prevent inaccurate data is one of the most important tasks a person faces when building one.
I would suggest you re-think the logic behind not scrubbing the data to begin with, and perhaps even requiring more accurate reporting from the vendor as well! After all, if they send you a claim for a mistakenly entered tail number, it really isn't your job to fix their error. When they understand the economic incentive for more accurate reporting (don't pay them if it's not your airplane :-), they will be much more likely to accurately record the tail numbers.
With all that said, have you tried rewording the join part of your criteria to make use of an IIF() structure? I.e. [CODE=sql]LEFT JOIN [Aircraft Table] ON Harrison.Aircra ft = IIF(IsNull([Aircraft Table].Tailnumber, [Aircraft Table].NTailNumberN, [Aircraft Table].Tailnumber)[/CODE]
This would create the join based on the Tailnumber field, unless it is null, in which case it would default to the NTailNumberN field.
Regards,
ScottComment
-
Scott,
Some info about the data and reason for the query. The point of the query is to find tail numbers that aren't ours so we can withhold payment. We get data in from many different vendors, some of which use the N prefix, some of which don't, and some of which use both. The both scenario (I think) rules out the IIF (unless I'm not using it correctly).
Because data is coming in from different people in different formats, custom scrubbing beforehand would cause additional steps, rather than just a specific SQL statement. Again, there is technically no "issue" with what I already have; I would just like to be able to edit the datasheet from the query rather than go back to the table.
We are trying to get better data from our vendors, but that's a task in and of itself!Comment
-
It's a continual struggle, isn't it? :-) Getting accurate data from vendors, that is...
With the 'both' scenario, you might run into a problem with the IIF(), but shouldn't be insurmountable. .. I haven't tested it to see yet, but if the 'both' means that you simply have the same tail number replicated in two different fields, with the one exception being that the second field includes the N prefix and suffix, then you can nest a LIKE statement inside the False condition of the IIF, which means that your search will be based on the tail number WITHOUT the N's. If not found in the first field it will look for the same number combination in the second column.
I'll get back to you on this later today, as I'm busy at the moment, but this is the direction I would suggest you pursue.
Good luck!
Regards,
ScottComment
-
This is untested, but this is more specifically what I would try:
[CODE=sql]
LEFT JOIN [Aircraft Table] ON Harrison.Aircra ft = LIKE(IIF([Aircraft Table].Tailnumber <> Harrison.Aircra ft, [Aircraft Table].NTailnumberN, [Aircraft Table].Tailnumber))[/CODE]
As I said, this is untested, so will probably need tweaking to get the performance you want.
Regards,
ScottComment
-
I'm getting a syntax error on the following, can't find the problem.
[CODE=sql]SELECT Harrison.[Transaction Date], Harrison.[Transaction #], Harrison.Custom er, Harrison.Aircra ft, Harrison.FuelTy pe, Harrison.Quanti ty, Harrison.[Unit Price], Harrison.Extend ed
FROM Harrison LEFT JOIN [Aircraft TABLE] ON Harrison.Aircra ft = LIKE(IIF([Aircraft TABLE].Tailnumber <> Harrison.Aircra ft, [Aircraft TABLE].NTailnumberN, [Aircraft TABLE].Tailnumber))
WHERE ((([Aircraft Table].TailNumber) Is Null));[/CODE]
I've used LIKE before, but not an expert on it. Wondering if it would cause an issue. The tail number with the N is actually just a prefix. If a tail number on my list is N314, and I use like, would data of N3148 show as a match? I wouldn't want it to. Thanks for all your help.Comment
-
Alternatively, you can strip out the N by using the Replace() function.
will result in 3148.Code:Replace("N3148N", "N", "")
This assumes that both your Harrison.Aircra ft and [Aircraft Table].NTailNumberN fields are text fields. If one is a numeric data type field, you will have to use the CLng() function to change the text string returned by the Replace() function into a Long Integer.
That would look something like this:
[CODE=sql]
SELECT *
FROM Harrison LEFT JOIN [Aircraft Table] ON IIF(IsNull([Aircraft Table].Tailnumber), Harrison.Aircra ft = Replace([Aircraft Table].NTailNumberN, "N", ""), Harrison.Aircra ft = [Aircraft Table].Tailnumber);
[/CODE]
However, in testing just now, I see that this returns a non-updateable query as well :-(.
Changing this into a Make-table query allows the resultset to be updateable, I'm not sure if that would be an acceptable solution for you, as it would result in a little more design work, but done properly would be seamless from the user's point of view. Let me know if this is an option for you.
Regards,
ScottComment
-
I'd like to try a make-table query, as I don't have much experience with that. Would I be able to take my current SQL and "change" it to make table by adding SELECT INTO? That seems like it would delete the table and replace with only the errors I find. Please help!Comment
-
An overview first:
You can change your current query into a make-table query (which allows you to determine a new table name to select the data into). From there, you can create a form (if you aren't currently using one for viewing/correcting the data) and base it on this new, temporary table. Then, after changing the data, you will use an update query to write the corrected data back into your main table/tables.
The make table query looks like this:
[CODE=sql]SELECT * INTO tmpTable
FROM Harrison LEFT JOIN [Aircraft Table] ON IIf(IsNull([Aircraft Table].Tailnumber) Or Harrison.Aircra ft<>[Aircraft Table].Tailnumber,Har rison.Aircraft= Replace([Aircraft Table].NTailNumberN," N",""),Harrison .Aircraft=[Aircraft Table].Tailnumber);
[/CODE]
Your data correction form will be in continuous form view (unless using datasheet view is particularly important for you, either to make as few visual changes for other users, or some other preference). Bind the form to the tmpTable by the usual method of setting the record source for the form.
Then in the AfterUpdate event of the form you will write the code to call a hard-coded update sql statement.
That will look like this:
[CODE=vb]
Option Compare Database
Option Explicit
Private Sub Form_AfterUpdat e()
Dim strSQL As String
'Application.Se tOption "Confirm Action Queries", False
strSQL = "UPDATE Harrison SET Aircraft = " & Me.txtHAircraft & ";"
DoCmd.RunSQL strSQL
'Application.Se tOption "Confirm Action Queries", True
End Sub[/CODE]
Notes: this code sample is an extremely simple example, and will need tweaking to fit your database. I left the Application.Set Option lines commented out, but you can un-comment them to turn the query confirmation messages off, without interfering with any error messages that might result from the update query.
Let me know if you need more information. At this point I'm just pointing you in the right direction :-)
Regards,
ScottComment
-
Scott,
Thanks for all your help. I'd be lost without it!Comment
-
Not a problem! That's one of the primary joys of volunteering here :-)
Regards,
ScottComment
Comment