creating a table from a query

[ptquan]

Folks,

Can I make a table from a query which data will be updated once there's change in the original database? If yes, how can I do?

Thanks folks,
Quan

 

[Pat Hartman]

I'm not clear on what you need to do. A Make Table query will create a new table from a query. Each time you run the Make Table query, it will prompt you to ask if you want to replace the old table. It doesn't update it. It completely replaces it. To update a table, you would use an Update query.

 

[ptquan]

Thanks for your feedback Pat. Yes, I can make a table as Make Table query and it will be replaced totally once I run the query.
My question is, can I make a table contents the fields in the query and it will be auto-updated once there's a change in the original table (the tables where the query collect information).

Thanks to advise
//Quan

 

[billmeye]

You can use an APPEND query to ADD new records to an existing table and an UPDATE query to make changes to existing records in an existing table.

 

[Pat Hartman]

My question is, can I make a table contents the fields in the query and it will be auto-updated once there's a change in the original table (the tables where the query collect information).

NO. Once you copy the data and put it somewhere else, it is a copy and completely separated from the source. You probably shouldn't be making a new table if you want to have "live" data. Why couldn't you simply use a select query? In Access, queries and tables are interchangeable for most purposes.

 

[ptquan]

Thanks Pat for your feedback. Ack.

In fact, I have 4 tbls: tblJobInfo, tblTaxNumber, tblInvoiceNumber, tbleCharge and its relationship as attached pix.
JobNumber is unique in this database. A same TaxNumber can be appeared in different JobNumber(s). A TaxNumber include different unique InvoiceNumber. The value of each InvoiceNumber will be calculated in tblCharge. It works so far.

Today, I have a request as following:

(1) to list all InvoiceNumber records (and its $ amount) of a TaxNumber: it be done by query;
(2) customer (determine by TaxNumber) will pay for their Invoices in several time with certain $ amount (TaxPayAmt);
(3) Accountant will divide these paid amounts (TaxPayAmt) into different invoices until the invoice fully paid (InvPayAmt); sum(InvPayAmt) at a time would be shown and equal to each TaxPayAmt;
(4) Each TaxPayAmt has an unique TaxPayID
(5) Each InvPayAmt could be linked back to the TaxPayID for the report purpose

How can I create a form which contents:
(a) TaxNumber, TaxPayID, TaxPayDate (input), TaxPayAmt (input)
(b) Subform contents: InvoiceNumber, Total, DebitCredit (query from existing db), InvPayAmt (input), Diff (=Total - sum(InvoicePayAmt)); only the Invoice record with Diff>0 will be displayed.

Thanks to advise, Pat.

//Quan

 

[Pat Hartman]

Why do you have ID columns that are not the primary key fields? They should probably be deleted.

Have you tried to create any of the queries using the query window? It is pretty much a drag and drop operation.

 

[ptquan]

Hi Pat,

These 4 table linked each other so i am using foreign key to link one-to-many. In some table I am using ID as foreign key.

I have tried using query to list all InvoiceNumber which linked to a TaxNumber. The issue is, I can't input data in the form which created from query (not a table). I tried to use in parallel query and table to enter data for InvPayAmt but can't create the relationship between InvPayAmt and TaxPayAmt.


Thanks to assist!
//Quan

 

[Pat Hartman]

Looking at the diagram, I can't tell what the ID columns are supposed to be. Visually, it looks like the wrong fields are defined as the PK. When Access creates "ID" columns it is because you allowed it to create an autonumber PK rather than specifically creating your own but in your case, the "ID" columns are not the PK so I have no clue what they are and that is what leads me to believe the relationships are backwards and that is why you cannot create the queries you need.

Try renaming the columns so that the "ID" fields have meaning. The PK name should identify the table if possible so CustID is the PK for tblCustomer and EmpID is the PK for tblEmployee and the foreign keys should retain the same name as the PK they point to. There are situations where this is not possible but usually it is. So in tblOrder, the PK is OrderID and there is a foreign key CustID which points to CustID in tblCustomer.