|
|
|
Ya 
|
|
|
|
|
Car on wheels does this (5)
|
|
|
|
|
|
ya
modified 5-Apr-19 1:01am.
|
|
|
|
|
Hi all. I have two tables in access (VB6): SupplierInvoices & Sales. They both have 3 common field names: Date, Code & Qty. I need to query both tables via SQL query by a specific date range as follows (I know this is wrong, but just to give you an idea):
rs.Open "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 format(SupplierInvoices.Date, 'dd-MMM-yyyy');", cn, adOpenKeyset, adLockOptimistic
The result needs to return from both tables the sum of the qty fields for that date range. I can't get it right! Please help.
modified 5-Apr-19 0:46am.
|
|
|
|
|
|
|
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
|
|
|
|
|
General 
News 
Suggestion 
Question 
Bug 
Answer 
Joke 
Praise 
Rant 
Admin 
Submit an article or tip
