If this is your first visit, be sure to check out the FAQ by clicking the link above. You may have to register before you can post: click the register link above to proceed. To start viewing messages, select the forum that you want to visit from the selection below. |
|
|
Thread Tools | Display Modes |
#1
|
|||
|
|||
Use calculated text to link query to table?
I import a large sales report that has a "full item description" field, part
of which is a lot number, part of which is a container code. To apply costs I have to split that into each individual component. I have successfully created a query to do that using left() and right() and IIf statements. I am having trouble getting that query to link to the rest of my cost info which is based on lot and contcode. Both lot and contcode are entered as text. I am very new to access so I am probably doing something silly wrong. |
#2
|
|||
|
|||
Use calculated text to link query to table?
I am having trouble getting that query to link to the rest of my cost info
which is based on lot and contcode. Post the query SQL by opening in design view, click on VIEW - SQL View, hightlight all, copy, and paste in a post. -- Build a little, test a little. "Robbro" wrote: I import a large sales report that has a "full item description" field, part of which is a lot number, part of which is a container code. To apply costs I have to split that into each individual component. I have successfully created a query to do that using left() and right() and IIf statements. I am having trouble getting that query to link to the rest of my cost info which is based on lot and contcode. Both lot and contcode are entered as text. I am very new to access so I am probably doing something silly wrong. |
#3
|
|||
|
|||
Use calculated text to link query to table?
..SELECT DISTINCTROW Sales.[Customer Name], Sales.[Full Product],
Sum(Sales.Amount) AS [Sum Of Amount], Sum(Sales.[Net Wt]) AS [Sum Of Net Wt], IIf(Left([Full Product],1)="8",Left([Full Product],8),Left([Full Product],7)) AS lot, Right([Full Product],3) AS contcode INTO [Sales Query Table] FROM Sales GROUP BY Sales.[Customer Name], Sales.[Full Product], IIf(Left([Full Product],1)="8",Left([Full Product],8),Left([Full Product],7)), Right([Full Product],3); |
#4
|
|||
|
|||
Use calculated text to link query to table?
Try this --
INSERT INTO [Sales Query Table] ( [Customer Name], [Full Product], [Amount] [Net Wt], [Lot], [contcode] ) SELECT DISTINCTROW Sales.[Customer Name], Sales.[Full Product], Sum(Sales.Amount) AS [Sum Of Amount], Sum(Sales.[Net Wt]) AS [Sum Of Net Wt], IIf(Left([Full Product],1)="8",Left([Full Product],8),Left([Full Product],7)) AS lot, Right([Full Product],3) AS contcode [Sales Query Table] FROM Sales GROUP BY Sales.[Customer Name], Sales.[Full Product], IIf(Left([Full Product],1)="8",Left([Full Product],8),Left([Full Product],7)), Right([Full Product],3); -- Build a little, test a little. "Robbro" wrote: .SELECT DISTINCTROW Sales.[Customer Name], Sales.[Full Product], Sum(Sales.Amount) AS [Sum Of Amount], Sum(Sales.[Net Wt]) AS [Sum Of Net Wt], IIf(Left([Full Product],1)="8",Left([Full Product],8),Left([Full Product],7)) AS lot, Right([Full Product],3) AS contcode INTO [Sales Query Table] FROM Sales GROUP BY Sales.[Customer Name], Sales.[Full Product], IIf(Left([Full Product],1)="8",Left([Full Product],8),Left([Full Product],7)), Right([Full Product],3); |
#5
|
|||
|
|||
Use calculated text to link query to table?
Ok, I'm no good with this sql stuff, so I'm probably doing somethign wrong.
After fixing some formatting problems from copying this into a new query sql window I finally get the error: Query input must contain at least one table or query. "KARL DEWEY" wrote: Try this -- INSERT INTO [Sales Query Table] ( [Customer Name], [Full Product], [Amount] [Net Wt], [Lot], [contcode] ) SELECT DISTINCTROW Sales.[Customer Name], Sales.[Full Product], Sum(Sales.Amount) AS [Sum Of Amount], Sum(Sales.[Net Wt]) AS [Sum Of Net Wt], IIf(Left([Full Product],1)="8",Left([Full Product],8),Left([Full Product],7)) AS lot, Right([Full Product],3) AS contcode [Sales Query Table] FROM Sales GROUP BY Sales.[Customer Name], Sales.[Full Product], IIf(Left([Full Product],1)="8",Left([Full Product],8),Left([Full Product],7)), Right([Full Product],3); -- Build a little, test a little. "Robbro" wrote: .SELECT DISTINCTROW Sales.[Customer Name], Sales.[Full Product], Sum(Sales.Amount) AS [Sum Of Amount], Sum(Sales.[Net Wt]) AS [Sum Of Net Wt], IIf(Left([Full Product],1)="8",Left([Full Product],8),Left([Full Product],7)) AS lot, Right([Full Product],3) AS contcode INTO [Sales Query Table] FROM Sales GROUP BY Sales.[Customer Name], Sales.[Full Product], IIf(Left([Full Product],1)="8",Left([Full Product],8),Left([Full Product],7)), Right([Full Product],3); |
#6
|
|||
|
|||
Use calculated text to link query to table?
A fresh morning and retrying it I get it to work somehow, not really sure
what I have done differently, just using the query wizard, same as I thought I was doing yesterday. Anyway thanks for the help. "KARL DEWEY" wrote: Try this -- INSERT INTO [Sales Query Table] ( [Customer Name], [Full Product], [Amount] [Net Wt], [Lot], [contcode] ) SELECT DISTINCTROW Sales.[Customer Name], Sales.[Full Product], Sum(Sales.Amount) AS [Sum Of Amount], Sum(Sales.[Net Wt]) AS [Sum Of Net Wt], IIf(Left([Full Product],1)="8",Left([Full Product],8),Left([Full Product],7)) AS lot, Right([Full Product],3) AS contcode [Sales Query Table] FROM Sales GROUP BY Sales.[Customer Name], Sales.[Full Product], IIf(Left([Full Product],1)="8",Left([Full Product],8),Left([Full Product],7)), Right([Full Product],3); -- Build a little, test a little. "Robbro" wrote: .SELECT DISTINCTROW Sales.[Customer Name], Sales.[Full Product], Sum(Sales.Amount) AS [Sum Of Amount], Sum(Sales.[Net Wt]) AS [Sum Of Net Wt], IIf(Left([Full Product],1)="8",Left([Full Product],8),Left([Full Product],7)) AS lot, Right([Full Product],3) AS contcode INTO [Sales Query Table] FROM Sales GROUP BY Sales.[Customer Name], Sales.[Full Product], IIf(Left([Full Product],1)="8",Left([Full Product],8),Left([Full Product],7)), Right([Full Product],3); |
Thread Tools | |
Display Modes | |
|
|