Append and Switch?
Experts,
I have an Append query. I am appending from Excel.
The table (tblReport) I am Appending to has a field [Receivor] with a property of Number, combo box and a row source query of: SELECT tblReceivor.ID, tblReceivor.Receivor FROM tblReceivor ORDER BY tblReceivor.Receivor;
tblReceivor has 26 records.
How to Append this text data from Excel [Receivor] to this field tblReport.[Receivor] with a Number ppty? I know a Switch statement would work however I think SWITCH is more practical for <5 options or so and is not very practical for 26 options to account for. I imagine there is an easier way.
thank you
I have an Append query. I am appending from Excel.
The table (tblReport) I am Appending to has a field [Receivor] with a property of Number, combo box and a row source query of: SELECT tblReceivor.ID, tblReceivor.Receivor FROM tblReceivor ORDER BY tblReceivor.Receivor;
tblReceivor has 26 records.
How to Append this text data from Excel [Receivor] to this field tblReport.[Receivor] with a Number ppty? I know a Switch statement would work however I think SWITCH is more practical for <5 options or so and is not very practical for 26 options to account for. I imagine there is an easier way.
thank you
Dale Fye
If I've got that many values to define new values for, I usually create a lookup table and just join the Excel file to that table and pull in the value from the lookup table.
ASKER
Hi Dale, could you give me a little more explanation? I am thinking the lookup table is the table I have now (tblReceivor). I dont quite get the setup of the Append and the lookup table. Not certain if you are saying I need to have this lookup table in the Append query. Thank you
here is my append query if it might make more sense:
INSERT INTO tblReport ( Timing, RequiredAttendee, Label, Deliverable_Or_Notice, Receivor, SectionReferenced, Description, ResponsibleParty, End_Start_Date_Comments, Comments )
SELECT Imported_Excel.Timing, Imported_Excel.[Required Attendee], Imported_Excel.Label, Imported_Excel.Deliverable
FROM Imported_Excel;
here is my append query if it might make more sense:
INSERT INTO tblReport ( Timing, RequiredAttendee, Label, Deliverable_Or_Notice, Receivor, SectionReferenced, Description, ResponsibleParty, End_Start_Date_Comments, Comments )
SELECT Imported_Excel.Timing, Imported_Excel.[Required Attendee], Imported_Excel.Label, Imported_Excel.Deliverable
FROM Imported_Excel;
Where were you thinking of using the Switch( ) function in this SQL?
What did you mean by:
"How to Append this text data from Excel [Receivor] to this field tblReport.[Receivor] with a Number ppty? "
What did you mean by:
"How to Append this text data from Excel [Receivor] to this field tblReport.[Receivor] with a Number ppty? "
ASKER
The Switch would have been placed on the Receivor field. (Typing from phone). For example if the record was "bank" in the excel then the number 1 would be put in the field instead of "bank". Very crude example but I think you know what I mean.
Not sure if that answers your question. Let me know
Not sure if that answers your question. Let me know
SOLUTION
membership
This solution is only available to members.
To access this solution, you must be a member of Experts Exchange.
ASKER CERTIFIED SOLUTION
membership
This solution is only available to members.
To access this solution, you must be a member of Experts Exchange.
ASKER
Thank you Dale. I think I get it now. Will test in a bit once get in front of the computer.
Have a good day
Have a good day
ASKER
Hi GUSTAV, I didn't see your response.
I think you hit the nail on the head. I have to pull in the ID on tblReceivor.
Not sure if Dale said that in the above tho through code.
On my way to office. Will test after awhile...
I think you hit the nail on the head. I have to pull in the ID on tblReceivor.
Not sure if Dale said that in the above tho through code.
On my way to office. Will test after awhile...
ASKER
I think Dale is pulling in the ID...I can't read code that well tho... :)
Yes, my recommendation would pull in the ID value rather than the Receivor value into tblReports. As I said, if there are invalid values in your Excel files Receivor column, they would show up as NULL in tblReports.
ASKER
Thank you. Forgot about this one