please please help .2nd post !!

[Guest]

Hi All,

I had link my accounting system database into my new projetc. Base on the
accounting system. I had table1 ( invoice#, invoice date, item#, sale price,
qty, sales rep.), table2 ( item#, cost ). I made an append query using these
2 tables to a table call "FINAL" , here are what I want to do :

1. Let say if i change the query to filter by sales rep. - how can I create
a column in the query to add up all the cost and a column to add all the sale
price

2. How can I make the append query only append un-added record (compare to
table "FINAL" )

P.S. in table 1, if the invoice had 3 items, the table will show line for
each item, I can not find an unique column for that.

Thank you

 

[John Vinson]

Hi All,

I had link my accounting system database into my new projetc. Base on the
accounting system. I had table1 ( invoice#, invoice date, item#, sale price,
qty, sales rep.), table2 ( item#, cost ). I made an append query using these
2 tables to a table call "FINAL" , here are what I want to do :

It's neither necessary nor desirable to store your data redundantly in
this table FINAL. You can create a Report based on a Query joining the
tables, and use the Sorting and Grouping dialog of the Report to group
the records as you wish, and calculate subtotals and totals
appropriately. It is not necessary to have the data all in one table
to create a report!

1. Let say if i change the query to filter by sales rep. - how can I create
a column in the query to add up all the cost and a column to add all the sale
price

This information SHOULD NOT EXIST in a table. It should be calculated
on the fly, either in a Totals query or in the Footer of a form or a
report.

2. How can I make the append query only append un-added record (compare to
table "FINAL" )

Without knowing anything about the structure of your tables, it's hard
to say. A unique index on a field or combination of fields that
identify an "added" record would work, but since I don't know what
those fields might be...

P.S. in table 1, if the invoice had 3 items, the table will show line for
each item, I can not find an unique column for that.

I do not understand this at all. It sounds like you're perhaps
confusing data *STORAGE* with data *DISPLAY*.

John W. Vinson[MVP]
Join the online Access Chats
Tuesday 11am EDT - Thursday 3:30pm EDT
http://community.compuserve.com/msdevapps

 

[Guest]

Hi John,

thanks for your quick reply. Basically I'm trying to take some data from my
accounting system and form a new table or report to calculate the profit for
each invoice as well as each items. Since the price inside the acocunting
system is changing all the time, I had specific a time of each day to run
this new program to capture the cost of an invoice ( from yesterday ) and
append it to a form or table. Then by the end of the month, I can just print
out the report that in the append table with a day range

The problem for question 2 is, the only way to unique that is combination of
invoice # and Item number

 

[John Vinson]

Hi John,

thanks for your quick reply. Basically I'm trying to take some data from my
accounting system and form a new table or report to calculate the profit for
each invoice as well as each items. Since the price inside the acocunting
system is changing all the time, I had specific a time of each day to run
this new program to capture the cost of an invoice ( from yesterday ) and
append it to a form or table. Then by the end of the month, I can just print
out the report that in the append table with a day range

The problem for question 2 is, the only way to unique that is combination of
invoice # and Item number

Put a unique two-field Index on those two fields, then.

If the data in the accounting program is changing all the time, how
can you have any confidence that the snapshot that you're taking is
complete and accurate!?

John W. Vinson[MVP]
Join the online Access Chats
Tuesday 11am EDT - Thursday 3:30pm EDT
http://community.compuserve.com/msdevapps

 

[Guest]

Thanks again. I had try to put in a new field to the table for those records
to make it unique. But my biggest problem is , I can not edit or add anything
to the table which come out from my accounting system otherwise the
accounting system will not function properly because I bought that software
as is and its not custom made.

regards on the for sure question, I had talk to the person who will do
update on the price (cost) , we agree that he will not do price update after
a certain time. So , all i have to do is if I finish up the program, is to
run the program everyday before the deadline.

Hope I'm not asking for too much. Thanks for your help