|
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
|
|
|
|
|
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
|
|
|
|
|
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
|
|
|
|
|
Cheers, brother! I will. You will be the first to know.
|
|
|
|
|
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
|
|
|
|
|
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.
|
|
|
|
|
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
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#)
{
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;
}
}
|
|
|
|
|
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]
|
|
|
|
|
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.
=========================================================
|
|
|
|
|
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.
|
|
|
|
|
|
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.
|
|
|
|
|
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
|
|
|
|
|
Well, I have checked & rechecked all the settings.. Named Pipes s disabled everywhere that I can see.
Still getting this error
If it's not broken, fix it until it is.
Everything makes sense in someone's mind.
Ya can't fix stupid.
|
|
|
|
|
Presuming you are NOT using named pipes and in fact the server is remote (different box) then the way to test connectivity is as follows.
1. Log in to the client box
2. Use telnet to access the other box
3. If telnet works then you have connectivity. If not then no connectivity.
To use telnet you must have the 'host' (name or ip) and the 'port'. Telnet doesn't need to do anything other than connect. That is the entire test.
If you do NOT have connectivity then one or more of the following is true
1. host is wrong
2. port is wrong
3. server is not up
4. server is not configured for the port you used (but see 1.)
5. Network is blocking traffic. This can include firewalls.
If you do have connectivity then you have an incorrect assumption. For example your test used the wrong host/port or your application is using the wrong host/port.
Kevin Marois wrote: For some reason I can no longer connect.
If it fact this is true. It worked at the install site and no longer does, then it suggests something changed in the install. For example
1. Configuration changed
2. Someone changed the stack. For example installed or changed a firewall.
Don't forget the always wildly popular - the server just simply is not running.
|
|
|
|
|
Hi guys, just wanted to ask if anyone can help me determine whether a table should be:
1. Left in its traditional database form; and if not why? and what should it be normalised to.
2.< The detailed process of implementing the database using SQL, including the normalisation of the table (should that be required), the identification of the attributes, the Entity-Relationship Diagram, and the use of SQL commands in order to create and populate the tables with data
If you're willing to help please reply or message needed urgently. Many thanks
|
|
|
|
|
|
Member 14182925 wrote: what should it be normalised to.
BCNF (also 3.5NF). Steps are described on wikipedia.
Member 14182925 wrote: The detailed process of implementing the database using SQL
We're not going to write a complete custom script and explain it.
Member 14182925 wrote: including the normalisation of the table (should that be required),
Is always required for relational data.
Member 14182925 wrote: and the use of SQL commands in order to create and populate the tables with data
If you don't know them, stay away from the database 
Bastard Programmer from Hell 
If you can't read my code, try converting it here[^]
"If you just follow the bacon Eddy, wherever it leads you, then you won't have to think about politics." -- Some Bell.
|
|
|
|
|
Member 14182925 wrote: If you're willing to help
You are not asking for help, you are asking for someone to do your work for you. If you intend to learn this stuff then you are the one to do the research and find out how to meet the requirement.
Never underestimate the power of human stupidity -
RAH
I'm old. I know stuff - JSOP
|
|
|
|
|
Reeks of homework to me....
Software rusts. Simon Stephenson, ca 1994. So does this signature. me, 2012
|
|
|
|
|
Almost certainly, any shortcuts taken now will mean you or I will be fixing his garbage in the future.
Never underestimate the power of human stupidity -
RAH
I'm old. I know stuff - JSOP
|
|
|
|
|
In Oracle DB, what are the terms USER and SCHEMA?
From what I've read, schema refers to all the content a database has (tables, sequences, views ...) which belongs to a USER.
But what is a USER? Is it like an "internal database"? I mean, let say that I have 2 programs (Planet Help | City industries) that each need 2 separate databases. So, can I create a USER with the name planetDatabase, and another with the name cityDatabase, which would mean I have 2 "internal databases"? And in each "internal database" I can create specific users that have restricted access to certain functions in their respective "internal database"? Or I am way off what a USER actually is?
modified 13-Mar-19 9:06am.
|
|
|
|
|
USER in Oracle is a function that returns the name of the User for the current session - Oracle / PLSQL: USER function[^] - an analogy for that would be the "person" logged on (but a user is not necessarily a person)
Schemas can be User specific - within a database. They are not databases in themselves. Think of them as the "user account".
Well I made that as clear as mud!
The best explanations of the differences that I've found are on this link[^] which also includes a link to this post[^] .
General consensus of opinion seems to be that Oracle made a mess of the terminology!
|
|
|
|
|
So, using my example, if I have 2 programs, and if I need a DB for each of them "Planet Help DB" and "City industries DB" (random names, no connection to real ones), can I create two Database users, using Application Express, one with the name "PlanetHelpDB" and the other "CityIndustriesDB", and use those as a DB for each app? I see that in both of them I can have tables with the same names as in the other one, and also for each I can create new users using "Administration" tools (from Application Express), which will be linked/restricted to a default schema (PlanetHelpDB or CityIndustriesDB, in my example).
Is this a good or a bad way to do it? In case it is bad, what is the recommended way to do it (to have 1 database for each app)?
|
|
|
|
|
In my own experience, having tables with the same name in two different schemas on the same database has led to issues (when people writing queries have omitted the schema name when referring to the table).
If there is absolutely nothing in common between the databases then I would probably have two separate databases and manage them separately. It rather depends on how things like backups, transaction logs, etc will be handled and if there are benefits to having the schemas held on the same db.
However, if these apps are for the same corporation and use different tables then I would put them on the same database, no need for differentiating schemas and users unless you want to use them to control access, or might want to in the future.
We are currently using schemas in a corporate database to indicate which "area" (application if you like) "owns" the data in the table(s) and using the dbo schema for common data (i.e. no tables with the same name in different schema). I'm yet to discover if this will cause us problems.
As you can probably gather .. "good" or "bad" is quite subjective and depends greatly on what the overall infrastructure is going to look like
|
|
|
|
General 
News 
Suggestion 
Question 
Bug 
Answer 
Joke 
Praise 
Rant 
Admin 
Submit an article or tip
