Software·43,781 discussions

Newbie – help creating code for Access 07

Locked

Hello!

I am a newbie with script, and a beginner with Access 2007 with ability to create basic queries, etc.

For the following table I am creating for work, I need code that will query to ONLY display the rows of Product Codes (PROD CODE), where subsequent rows of that product code of money1 and money2 columns (MON1 and MON2) do not net. Multiple rows exist, and if possible, we only need to see the rows that do not pair off for each product.

The attached sample table is marked with asterisks for the rows that need to to be removed from the result of the query.

SORRY, pasting the table is messy. Imagine the number values alternate, from column MON1 in first row to MON2 in second row, etc.

I cannot hand-write code yet, so I would appreciate any help!

PROD CODE MON1 MON2 CWM
AAI -120 W
AAI -120 C
AASS 15300 W
AASS 15300 C
AASS 44198 W
AASS 44198 C
AASS 804 W *
AASS 904 C *
ABC -11100 W
ABC -11100 C
ABJO -30 C
ABJO -20 W
ABJO -50 C
ABXQ 2650 C *
ADPW 2205 W
ADPW 2205 C

Topic

Clarifications

In reply to Newbie – help creating code for Access 07

Clarifications

Well that’s a total nightmare

In reply to Newbie – help creating code for Access 07

If you were using a database with table functions and or cursors. You could probably do with the Coalesce function as well as Mon1,Mon2 is a complete waste of space, but access doesn’t have that either…

You have got to change that table.

For a start there is no explicit order.

Add a column Prod_Order,
type integer and make it an identity, if you’ve nothing else. and if this is a permanent table, make it the priomary key.

Rule one in database design never have a table without a key.

get rid of Mon1,Mon2 with

Select Prod_Order,[Prod Code]as Prod_Code,
Case When MON1 is not null Then MON2
else Mon1
As Amount,CWM
From [Product Codes]

Call that qryProductCodes

At that point you can find the first one
with something like

Select Prod_Code,Min(Prod_Order) as FirstInstance
Group By Prod_Code
From qryProductCodes

Call the query say qryFirstInstance

Then you join that back to qryProdcutCodes
to get each first row

e.g
Select pc.* From qryProductCodes pc, qryFirstIntance q
Where pc.Prod_Order = q.FirstInstance

Call That qFirstInstanceDetail

and then again to get the sum of all the other ones,

e.g.

Select pc.Prod_Code,Sum(PC.Amount) as amount From qryProductCodes pc, qryFirstIntance q
Where pc.Prod_Order <> q.FirstInstance
and pc.Prod_code = q.Prod_Code

Call that qryRestOfThem

Then you can join those results to get the answer

e.g.

Select d.Prod_Code,d.Amount,d.CWM From
qryFirstInstanceDetail d,
QryRestofThem r
Where d.Prod_Code = e.Prod_Code
and d.Amount <> r.Amount

Only other way would be to write a script to do it logic wise in VBA, but that’s a clueless numpty trick, or a one off power user script for analysis purposes.

Sometimes you have to have a table design that makes it difficult to do some things, because others were more important, if you don’t, don’t…

PS
Why are you making things harder for yourself by having column and table names with spaces in them?

PPs I’m not picking on you, this is classic newbie problem trying to use SQL which is effectively set based on data that can’t be described as a set.

Don’t know how much new stuff I’ve sprung on you either :p

I didn’t check any of it for dumbass errors either, the theory is sound though, if I’ve understood what you meant.

RE: Well that’s a total nightmare

In reply to Well that’s a total nightmare

Not the subject line I hoped to see! Haha! Thank you for the detailed logic.

I am a beginner with Access 07, and only know enough SQL to know the basics to follow the simplest lines. I will take a couple days after work to try to create the queries as described.

My table structure is what it must be for the report we are given. In this case, we cannot chose our data or data structure. It is a new report, thus table, every day to overwrite the previous day’s data. We can combine data in order to do the queries, but when the results are displayed back out, the individual records must show, and the values must show under MON1 or MON2 as they did in the original table.

Do these steps provide an output such as the following (if spacing keeps in this post), where the money values are identified by their original and respective column names?

PROD_CODE MON1 MON2 CWM
AAI -120 W
AAI -130 C
AASS 804 W
AASS 904 C
ABXQ 2650 C
ADPW 2205 W
ADPW 3205 C

No

In reply to RE: Well that’s a total nightmare

Get rid of the step that takes Mon1 or Mon2 and amend the rest of the queries.

Or do one more step and join to the original table by [prod code]

When you get lumbered with this sort of drivel, break it down into steps doing a littel bit at a time, check teh intermediate results, then you can have a look at optimisation if required.

Don’t do it all in one go from the start though, you’ll confuise the heck out of yourself.

PS I was thing after I posted if you don’t like case, you could do

Select IsNull(Mon1,0) + IsNull(Mon2,0)

If you don’t coalesce these two fields though the next stages will be even more of a mare,

[Author]

Replies