Use calculated text to link query to table?

  • Thread starter Thread starter Robbro
  • Start date Start date
R

Robbro

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.
 
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.
 
..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);
 
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);
 
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 said:
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 said:
.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);
Click to expand...
Click to expand...
 
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 said:
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 said:
.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);
Click to expand...
Click to expand...
 
Back
Top