How to Insert Multiple or single data into Another Table

[Moore71]

Hi, I am just trying out something here. Although I have gone thru various post concerning this question, but I haven't seen the exact one that fits into my criteria.


I want RecordSet that Insert The Following Fields from a query (MyQuery) into a new table (MyTable)
MyQuery feilds are TransID, CriteriaID, Date, Description, Qty, Amout (CriteriaID = Forms!MyForm!CriteriaID)
and
MyTable feilds are TransNo, CriteriaID, Date, Desc, Qty, Amount


Thanks and compliments
Moore

 

[isladogs]

If you must duplicate data in a second table, you can just use an append query to do this

 

[June7]

Why do you need to duplicate data to another table?

Are you saying MyTable is not yet created? Use SELECT INTO query.

If MyTable already built and you just want to add new records, use INSERT SELECT.

INSERT INTO MyTable(TransNo, CriteriaID, [Date], [Desc], Qty, Amount) SELECT TransNo, CriteriaID, [Date], [Desc], Qty, Amount FROM MyQuery WHERE CriteriaID = Forms!MyForm!CriteriaID;

Date and Desc are reserved words and should not use reserved words as names for anything.

 

[Moore71]

Why do you need to duplicate data to another table?

Are you saying MyTable is not yet created? Use SELECT INTO query.

If MyTable already built and you just want to add new records, use INSERT SELECT.

INSERT INTO MyTable(TransNo, CriteriaID, [Date], [Desc], Qty, Amount) SELECT TransNo, CriteriaID, [Date], [Desc], Qty, Amount FROM MyQuery WHERE CriteriaID = Forms!MyForm!CriteriaID;

Date and Desc are reserved words and should not use reserved words as names for anything.

I am actually fetching the data from 3 tables, that's why I am using MyQuery as source and I want to insert into one almighty table for achieving and other reasons

 

[isladogs]

From your description that still seems totally unnecessary.
As you can get all the fields you need from your query, you can use that query for 'achieving and other reasons'

 

[June7]

Did you mean 'archiving'?

Why do you need to archive? This is seldom necessary.

 

[Moore71]

I am thinking of archiving because my client is complaining of too slow operation even after I index all the tables in the application.


I am also thinking of inserting into new table because data will be pulled from 3 tables to update and record keeping at various times not at the same time and I want to be calculating Available QTY on the fly based on the new table when needed

 

[The_Doc_Man]

On an indexed table, unless we are talking about the potential of returning a million rows, slow operation isn't caused by failure to archive. It COULD be caused by failure to filter properly. But usually speed questions come down to configuration and complexity of the query. If you have data from three tables, you have a 3-way JOIN so the question will be, what KIND of JOIN are we discussing? Are they just lookup-JOINs or are we talking parent-child JOINs with multiple child records in each case? Do you have a Domain Aggregate function anywhere in the query?

What is your configuration? Monolithic or split? If split, what is the network like? How "deep" in the folder structure is the BE file?

What you are doing for speed purposes makes sense in one way - but remember that when you update ANY of the 3 tables that were your data sources, you just doubled your work because you now have to update that combined table, too. Otherwise, this denormalized structure is going to quickly diverge from reality.

Worse, depending on how it is structured, the denormalized table might actually be BIGGER than the source tables if there are any one-to-many relationships from the main table to either subsidiary. It is possible that doing this will make your queries SLOWER.

You would do better to find out why your queries are slow before attacking this method of conjoining the tables.

 

[Deleted member 147267]

my client is complaining of too slow operation even after I index all the tables in the application.

That may be part of the problem. A read up on indexing might be advisable, but slow performance is like a spider web of possible causes, depending on when the slow performance is experienced.

Here's one for a start

https://support.office.com/en-us/ar...formance-0a8e2aa6-735c-4c3a-9dda-38c6c4f1a0ce