|
I don't have any experience with Access, but from what I know from SQL server, I think when you have group by you are allowed to have in the select the aggregate and the group by field. The problem is that you have a group by from one of the tables and put other fields in the select. I think you have to use Union and get it in separate set of rows. And from that set you can try to select in an outer select. Not sure if this is possible in Access.
modified 20-Oct-19 21:02pm.
|
|
|
|
|
I have tried this but get Group by error:
rs2.Open "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 & "# 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 & "#;", cn, adOpenKeyset, adLockOptimistic
|
|
|
|
|
I mean something like:
select SupplierInvoices.Date as tDate, Sum(SupplierInvoices.QTY) as lTotal, 'supplier' as tablename
group by tdate,tablename
Union
select other table same thing
I can't test it, but I would try something like that
modified 20-Oct-19 21:02pm.
|
|
|
|
|
Thank you my good man. I am almost there!!! except I get an error saying item rs2!sTotal cannot be found:
rs2.Open "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');", cn, adOpenKeyset, adLockOptimistic
|
|
|
|
|
The error you are getting is because in Union the column names have to be the same. That's why you need another column tablename to distinguish between them
I would also try to put in the first original query
Select distinct format(SupplierInvoices.Date, 'dd-MMM-yyyy') as tDate, Sum(SupplierInvoices.QTY) as tTotal from SupplierInvoices, format(Sales.Date, 'dd-MMM-yyyy') as sDate, Sum(Sales.QTY) as sTotal from Sales where PLU = '" & lblPLU.Caption & "' and Date Between #" & StartTime & "# and #" & EndTime & "# Group by tDate, sDate
Wouldn't that work too?
modified 20-Oct-19 21:02pm.
|
|
|
|
|
I tried it but says error in from clause.
I appreciate your help.
|
|
|
|
|
I also tried:
rs2.Open "Select distinct format(SupplierInvoices.Date, 'dd-MMM-yyyy') as tDate, Sum(SupplierInvoices.QTY) as tTotal, format(Sales.Date, 'dd-MMM-yyyy') as sDate, Sum(Sales.QTY) as sTotal from SupplierInvoices, Sales where PLU = '" & lblPLU.Caption & "' and Date Between #" & StartTime & "# and #" & EndTime & "# Group by tDate, sDate;", cn, adOpenKeyset, adLockOptimistic
but it says "the field PLU could refer to more than one...."
|
|
|
|
|
Ah poor Bruno, this weeks lame duck.
Asking a programming question in the Lounge where it clearly states not to.
Doesn't know enough netiquette not the SHOUT.
Having to support a VB6 application more than 20 years after it's EOL.
Using Access as the database
Man you have my sincere sympathies.
The only thing to make that worse would be to be using Crystal Reports as well.
Never underestimate the power of human stupidity -
RAH
I'm old. I know stuff - JSOP
|
|
|
|
|
I'm the orphan in the storm, yes!
But hey, I am trying!
|
|
|
|
|
I am using a SSD to hold code and (until Sunday) databases that were attached to the sql instance on my desktop and my laptop. Everything was fine until Sunday night, when I think I did some steps in the wrong order on my desktop regarding detaching/attaching databases. In the process of doing something wrong, I lost all of the database work I had done over the weekend. Half a dozen tables and almost a dozen stored procs - gone - and could not be recovered.
Today, I finished reinventing all the database crap I lost, and implemented a new policy. I create two jobs - one that backs up all the databases (that are not system databases) to the SSD, and one that restores the backed up databases from the SSD. Now all I have to do is remember to run them (and not run the wrong one when I run one).
BTW, my template project is going quite well, and I'm almost ready to make the demo video (hopefully by sometime next week.
".45 ACP - because shooting twice is just silly" - JSOP, 2010
-----
You can never have too much ammo - unless you're swimming, or on fire. - JSOP, 2010
-----
When you pry the gun from my cold dead hands, be careful - the barrel will be very hot. - JSOP, 2013
|
|
|
|
|
Cobian Backup[^] is the absolute bee's knees for the kind of replication you mention.
I wanna be a eunuchs developer! Pass me a bread knife!
|
|
|
|
|
Won't do the job. You have to use ssms or sql server to backup/restore databases. I'll only be using this as a synchronization vehicle.
".45 ACP - because shooting twice is just silly" - JSOP, 2010
-----
You can never have too much ammo - unless you're swimming, or on fire. - JSOP, 2010
-----
When you pry the gun from my cold dead hands, be careful - the barrel will be very hot. - JSOP, 2013
|
|
|
|
|
Ah, I thought you were just dumping them as blobs. I stand corrected.
I wanna be a eunuchs developer! Pass me a bread knife!
|
|
|
|
|
Losing work always sucks,  but I usually find that the second effort is better than the original. The bonus is the lesson you learned and steps taken to keep it from reoccurring. Sometimes it pays dividends to idiot-proof your own stuff! 
"Go forth into the source" - Neal Morse
|
|
|
|
|
I agree - it sucks, but the second is one is usually better than the first.
Accept that one time that I lost a new algorithm and could not recreate the thought process that allowed me to create it in the first place. It was beautiful, it was inspired, efficient and now gone forever. 
INTP
"Program testing can be used to show the presence of bugs, but never to show their absence." - Edsger Dijkstra
"I have never been lost, but I will admit to being confused for several weeks. " - Daniel Boone
|
|
|
|
|
Pierre de Fermat wrote " ...Hanc marginis exiguitas non caperet"
I have discovered a truly marvelous demonstration of this proposition that this margin is too narrow to contain."
Couldn't resist.
Cheers,
Peter
Software rusts. Simon Stephenson, ca 1994. So does this signature. me, 2012
|
|
|
|
|
Well I’m having issues with my reinvention. I feel like I have to say one more time that I hate EF6...
EDIT ===============
It's Friday morning - the afore mentioned issues have been resolved. I am now back to a functioning website and database, and I can finally say that I'm back to where I was on Sunday before I screwed up.
Final observations - EF6 sucks. In point of fact, I'm gonna go out on a limb and claim that ALL ORMs suck.
".45 ACP - because shooting twice is just silly" - JSOP, 2010
-----
You can never have too much ammo - unless you're swimming, or on fire. - JSOP, 2010
-----
When you pry the gun from my cold dead hands, be careful - the barrel will be very hot. - JSOP, 2013
modified 5-Apr-19 6:48am.
|
|
|
|
|
|
Can't be a scheduled task. It can't even be a startup task due to the nature of "the way things are" regarding the SSD. I'm sure reading my explanation would take a lot more time than you are willing to invest in reading it.
".45 ACP - because shooting twice is just silly" - JSOP, 2010
-----
You can never have too much ammo - unless you're swimming, or on fire. - JSOP, 2010
-----
When you pry the gun from my cold dead hands, be careful - the barrel will be very hot. - JSOP, 2013
|
|
|
|
|
Over the last two months Google has forced me to change my password for an account I have had from the days when you needed to be invited to have an e-mail account.
They inform me about this via an e-mail which I can not reach because I can not log in
I have all the Google we want to know settings turned off.
I also clear browsing data 10 or so times a day.
The first reason they invoked this algorithm I deleted 10 unread e-mails
I have noticed when I open Google maps my location is 20 to 50 miles off which is OK with me. My ISP is Frontier and I use Cloudflare 1.1.1.1
Short of changing all my contacts to my Proton Mail account and giving Google more information like my phone number or an alternative e-mail address
Do I have a way to stop this hair brained Google behavior?
YES I know the Google account has been pawned
|
|
|
|
|
Choroid wrote: I also clear browsing data 10 or so times a day.
Including cookies?
I wanna be a eunuchs developer! Pass me a bread knife!
|
|
|
|
|
|
Seriously?
Well, if you clear your cookies, then you clear your stored passwords and settings, so the fact that your passwords and settings aren't stored shouldn't be too much of a surprise.
Be a bit more discriminative, when cleaning out cookies.
I wanna be a eunuchs developer! Pass me a bread knife!
|
|
|
|
|
I have no stored passwords and the check box for clearing passwords is un-checked ?
|
|
|
|
|
Look, if you want to use google "services", you have to bow the knee to their absolute right to sh1t all over your personal information agree to let them retain certain information.
If you don't allow this, they will nag the cr@p out of you. That's a fact of life that is only being contested by the EU, so you're either living in the wrong place, or you've got the wrong dream.
I wanna be a eunuchs developer! Pass me a bread knife!
|
|
|
|
General 
News 
Suggestion 
Question 
Bug 
Answer 
Joke 
Praise 
Rant 
Admin 
Submit an article or tip
