• Status: Solved
  • Priority: Medium
  • Security: Public
  • Views: 133
  • Last Modified:

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
0
pdvsa
Asked:
pdvsa
  • 6
  • 4
2 Solutions
 
Dale FyeCommented:
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.
0
 
pdvsaProject financeAuthor Commented:
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_Or_Notice, Imported_Excel.Receivor, Imported_Excel.Section_Referenced, Imported_Excel.Description, Imported_Excel.ResponsibleParty, Imported_Excel.End_Start_Date_Comments, Imported_Excel.Comments
FROM Imported_Excel;
0
 
Dale FyeCommented:
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?  "
0
Concerto's Cloud Advisory Services

Want to avoid the missteps to gaining all the benefits of the cloud? Learn more about the different assessment options from our Cloud Advisory team.

 
pdvsaProject financeAuthor Commented:
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
0
 
Gustav BrockCIOCommented:
I think you should first import all receivors by name from Excel to tblReceivor.

Then adjust your append query to inner join between the table of imported reports and tblReceivor on the name of the receivor. Then, from tblReceivor, you would pull the ID, not the receivor name, into tblReports.

/gustav
0
 
Dale FyeCommented:
Sorry, was not sure I understood what tblReceivors was for.

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_Or_Notice, tblReceivor.ID, Imported_Excel.Section_Referenced, Imported_Excel.Description, Imported_Excel.ResponsibleParty, Imported_Excel.End_Start_Date_Comments, Imported_Excel.Comments
FROM Imported_Excel
LEFT JOIN tblReceivor ON Imported_Excel.Receivor = tblReceivor.Receivor

Because of the LEFT JOIN, any of the Receivor values in the Excel file that are not found in tblReceivor would result in NULLs in the ID field.  So, before actually doing the insert, I would check that field for values not found in tblReceivors.

SELECT DISTINCT Imported_Excel.Receivor
FROM Imported_Excel
LEFT JOIN tblReceivor
ON Imported_Excel.Receivor = tblReceivor.Receivor
WHERE tblReceivor.ID IS NULL

If this returns records, you can display a message indicating the values that are unacceptable and provide the opportunity to fix the Excel file before proceeding, or you could simply perform the import and then provide a way for the user to fill in the NULL values in tblReporting.
0
 
pdvsaProject financeAuthor Commented:
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
0
 
pdvsaProject financeAuthor Commented:
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...
0
 
pdvsaProject financeAuthor Commented:
I think Dale is pulling in the ID...I can't read code that well tho... :)
0
 
Dale FyeCommented:
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.
0
 
pdvsaProject financeAuthor Commented:
Thank you.  Forgot about this one
0

Featured Post

VIDEO: THE CONCERTO CLOUD FOR HEALTHCARE

Modern healthcare requires a modern cloud. View this brief video to understand how the Concerto Cloud for Healthcare can help your organization.

  • 6
  • 4
Tackle projects and never again get stuck behind a technical roadblock.
Join Now