Billing DB form

[June7]

Setting relationships not required for db to work but as you discovered can be helpful to ensure data integrity.

If Company_info has only one record for the user's company info, then it does not have a relationship with any other data. It cannot be included in a query with multiple tables. It can be included in a query with only one other table. Put two tables in a query without a join clause and every record of each table will join to every record of other table (a cartesian join). There must be a record in Company_info. Use this query as the report's RecordSource:



SELECT Query1.*, Order_details.*, Products.Description, Products.Prix
FROM (Products RIGHT JOIN Order_details ON Products.Product_ID = Order_details.Product_ID) RIGHT JOIN Query1 ON Order_details.Order_ID = Query1.Order_ID;

[Symlink]

Company_info is for the end user to enter data (per exemple he can enter the slogan which will be displayed at the bottom of the bill, that way he could change it at some point in the future if he wants either advertise or just to say have a good day and thank you).

I took your advice on having a company_info_ID in the order table and set it to 1 by default. So I could make a relationship between those 2 tables using Company_info_ID. Was I wrong ? Because it's showing the data I wanted.

I tried the query you just posted naming it Query1 and trying to put it as a recordsource for a dummy report I created (not to mess with the one I have right now) and it gave me an error " Circular reference caused by "Query1" "

[June7]

Ooops, I edited my post after you read.

Sorry, I posted the wrong SQL statement.

SELECT Query1.*, Order_details.*, Products.Description, Products.Prix FROM (Products RIGHT JOIN Order_details ON Products.Product_ID=Order_details.Product_ID) RIGHT JOIN (SELECT Order.*, Company_info.* FROM [Order], Company_info) AS Query1 ON Order_details.Order_ID=Query1.Order_ID;

But if the default field satisfies, go with that.

[Symlink]

But that would be a design flaw right ? as in not exactly the proper way to do it.

[June7]

For a purist maybe. I don't follow all the 'rules'. I do what works for me. It is a balancing act between normalization and ease of data entry/output. I have tables with lots of empty cells because the structure works best for my primary reporting requirements.

Either way, the Company_info table is in a query with tblOrder. The advantage of the default field is can be done in a query with many tables. The cartesian join can be only two tables.

The alternatives are

1. a subreport bound to Company_info placed in report header

2. company info 'hard coded' on report in labels

[Symlink]

I'll stick to what I have right now or I'll add a subreport like you just suggested either way I think I might be done with the preliminary version of the DB. Thank you very much for you time / patience / and I'd go as far as compassion lol :P

[Symlink]

Latest DB version.

[June7]

Expressions in the taxable and non-taxable textboxes:

=Sum(IIf([Taxable],[Quantity]*[Prix],0))

=Sum(IIf(Not [Taxable],[Quantity]*[Prix],0))

Expression in the tax textboxes (I have forgotten the tax rates):

= 0.05 * Text42

Expression to sum total:

=Nz([txt_sub_total],0) + Nz([Texte42],0) + Nz([txt_tps],0) + Nz([txt_tvq],0)

[Symlink]

Really I am lost. That's just about what I have been trying to do and I do not see where the statement is wrong.

Also I tried uninstalling the french version and installing the english versio with no success I am still getting the syntax error : You omitted an operand or operator, you entered an invalid character or comma, or you entered text without surrounding it in quotation marks.

[June7]

Don't know how to fix this at your end. It works perfect on my computer. I did no other edits in your db (except to resize the subform so the payment mode combobox was not on top of it). I simply entered those expressions in the textboxes you already had there.

Is there another computer you can test this db on?

[Symlink]

Yes I will try on another computer. You entered it in the Control Source of the textbox right ?

[June7]

Correct. Expressions in textboxes ControlSource property.

[Symlink]

It works on my laptop... I am very confused.

[June7]

Sorry, beyond my expertise.

[Symlink]

Anyone has any idea why it would work on my laptop but not on this computer?