Search options (Page 12 of 16)

I didn't make any changes on income statement.
The populating of data occur on the onClick method of No_Sales_2 Button.
It's like Insert into qtable with the (Select query I copied on No_Sales Query)

qtable columns is the same count and sequence on its corresponding data in the select query for it to execute properly.

Simplest way that I can think of is to have another table and populate it of the records whenever you are going to access it. Filtering will be easy in that manner.

Here. I made some changes too. I point again the table from Sales_Ticket Form to Sales_Ticket table. It must not be pointing to sales. I added a detailed tablegrid for the sales table. Sales_Ticket table must be pointing to Sales_Ticket for the edit button to function. I also deleted Total Field on Sales_Ticket, you won't be needing it for you already have sTotal field

You can just copy the whole text from the calculated sTotal and paste it to where you want to use it. But you need to consider the relationship and to where you are trying to access or include it. In the case you want, as I've said, you don't need and it is not possible to add sTotal to No_Sales query because of the structure of your database. You have to look for other example and scenario for practicing.

Sorry, still not getting what field you want to add and to where.
Are you referring to the query on No_Sales form? It's working fine for me. You don't have to add or include Sales_Ticket there for it is already getting the total sales grouped by date from sales table and not by ticket.

Yes you can. Can you elaborate what field/s you want to add and to where?

It's something like

( tbl1.field1 + (SELECT somefieldon_tbl2 FROM tbl2 WHERE tbl2.id_tbl1=tbl1.id) )

or something like

( tbl1.field1 + (SELECT TOTAL(somefieldon_tbl2) FROM tbl2 WHERE tbl2.id_tbl1=tbl1.id) )

It'll depend on what data you are trying to get or obtain.

You can't do that.

It is a syntax of SQL. It's like naming sales to a then sales to b
a and b both point to Sales table but the query consider it like a different tables.
The query basically get the distinct record of a by date, then subquery the number of record per category from b grouped by sales date from a

I can't comprehend what you really want to do but it is attainable in my perception. We just need a little bit more details on your table and to where do you execute that queries. As derek said, it is better for you to upload at least part of your program if you can't upload all of it. Just have copy of the form and tables to where you want to execute that queries in a new project.

I think the best way is to convert it into sqlite. Then you can use all MVD's feature. search, add, edit, delete, etc.
You can use 3rd party software to migrate your data. In my case, I use Navicat. It's a paid software but you can use the 14-day free fully functional trial. You can directly export data from .accdb to sqlite from there. If you are working with .mdb, you can export it to excel, then from excel, you can export it to sqlite using Navicat. There's a lot of 3rd party database viewer. DB Browser is a free tool, I think you can export csv data using it.

I don't have time to check on the details but for the Report SQL, all you have to do to avoid that ambiguous error is to explicitly select each Select_Eye with their corresponding Table. Here's a sample to join lu_004_Select_Eye named as CE and then explicitly select the two Select_Eye Fields that is in same table lu_004_Select_Eye but in different name(CE and E) and joined to separate table clinic and complaints. Hope this gives you an idea

SELECT
V.id, OPD_ID, Date,
First_Name,Last_Name, PID ,

doctor, complaints ,diagnosis , E.Select_Eye as SE_Diagnosis, CE.Select_Eye as SE_Complaints 

FROM                                               
aa_002_opd_visits V                                           
                                    
INNER JOIN    
aa_001_patients P  ON  V.id_aa_001_patients = P.id

INNER JOIN
lu_001_doctors D  ON  V.id_lu_001_doctors = D.id        

INNER JOIN                                                 
aa_004_clinics C ON  C.id_aa_002_opd_visits = V.id

INNER JOIN               
lu_003_diagnosis G ON G.id = C.id_lu_003_diagnosis 

INNER JOIN
lu_004_Select_Eye E ON E.id = C.id_lu_004_Select_Eye

INNER JOIN
aa_003_complaints  M ON   M.id_aa_002_opd_visits = V.id  

INNER JOIN           
lu_004_Select_Eye CE ON CE.id=M.id_lu_004_Select_Eye

INNER JOIN      
lu_002_complaints  LM ON LM.id = M.id_lu_002_complaints 


 where
 V.id = $id

Just declare it globally