I am making this database for a project that has to update monthly revenue and create a Journal Entry based on a business ledger report for different revenue categories (streams). I have created an import table that imports from recurring excel source (containing ledger report) and updates the main table that holds the current month records. The records represent revenue by client campaign, each having a unique Campaign Master IDs (my primary key). It is all pretty straightforward and being a novice I am pretty proud I managed to create this automated monthly update using these two tables and a pair of queries all running within a simple macro.
However, the problem I run into is due to specific requirements for the Journal Entry format that actually needs to be derived from the input format (that I import from Excel and maintain in my main update table).
Journal Entry has to be have several columns that have to be
-populated by text or numerical constants
(column 1 should always read "General",
column 2 should always read "Revenue",
column 5 should always read "invoice",
column 15 should alwaye read "0100")
-blank placeholders (with null values)
(columns 11 & 12)
-value based on coversion table
(column 14)
-either invoice number from input field or (if invoice # is blank) a composite of a posting date and account number
(column 6)
-either customer or G/L account type based on the type of customer
(column 7)
etc.
What determines these is basically whether I have values in the import table columns that determine the type of revenue so I guess my question is how to write a query that performs all these special operations (I described above). I have a feel these are some sort of IF statements but how exactly to implement this logic is what I am struggling with.
I also attached my database in case my descriptions are not all that clear.
tblExportJournalEntry is what I should end up with after processing my tblMainUpdateTable the way I tried to explain above.
I really appreciate any help and am grateful in advance.