Query and Table question

[Marinus]

Hi All,

Before starting to mess up things, I would like to ask some advise, I have to store data in 2 different tables. Table one has the following structure;
ID|MATID|FULLWEIGHT|EMPTYWEIGHT|BUYPRICE

Table 2 has structure;
ID|MATID|WEIGHT|HOWMANY|BUYPRICE

The quer(y/ies) should should give the following result;

Table 1
(FULLWEIGHT - EMPTYWEIGHT) = NETTO * BUYPRICE = WEIGHTAMOUNT

Table 2
HOWMANY * BUYPRICE = HOWMANYAMOUNT

One query should create a receipt on both on amounts and calculate total
Second query should extract both weights to a stock report.

At this moment I already have Table 1 fully functional and was wondering how best to combine both, put additional fields in Table 1 or create Table 2 and try to combine them somewhere.

To further explain my lack of knowledge; 80% of transactions are based on Table 1 on Weight, 20% of transaction are items that are bought as items but their weight has to be stored so stock weight can be calculated, or an amount of a certain MATID can be extracted. If not clear, I can explain further..

 

[lman]

Are these tables intended to be a list of actual transactions or simply look up tables used to calculate amounts to be used elsewhere?

Just so everyone can a better understand of what you are needing...thanks

 

[Marinus]

Are these tables intended to be a list of actual transactions or simply look up tables used to calculate amounts to be used elsewhere?

Just so everyone can a better understand of what you are needing...thanks

Thanks Iman, they contain the actual transactions Table1 contains waste bought by weight, Table2 by item, however they can be sold by item or by weight..

Hope this explains..

 

[lman]

I'm still not clear about what you are asking,

If these are both transaction tables then I would suggest re-designing your tables because you shouldn't need 2 seperate tables just because the way the price is calculated is different. From what i gather the price is either calculate by weight or by qty.

You would probably still have 2 tables but table1 would be a transaction header table that contains the basic transaction data such as transaction_ID, date/time, customer, etc)

Table2 would be the transaction detail table that contains the details of each transaction. This would contain transaction_ID (which matches the transaction_id in table1), matid, fullweight, empyweight, netweight, qty and buyprice. You would plug data in these fields as needed depending on how stuff is being bought.

Note transacation_id would be a primary key in table1 but not in table2 as you could or would have multiple matid's for each transaction


Hope this helps and let me know if i am not understanding you correctly...