Database

Mycroft Holmes4-Apr-19 20:38

Mycroft Holmes

4-Apr-19 20:38

From the Lounge response

Quote:
but it says "the field PLU could refer to more than one

Indicates that you need to prefix the PLU field (TableName.PLU) with a table name so the query knows which table to use.

Never underestimate the power of human stupidity -
RAH
I'm old. I know stuff - JSOP

BrunoPigeon4-Apr-19 20:56

4-Apr-19 20:56

Thanks Mycroft. I am using this one - trying to get it to work:

Select distinct format(SupplierInvoices.Date, 'dd-MMM-yyyy') as tDate, Sum(SupplierInvoices.QTY) as lTotal from SupplierInvoices where PLU = '" & lblPLU.Caption & "' and Date Between #" & StartTime & "# and #" & EndTime & "# Group by format(SupplierInvoices.Date, 'dd-MMM-yyyy') Union Select distinct format(Sales.Date, 'dd-MMM-yyyy') as tDate, Sum(Sales.QTY) as sTotal from Sales where PLU = '" & lblPLU.Caption & "' and Date Between #" & StartTime & "# and #" & EndTime & "# Group by format(Sales.Date, 'dd-MMM-yyyy')

Error is: Item sTotal cannot be found.

I appreciate any feedback (even if it's about vb6/access!)

GuyThiebaut4-Apr-19 21:06

4-Apr-19 21:06

Read up on what the UNION command does.

I could tell you what is not working here but you will find out much more if you read, understand than methodically practise what you have read and understood rather than throwing things together hoping they will work then asking for help when they don't work.

What the heck, I will tell you anyway - either change ITotal to sTotal or change sTotal to ITotal.
The column names for the unioned results need to be the same.

As an aside - I don't think that query is going to give you any information that is of any use as you are basically listing sales totals and dates together with invoice totals and dates with no ability to distinguish between what is a sales row and what is an invoice row.

I think you need to step away from the computer, read a book, follow tutorials and work out what you want to do on paper before typing SQL queries that you hope will somehow work.

“That which can be asserted without evidence, can be dismissed without evidence.”

― Christopher Hitchens

BrunoPigeon4-Apr-19 21:10

4-Apr-19 21:10

You are right, Guy.

At this stage I don't fully understand SQL and am attempting queries out of my depth of understanding!

Thank you for the encouragement.

GuyThiebaut4-Apr-19 21:20

4-Apr-19 21:20

Take it slowly and learn from the beginning - it's the classic thing of if you bite off more than you can at first chew you will be put off by it.

If you are diligent, within a couple of weeks you will be able to solve these sorts of issues without too much help.

Good luck!

“That which can be asserted without evidence, can be dismissed without evidence.”

― Christopher Hitchens

GuyThiebaut4-Apr-19 21:43

4-Apr-19 21:43

Maybe try something like this:
SQL in 24 Hours, Sams Teach Yourself: Amazon.co.uk: Ryan Stephens, Arie D. Jones, Ron Plew: 9780672337598: Books[^]

The books can be a bit expensive but I have generally found that the series is a good introduction to any topic and you can usually get through all the exercises in a week.

“That which can be asserted without evidence, can be dismissed without evidence.”

― Christopher Hitchens

GuyThiebaut4-Apr-19 20:56

4-Apr-19 20:56

I am curious - is this a university or school project question?

I ask because the SQL you have is just not going to work - it's basically a word salad of SQL, a jumble of keywords in the wrong order and missing information.

I would suggest you step back and start learning the basics of SQL.

Also it will not be possible to anyone to help you with the information you have provided as you have not told us which columns are the primary and foreign keys within Sales and SupplierInvoices.

“That which can be asserted without evidence, can be dismissed without evidence.”

― Christopher Hitchens

BrunoPigeon4-Apr-19 20:58

4-Apr-19 20:58

I appreciate the feedback.

This is what is giving me headaches:

Select distinct format(SupplierInvoices.Date, 'dd-MMM-yyyy') as tDate, Sum(SupplierInvoices.QTY) as lTotal from SupplierInvoices where PLU = '" & lblPLU.Caption & "' and Date Between #" & StartTime & "# and #" & EndTime & "# Group by format(SupplierInvoices.Date, 'dd-MMM-yyyy') Union Select distinct format(Sales.Date, 'dd-MMM-yyyy') as tDate, Sum(Sales.QTY) as sTotal from Sales where PLU = '" & lblPLU.Caption & "' and Date Between #" & StartTime & "# and #" & EndTime & "# Group by format(Sales.Date, 'dd-MMM-yyyy')

It says Item sTotal cannot be found.

I am new to SQL and am trying to learn, hence asking on this forum.

Mycroft Holmes4-Apr-19 21:16

Mycroft Holmes

4-Apr-19 21:16

BrunoPigeon wrote:
I am new to SQL and am trying to learn, hence asking on this forum.

Ok there is something REALLY wrong with this. You are trying to learn SQL using the absolutely WRONG tools. As I said VB6 is dead, you will not get anything but bad memories from old farts who used to use it.

Access is not a suitable learning platform for SQL it has some weird stuff unique to Access. Port your database to SQL Server (there is a clear migration path) and use the proper tools to learn TSQL.

You are doing yourself a disservice continuing to use these tools as you will need to UNLEARN a lot of stuff. Besides there are a huge number of resources to help you with the current tool sets.

Never underestimate the power of human stupidity -
RAH
I'm old. I know stuff - JSOP

GuyThiebaut4-Apr-19 21:24

4-Apr-19 21:24

Excellent points.
I learnt SQL using Access and basically I learnt nothing of use.

Then when I picked up SQL Server and did the training courses, the world of SQL open up to me with its resplendent unicorn rainbows and... okay I am exaggerating a bit but I think you make some very good points in your comment.

“That which can be asserted without evidence, can be dismissed without evidence.”

― Christopher Hitchens

BrunoPigeon5-Apr-19 0:13

5-Apr-19 0:13

I know MyCroft! I am busy porting it over to MySQL but , GEEEEZ, alot must be changed in the code!

But yes, I agree vb6 is dead. It's just that I have an old program I still support. So hanging in there on the last threads of vb6!!

Thank you for your input - I really appreciate it.

Bruno

GuyThiebaut4-Apr-19 21:17

4-Apr-19 21:17

Have a look at my other comment where I explain what is going on.
I'd also suggest that you get into the habit of formatting your SQL so that is is easier to read.
Here's an example of a possible way of formatting your SQL:

Select 
    distinct format(SupplierInvoices.Date, 'dd-MMM-yyyy') as tDate, 
    Sum(SupplierInvoices.QTY) as lTotal 
from SupplierInvoices 
where PLU = '" & lblPLU.Caption & "' 
    and Date Between #" & StartTime & "# and #" & EndTime & "# 
Group by format(SupplierInvoices.Date, 'dd-MMM-yyyy') 
Union 
Select 
    distinct format(Sales.Date, 'dd-MMM-yyyy') as tDate, 
    Sum(Sales.QTY) as sTotal 
from Sales 
where PLU = '" & lblPLU.Caption & "' 
    and Date Between #" & StartTime & "# and #" & EndTime & "# 
Group by format(Sales.Date, 'dd-MMM-yyyy')

“That which can be asserted without evidence, can be dismissed without evidence.”

― Christopher Hitchens

BrunoPigeon5-Apr-19 0:11

5-Apr-19 0:11

I really appreciate all the things you said, Guy. Thanks a ton. I will dive into this this weekend and hopefully by Sunday, have a solution to my own question!

Take care & thanks for your valuable input.

Bruno

GuyThiebaut5-Apr-19 2:48

5-Apr-19 2:48

You're welcome.
You will get there - good on you for bearing with things!

“That which can be asserted without evidence, can be dismissed without evidence.”

― Christopher Hitchens

BrunoPigeon5-Apr-19 4:22

In Access: group of records and assign conditional group ?

5-Apr-19 4:22

Cheers, brother! I will. You will be the first to know.

Member 245846726-Mar-19 22:18

Member 2458467

26-Mar-19 22:18

In Access I want to group and assign data according to conditions with 2 values 0 and 1 (true or false) and with the condition NGAYGIAO>= #01/01/2019# and NGAYGIAO <=# 31/01/2019# me How to write commands ? The data I put in the excel file consists of two sheets: Original data and Result, where Original data is the original data sheet that is the result of the problem I need, when running the access issue, ask you to help me with the problem. http://www.mediafire.com/file/hkkw519bw2k3o9h/baitap_data2.xls/file

Re: In Access: group of records and assign conditional group ?

Victor Nijegorodov27-Mar-19 11:50

Victor Nijegorodov

27-Mar-19 11:50

Your problem description is not very clear, so it's hard to guess what you do and what you need...
However, you could rewrite your condition for NGAYGIAO as

NGAYGIAO BETWEEN #01/01/2019# AND #31/01/2019#

which looks more readable.

Re: In Access: group of records and assign conditional group ?

Member 24584677-Apr-19 16:20

Member 2458467

7-Apr-19 16:20

for (int i = 0; i < [Original data].Count; i++)
            { 
                bool BOOLEAN = 0;
                string SOHD = "";
                if([NGAYGIAO].[i]>=#1/1/2019# && [NGAYGIAO].[i] <=#1/31/2019#) //dd/MM/yyyy
                {
                   for (int j = 0; j < [Original data].Count; j++)
                   {
                       if([LOAIHD].[j] == "FBAN") 
                        {
                            BOOLEAN = 1;
                            SOHD = [Original data].[HD].[j];
                        }<br />
                   }
                }
                if(SOHD == [Original data].[HD].[i] && SOHD != "")
                {
                    [Result].[BOOL].[i] = BOOLEAN;
                }
            }

Re: In Access: group of records and assign conditional group ?

Member 24584677-Apr-19 16:24

Member 2458467

7-Apr-19 16:24

from the original data I created the result, if the contract in the month has LOAIHD = "FBAN" and between (#01/01/2019# and #31/01/2019#) then [BOOL] = 1 else [BOOL = 0, I will describe the condition of [BOOL] in C# code

[CODE]
for (int i = 0; i < [Original data].Count; i++)
{
bool BOOLEAN = 0;
string SOHD = "";
if([NGAYGIAO].[i]>=#1/1/2019# && [NGAYGIAO].[i] <=#1/31/2019#) //dd/MM/yyyy
{
for (int j = 0; j < [Original data].Count; j++)
{
if([LOAIHD].[j] == "FBAN")
{
BOOLEAN = 1;
SOHD = [Original data].[HD].[j];
}

}
}
if(SOHD == [Original data].[HD].[i] && SOHD != "")
{
[Result].[BOOL].[i] = BOOLEAN;
}
}
[/CODE]

Re: In Access: group of records and assign conditional group ?

Chris Quinn28-Mar-19 1:55

Chris Quinn

28-Mar-19 1:55

Add a derived column to your selection where the definition is as follows:

=IIF(NGAYGIAO BETWEEN #01/01/2019# and #31/01/2019 23:59:59#,true,false)

You can then sort and group by this column

=========================================================
I'm an optoholic - my glass is always half full of vodka.
=========================================================

SQL Remote Connections

Kevin Marois18-Mar-19 8:00

Kevin Marois

18-Mar-19 8:00

I had SQL Remote Connections working on a clien't server. For some reason I can no longer connect.

I'm getting

A network-related or instance-specific error occurred while establishing a connection to SQL Server. The server was not found or was not accessible. Verify that the instance name is correct and that SQL Server is configured to allow remote connections. (provider: Named Pipes Provider, error: 40 - Could not open a connection to SQL Server) (Microsoft SQL Server, Error: 53)

The network path was not found

I can remote into their server, I just can't connect to the DB either with SSMS or through my app. The ports are open, and SQL is configured for remote access.

I've tried all the usual debugging techniques, like this, but I can't get in.

Anyone have any other ideas?

If it's not broken, fix it until it is.
Everything makes sense in someone's mind.
Ya can't fix stupid.

Richard Deeming18-Mar-19 9:35

Richard Deeming

18-Mar-19 9:35

Kevin Marois wrote:
provider: Named Pipes Provider

If you're using named pipes, you'll need to open port 445:
Direct hosting of SMB over TCP/IP[^]

Alternatively, configure the server and client to use TCP/IP:
Configuring Client Network Protocols | Microsoft Docs[^]
SQL Server Configuration Manager - SQL Server | Microsoft Docs[^]

"These people looked deep within my soul and assigned me a number based on the order in which I joined."
- Homer

Kevin Marois18-Mar-19 10:02

Kevin Marois

18-Mar-19 10:02

Named Pipes is disabled in SQL Config Mgr.

I followed this to set up Remote Access.

I also opened ports 1433 and 1434 in Windows Firewall.

Again, no joy

If it's not broken, fix it until it is.
Everything makes sense in someone's mind.
Ya can't fix stupid.

Richard Deeming19-Mar-19 1:57

Richard Deeming

19-Mar-19 1:57

But your error message says you're using named pipes.

Have you checked the client protocol configuration, as well as the server config?

"These people looked deep within my soul and assigned me a number based on the order in which I joined."
- Homer