Topic: Need Help

Hi Friends,
            How are you? I am attaching a project in which I want to display no of items to be sold.On form1, there is button caption " No of Sales" I tried my best but failed to get desired result. I want to display No of sold items on each date. For example if three pens sold on a date, it must display three. Can any one help?

Post's attachments

Attachment icon Income Expenses New.zip 342.52 kb, 25 downloads since 2020-10-07 

2 (edited by ehwagner 2020-10-08 02:18:51)

Re: Need Help

See if the attached works for you.

Post's attachments

Attachment icon Income Expenses New3.zip 355 kb, 43 downloads since 2020-10-08 

Re: Need Help

Thank you ehwagner. It is really good solution which I wanted but it created confusion. Confusion is from where a.sdate, b.sdate, b.id_Category , sales a, sales b fields come? No in any table?

Re: Need Help

unforgettable wrote:

Thank you ehwagner. It is really good solution which I wanted but it created confusion. Confusion is from where a.sdate, b.sdate, b.id_Category , sales a, sales b fields come? No in any table?

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

brian

Re: Need Help

Thank you brain. Would you like to mention websites from where I get more detail.
My next issue is how to get print?

Re: Need Help

unforgettable,
See attached for print.


There are a bunch of sites for SQL. Here is one of many for learning: https://www.w3schools.com/sql/default.asp.

Post's attachments

Attachment icon Income Expenses Print.zip 356.69 kb, 34 downloads since 2020-10-08 

Re: Need Help

Thank you endanger. I got much more ideas. I can add price column same way?. How to get sum/total on footer of tablegrid?

Re: Need Help

unforgettable,
See attached for price/sum in tablegrid and report.

Post's attachments

Attachment icon Income Expenses Price.zip 357.06 kb, 28 downloads since 2020-10-12 

Re: Need Help

Thank you endanger to guide. You gave much more information. My another issue is how can we add a field from another table? Or we can add field from one table? Can you guide?

10 (edited by brian.zaballa 2020-10-12 12:34:56)

Re: Need Help

unforgettable wrote:

Thank you endanger to guide. You gave much more information. My another issue is how can we add a field from another table? Or we can add field from one table? Can you guide?

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.

brian

Re: Need Help

Hi brain
          Look attached project by endanger. From Sales_Ticket table, I want to add sTotal field in Sal quarry on farm. No_Sales quarry button. How it will be?

Re: Need Help

unforgettable wrote:

Hi brain
          Look attached project by endanger. From Sales_Ticket table, I want to add sTotal field in Sal quarry on farm. No_Sales quarry button. How it will be?

Sales_ticket already had sTotal calculated field in it. I just update the TableGrid on Sales_Ticket Form for it is pointing to sales table. It has to be on Sales_Ticket for it to display correctly.

Post's attachments

Attachment icon Income_Expenses_Fix.zip 515.07 kb, 24 downloads since 2020-10-13 

brian

Re: Need Help

Hi brain,
            Infact I want to know how to add field  from other table with same condition. Just like I typed before

Re: Need Help

unforgettable wrote:

Hi brain,
            Infact I want to know how to add field  from other table with same condition. Just like I typed before

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.

brian

Re: Need Help

Here. I added total count on No_Sales Form query. Maybe it'll give you an idea

Post's attachments

Attachment icon Income_Expenses_Fix (2).zip 515.09 kb, 26 downloads since 2020-10-13 

brian

Re: Need Help

Thank you brain to reply. I am really sorry you still could not understand what I am asking. I want to know how to add field from other table to quarry? For example sTotal from Sales_Ticket to No_Sales quarry. How it will be done. This is for practice. I know this project is working very well.

Re: Need Help

unforgettable wrote:

Thank you brain to reply. I am really sorry you still could not understand what I am asking. I want to know how to add field from other table to quarry? For example sTotal from Sales_Ticket to No_Sales quarry. How it will be done. This is for practice. I know this project is working very well.

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.

brian

Re: Need Help

Thank you brain. Would you like to tell which kind of changes be made in project structure? My other question is: it is possible select clause from one table and conditional from amother table? What will be syntax and how it will work

Re: Need Help

unforgettable wrote:

Thank you brain. Would you like to tell which kind of changes be made in project structure? My other question is: it is possible select clause from one table and conditional from amother table? What will be syntax and how it will work

There's no need for a change in project structure. It's just that we need the desired output you want and someone will help you with it. I don't have a case to give as a sample. You can state a problem to which that conditional from other table is needed, then we'll solve it and create a query with it.

brian

Re: Need Help

Hi friends

l Look at attached project. On form Sales I added three components edit1,edit2 and edit3. For them I created three fields Total, Discount and Receivable on table Sales_Tickets. I want to add these field to querry on form No_sales. How it will be?

Post's attachments

Attachment icon Income Expenses New.rar 308.34 kb, 21 downloads since 2020-10-14 

Re: Need Help

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

Post's attachments

Attachment icon Income_Expenses_New_2.zip 515.89 kb, 22 downloads since 2020-10-14 

brian

Re: Need Help

Thank you brain.This project is going to be interesting. I want to know to how a checkbox can enter in querry conditional part and how can we display record for specific period

Re: Need Help

unforgettable wrote:

Thank you brain.This project is going to be interesting. I want to know to how a checkbox can enter in querry conditional part and how can we display record for specific period

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.

brian

Re: Need Help

Here. I made a sample. just add whatever filter you want there.

Post's attachments

Attachment icon Income_Expenses_New_3.zip 517.88 kb, 43 downloads since 2020-10-15 

brian

Re: Need Help

Thank you again brain. How did you populate table? You did not make any change on income statement. I still could not understand about a and b.