Append new data into Both Parent and Child

[mounty76]

Hi All,

I got 3 tables:

tblSubscription
tblExpenses
tblLineItems

tblExpenses and tblLineItems are parent and child, connected via the ExpenseID (autonumber in expense table and foreign key in lineitem)

tblSubscription holds information some of which is for tblExpense and some for tblLineItem.

How can I make an append query to update both tables? I could do two append queries but not sure how to pass the correct ExpenseID across to the child table?

Any ideas most welcome!

 

[GPGeorge]

Sorry, but the description of these tables doesn't sound right.

How can a subscription apply to both an expense and an individual line item? Please explain how this is supposed to work, with examples.

How, for example, would ONE subscription apply to an expense, which in turn has multiple line items, each with different subscriptions? Again, examples can help clarify what's supposed to be going on.

 

[ebs17]

You absolutely need an append query per table.
If the primary keys are based on autovalues, it is quite clear.

INSERT INTO
   tblExpenses(
      UniqueAttributExpense
   )
SELECT DISTINCT
   UniqueAttributExpense
FROM
   tblSubscription

INSERT INTO
   tblLineItems(
      ExpandeID,
      UniqueAttributLineItem
   )
SELECT
   E.ExpandeID,
   S.UniqueAttributLineItem
FROM
   tblSubscription AS S
      INNER JOIN tblExpenses AS E
      ON E.UniqueAttributExpense = S.UniqueAttributExpense

In practice, an inconsistency check would be added in order to only enter new records.

 

[gemma-the-husky]

@GPGeorge is right.

The whole point of a database is the relationships between data elements

You add a record into table A
You THEN add multiple records into table B, each of which are related to an item in table A.

There are 2 distinct processes. If you have a record that you think needs to go in both table A and table B, then the data structures need to be considered more carefully, but in any event, you still need to create the parent record before any of the child records, so theoretically table A first, then table B.

 

[mounty76]

@GPGeorge and Gemma - These subscriptions only have one lineitem, however there are other expenses added to the expense table that have multiple lineitems per record (different budget codes and lineitems for one invoice), maybe the tables should be named tblInvoice and tblLineitem but essentially there are creditcard, cash, and invoices for expenses, some (not all) have more than one lineitem hence it structured like this.

The subscriptions are just a few per month, I'm just wanting to have a seperate subscription table that I can append once per month to the expenses and lineitem table so that it saves manually putting them in each month

 

[mounty76]

You absolutely need an append query per table.
If the primary keys are based on autovalues, it is quite clear.

INSERT INTO
   tblExpenses(
      UniqueAttributExpense
   )
SELECT DISTINCT
   UniqueAttributExpense
FROM
   tblSubscription

INSERT INTO
   tblLineItems(
      ExpandeID,
      UniqueAttributLineItem
   )
SELECT
   E.ExpandeID,
   S.UniqueAttributLineItem
FROM
   tblSubscription AS S
      INNER JOIN tblExpenses AS E
      ON E.UniqueAttributExpense = S.UniqueAttributExpense

In practice, an inconsistency check would be added in order to only enter new records.

Sorry not very good with SQL, can this be done in design view?

 

[GPGeorge]

@GPGeorge and Gemma - These subscriptions only have one lineitem, however there are other expenses added to the expense table that have multiple lineitems per record (different budget codes and lineitems for one invoice), maybe the tables should be named tblInvoice and tblLineitem but essentially there are creditcard, cash, and invoices for expenses, some (not all) have more than one lineitem hence it structured like this.

The subscriptions are just a few per month, I'm just wanting to have a seperate subscription table that I can append once per month to the expenses and lineitem table so that it saves manually putting them in each month

I'd have to see the relationship diagram and some sample data to follow that.

 

[mounty76]

I'd have to see the relationship diagram and some sample data to follow that.

 

[The_Doc_Man]

@mounty76 - the problem with what you described is that if you truly had such a relationship, you would terribly confuse the Access built-in "wizards" that help you write queries and event code. You would have a parent/child dependency - which is perfectly OK. But that third table has a path to both other tables and that leads to an ambiguous path. That drives Access bonkers when it tries to analyze that. The fact that you think you need this tells us that one of two things is true: Either (A) you are mistaken regarding the need or (B) your data schema is faulty.

