Append Query

[GagaZ]

Hi,
Is there any way to append form that contains subform to another form with subform.
See attachment.
I made sample database:

table Inv with fields
InvoiceId - autonumber
InvoiceDate - date

table Quo with fields
InvoiceId - autonumber
InvoiceDate - date

table Pro
ProductId - autonumber
ProductName - text

table InvPro
InvoiceId - number
ProductId - number

table QuoPro
InvoiceId - number
ProductId - number

Tables Inv and InvPro and Pro are in 1 to many relation ship
Tables Quo and QuoPro and Pro are in 1 to many relation ship

I created following Query InvProd
using tables InvPro and Pro
that contains InvoiceId ProductID and ProductName fields

Query QuoProd
using tables QuoPro and Pro
that contains InvoiceId ProductID and ProductName fields

I made forms:
Inv : InvoiceId, InvoiceDate
with subforms (query InvProd)

Quo : InvoiceId, InvoiceDate
with subforms (query QuoProd)
I populated booth forms and have in form Inv - invoice nr 1 and 3 products, form Quo invoice nr 1 and nr 2 booth with 5 and 6 product items.
What I want is to import from the form Quo invoice nr 2 to form Inv so that I receive in form Inv invoice nr 2 with 6 product items.
Meaning of is :
As i do quotation for some company, some of them get approved and some not, approved one will be realized so they have to go to Inv form, the other that they are not approved stay in quotation and they don't enter my bookkeeping side of program - they are not realized sales.

This is my Sql:
INSERT INTO InvPro ( InvoiceId, ProductId, ProductName )
SELECT [QuoProd].[InvoiceId], [QuoProd].[ProductId], [QuoProd].[ProductName]
FROM QuoProd
WHERE [QuoProd].[InvoiceId]=2;

I will make select query with criteria [EnterInvoiceId] that will ask you which invoice from Quotation will be append.
Tks,
GagaZ

 

[boblarson]

Frankly, why can't you just use one set of tables to hold both the quote and invoice data? All you would need is a flag (Yes/No) to "convert" the quote to an invoice. Your current setup is non-normalized and really isn't efficient.

I know that there have been discussions about that on the forum here.

 

[GagaZ]

Hi Bob,
Thank for reply.
If I'm understanding good : example I should make table Orders with fields Invoice (Yes/No) and Quotation (Yes/No). So If quotation is getting realized as invoice just to change thick box from Quotation (No) to Invoice (Yes). Is this the way how would you convert quotation to invoice?
Tks,
GagaZ

 

[boblarson]

You don't need both fields. Just one. Just one YES/NO field for QuoteConversion. If converted, then Yes, if not (just quote) No.

 

[GagaZ]

Hi Bob,
Thanks, will try to talk to the ppl that I work with. I know there will be disagreement as for bookkeeping: there will be Invoice Nr 1,2, 8,9,15..... the missing ones will be quotations.
Anyway Thanks very much.
GagaZ

 

[boblarson]

Remember, there are ways to make things work without going outside of, or too far outside of, normalization rules. You want a system that is normalized as it will help with data INTEGRITY and it will also allow you to get at data more readily with reporting. So, feel free to post as many questions to the forum as you need to get a robust design going. Post to the Theory and Practice Database Design forum category and I'm sure you'll get a lot of assistance with this.

Remember too, you are the one who needs to be able to tell your users that something that they want is not doable if it will make the database garbage. You have to strike a balance between what they want and what they need. You can give them what they need, but what they want is just that - wants - that must not get in the way of providing what they need. You'll have to be a diplomat there, but if you do it right, you can give them what they want as well as what they need, but not necessarily in the way they first envisioned it. As the developer, you need to be the expert that they can depend on for getting those things accomplished.

 

[GagaZ]

Hi,
Thanks for your advice, we have compromised for Quotations to be Yes/No as you have suggested. Now, in your first answer to my thread you said that my current setup is not normalized and not efficient, I'm refering to the test database in previous attachment.Having tables Inv and Pro with PK autonumber and table InvPro just with number no PK, that allows me to enter same product items multiple times on same invoice. Xmpl: Invoice nr.12 : ---- Item : 3 Milk, 1 Eggs, 6 Oil, 3Milk.
Reason for that is that company is having private orders, sometimes 20 people order similar items and instead of creating 20 invoices they put them all on one invoice, with result that one invoice will have example 15 x milk order with different quantities. If I normalize my database and make in InvPro table PK numbers than I'm unable to enter same product multiple times. Is there any solution for.
2. I would like to have product numbers numbered with autonumber so that on report
I would have 1,2,3,4..... numbering for product items I have entered in Invoice. Now I have: 3,1,6,3 numbers from items. Can I enter in report in subform autonumber box for product items entered or I have to create separate table that will contain subform products.
Tks GagaZ