I would respectfully suggest that you need to do some reading on database normalization, which would enlighten you regarding table structures and relationships. You can search this forum for "normalization" but if you search the general web, look for "database normalization" because the single word "normalization" is used in at least six major disciplines including math, chemistry, psychiatry, and international political relations.

As to writing SQL via Design view, that can happen. After all, if you left-click on the tab holding the design grid, you can change to Datasheet view BUT you can also change to SQL view. If you store the query, that SQL view is what will actually be stored. So sure, most of the time you can build a query in design view.

EDIT: Your post of your relationship diagram crossed with my response. I see nothing wrong with that diagram. So my thought is that your subscription data should affect one or the other of your tables, but not both. Which of the two tables is affected by subscription, and what is the intended effect?

 

[ebs17]

can this be done in design view?

Sure, theoretically yes. Practically not. I don't have your database with your tables (as you probably know). So I wrote the queries out of pure abstraction from a virtual database in my head, as code statements.
But I can't and don't want to paint pictures with rows and columns.

But you can switch from SQL view to design view, back and forth. That should be reasonable and affordable.
Logically, some field names would also have to be adapted to your reality.

not very good with SQL

Here would be an opportunity to improve this a little.

For explanation to third parties:
I understood the task to mean that the data from an Excel table (tblSubscription) should be split/transferred into two tables with a relationship.

 

[mounty76]

@mounty76 - the problem with what you described is that if you truly had such a relationship, you would terribly confuse the Access built-in "wizards" that help you write queries and event code. You would have a parent/child dependency - which is perfectly OK. But that third table has a path to both other tables and that leads to an ambiguous path. That drives Access bonkers when it tries to analyze that. The fact that you think you need this tells us that one of two things is true: Either (A) you are mistaken regarding the need or (B) your data schema is faulty.

I would respectfully suggest that you need to do some reading on database normalization, which would enlighten you regarding table structures and relationships. You can search this forum for "normalization" but if you search the general web, look for "database normalization" because the single word "normalization" is used in at least six major disciplines including math, chemistry, psychiatry, and international political relations.

As to writing SQL via Design view, that can happen. After all, if you left-click on the tab holding the design grid, you can change to Datasheet view BUT you can also change to SQL view. If you store the query, that SQL view is what will actually be stored. So sure, most of the time you can build a query in design view.

EDIT: Your post of your relationship diagram crossed with my response. I see nothing wrong with that diagram. So my thought is that your subscription data should affect one or the other of your tables, but not both. Which of the two tables is affected by subscription, and what is the intended effect?

These are the fields in the Subscription table, as you can see there is data that needs to go in both tables (expenses and lineitems). This isn't the end of the world as it is only a few items that need to go in each month, it is more frustrating when I cannot do it and I'd rather find a solution than give up. The SubCode field really isn't necessary.

The SubDate is the date the subscription started, my intention was to take the day of the subscription and apply it to the current month and year to give an 'ExpenseDate' the append query then can be manually activated (via a button) once a month to put the subscriptions into the expenses/lineitem tables.

The issue I have is that the ExpenseID is an autonumber and so not sure how I copy this into the lineItem table as a foreign key, making 2 append queries is easy and I can append the data I need into each table, I just need the ExpenseID for the second append?

Would it be best to (1) append subscription information into tblExpenses then use a query to find the correct records (ExpenseID) that have a date match of Day(SubDate) & Month(Now()) & Year (Now()) then append this into the forgien key in the LineItem table alongwith the other info required?

 

[GPGeorge]

View attachment 110830

These are the fields in the Subscription table, as you can see there is data that needs to go in both tables (expenses and lineitems). This isn't the end of the world as it is only a few items that need to go in each month, it is more frustrating when I cannot do it and I'd rather find a solution than give up. The SubCode field really isn't necessary.

The SubDate is the date the subscription started, my intention was to take the day of the subscription and apply it to the current month and year to give an 'ExpenseDate' the append query then can be manually activated (via a button) once a month to put the subscriptions into the expenses/lineitem tables.

The issue I have is that the ExpenseID is an autonumber and so not sure how I copy this into the lineItem table as a foreign key, making 2 append queries is easy and I can append the data I need into each table, I just need the ExpenseID for the second append?

Would it be best to (1) append subscription information into tblExpenses then use a query to find the correct records (ExpenseID) that have a date match of Day(SubDate) & Month(Now()) & Year (Now()) then append this into the forgien key in the LineItem table alongwith the other info required?

Thanks for including the Subscription table.

Unfortunately, I can't really see which data points from it need to go into both tables, though. Which values, specifically by field name, go in both tables?

 

[gemma-the-husky]

If an employee makes an expense claim, he has a single claim, representing multiple line items, one of which, let's say is a subscription. That's the way to look at it.

Now if you need to break this subscription into multiple analysis codes, you either do that in the line items table, by showing multiple line items although that would most likely give you a repeating group OR - you add a third table called line item analysis, linked to the line items, and never linked to the expense claim.

If you only want the sub-analysis on some occasions, you are still better having a single item for the analysis code, rather than try to have some analysis codes against the expense, some against line items, and some against a line items explosion. If that's what you are trying to do.

 

[mounty76]

Thanks for including the Subscription table.

Unfortunately, I can't really see which data points from it need to go into both tables, though. Which values, specifically by field name, go in both tables?

SubDate > tbl.Expenses.ExpenseDate
SubSupplier > tblExpense.Supplier
CCUser > tblExpense.Person

SubCurrency > LineItems.Currency
SubCost > LineItems.Cost
SubCode > LineItems.BudgetCode
SubDetails > LineItems.Description

 

[GPGeorge]

SubDate > tbl.Expenses.ExpenseDate
SubSupplier > tblExpense.Supplier
CCUser > tblExpense.Person

SubCurrency > LineItems.Currency
SubCost > LineItems.Cost
SubCode > LineItems.BudgetCode
SubDetails > LineItems.Description

As others point out, this appears to be an inappropriate table design. Here's why. If a subscription applies to one or more expenses, each of the other fields in that subscription MUST apply to that expense. Take currency, for example. If a Subscription is designated in Euros, then the expense record has to be in Euros, and in turn ALL lines items must also be in Euros. It's redundant to add a second field in the line item record to repeat that. Cost, SubCode and Details are the same.

If you can describe a specific case where one subscription COULD have different Currencies for different line items under a single expense item, we might have to dig further into the actual business rules, though. However, at this point, it's hard to see how that could happen.

 

[gemma-the-husky]

if you settle expenses in multiple currencies, maybe you need either

1. A separate claim for each currency. OR
2. Claim in a single currency only, and just keep a refence note of the calculation from the local currency.

Expenses wouldn't normally be significant enough to make a difference but I assume you have some company standard on this anyway.

 

[mounty76]

As others point out, this appears to be an inappropriate table design. Here's why. If a subscription applies to one or more expenses, each of the other fields in that subscription MUST apply to that expense. Take currency, for example. If a Subscription is designated in Euros, then the expense record has to be in Euros, and in turn ALL lines items must also be in Euros. It's redundant to add a second field in the line item record to repeat that. Cost, SubCode and Details are the same.

If you can describe a specific case where one subscription COULD have different Currencies for different line items under a single expense item, we might have to dig further into the actual business rules, though. However, at this point, it's hard to see how that could happen.

Agreed the currency field should be in the tblExpenses as all lineitems are in the same currency, I'm too far down this path already to change that now, plus it does give the flexibility for different currencies in the same invoice, this could potentially happen with a conversion to a 'Home Currency'. Same does not apply for Cost, SubCode, and Details, these are different for each lineitem

 

[GPGeorge]

Agreed the currency field should be in the tblExpenses as all lineitems are in the same currency, I'm too far down this path already to change that now, plus it does give the flexibility for different currencies in the same invoice, this could potentially happen with a conversion to a 'Home Currency'. Same does not apply for Cost, SubCode, and Details, these are different for each lineitem

If the Cost, SubCode and Details are different for each line item, then they can't be determined from a Subcription, which has only ONE Cost and SubCode. I think we really need to start at the beginning and sort out all of the entities and relationships.

 

[mounty76]

For the subscription their will only be one lineitem per expense. It is for other expenses where there maybe more than one lineitem.

Thanks for your help but this seems impossible so will stick with entering them manually each month, was only a nice to have

 

[The_Doc_Man]

I'm too far down this path already to change that now

...

this seems impossible

If you can't find time to fix it now, how will you EVER find time to fix it later? This is one of those "pay me now, pay me later" situations. It is always your choice but I just want you to recognize that you are condemning yourself for future frustration.