HELP : IN Duplicaiting record in query based on condition

[eddii]

i have a query named : {imtemasterqry} in which i have record entered only once for calculation purpose

i want to duplicate the records based on [freq] and change [monthofcal]

Sqlview

SELECT IMTEMASTER.IMTENAME, IMTEMASTER.IMTENUMBER, IMTEMASTER.SHOP, IMTEMASTER.PLANT, IMTEMASTER.mfgdate, IMTEMASTER.FREQ, IMTEMASTER.monthofcal
FROM IMTEMASTER;


IMTENAMe----------------IMTENUMBER---SHOP------PLANT---mfgdate---------FREQ-------monthofcal
RADIUS GAUGE--------------Q99401A04----QD--------P1------2006-------------24-----------JAN
TAPE-----------------------Q08433D01----QD--------P2------2008-------------12-----------JAN
DIGITAL TORQUE WRENCH---Q06421F51----QD--------P1------2006-------------6------------JAN
DIGITAL TORQUE WRENCH---Q09428F51----QD--------P1------2009-------------3------------JUN
DIGITAL TORQUE WRENCH---Q10433F51----ASSY------p1------2010-------------2------------JAN

i want a new query which have fields based on calculation
if [freq] is less than 12 (that is months) then it should calculate the [freq] at copy the record based on [freq] resulting in if 6 then two times a year if 3 then 4 times a year if 2 then 6 times a year
i want this result by changing [monthofcal] should change based on [freq] and duplicate records and get this resut in a new query what should i do to get this result

IMTENAMe----------------IMTENUMBER---SHOP------PLANT---mfgdate---------FREQ-------monthofcal
RADIUS GAUGE--------------Q99401A04----QD--------P1------2006-------------24-----------JAN

TAPE-----------------------Q08433D01----QD--------P2------2008-------------12-----------JAN

DIGITAL TORQUE WRENCH---Q06421F51----QD--------P1------2006-------------6------------JAN
DIGITAL TORQUE WRENCH---Q06421F51----QD--------P1------2006-------------6------------JuN

DIGITAL TORQUE WRENCH---Q09428F51----QD--------P1------2009-------------3------------JUN
DIGITAL TORQUE WRENCH---Q09428F51----QD--------P1------2009-------------3------------sep
DIGITAL TORQUE WRENCH---Q09428F51----QD--------P1------2009-------------3------------dec
DIGITAL TORQUE WRENCH---Q09428F51----QD--------P1------2009-------------3------------mar

DIGITAL TORQUE WRENCH---Q10433F51----ASSY------p1------2010-------------2------------JAN
DIGITAL TORQUE WRENCH---Q10433F51----ASSY------p1------2010-------------2------------mar
DIGITAL TORQUE WRENCH---Q10433F51----ASSY------p1------2010-------------2------------may
DIGITAL TORQUE WRENCH---Q10433F51----ASSY------p1------2010-------------2------------JuN
DIGITAL TORQUE WRENCH---Q10433F51----ASSY------p1------2010-------------2------------Aug
DIGITAL TORQUE WRENCH---Q10433F51----ASSY------p1------2010-------------2------------oct
DIGITAL TORQUE WRENCH---Q10433F51----ASSY------p1------2010-------------2------------nov


as i dont know how to do it pls help

 

[vbaInet]

You can't duplicate records in a query unless they exist in the data source. The duplicate data must exist in the table.

 

[eddii]

THANKS vbaInet : HELP : IN Duplicaiting record in query based on condition

THANKS vbaInet : for checking the thread

as you suggest i have created a duplicate freq table


I have crude idea but i dont know how to put the formula in the query

i have created a new table called [freqcalculate]
in this table i have entered [freq] & [freqmonth]

[freqcal ]
6------twice
6------twice
36------once
12------once
24------once
3------four times
3------four times
3------four times
3------four times

[freqmonth] i have to put the formula

which may be like this

can this formula be put
exp1: add [monthofcal]+[freq]
-----------[jan] + 6
so that it gives result [dec]


this mutliplies the table to
and gives this result



IMTENAMe----------------IMTENUMBER---------FREQ-------monthofcal------FREQMONTH
RADIUS GAUGE--------------Q99401A04-------------24-----------JAN------

TAPE-----------------------Q08433D01-------------12-----------JAN------

DIGITAL TORQUE WRENCH---Q06421F51-------------6------------JAN------
DIGITAL TORQUE WRENCH---Q06421F51-------------6------------JAN------

DIGITAL TORQUE WRENCH---Q09428F51-------------3------------JUN------
DIGITAL TORQUE WRENCH---Q09428F51-------------3------------JUN------
DIGITAL TORQUE WRENCH---Q09428F51-------------3------------JUN------
DIGITAL TORQUE WRENCH---Q09428F51-------------3------------JUN------

but how to change the month of [freqmonth] which once gives the monthofcal and other nextmonth of cal


then i will be able to get this result by joining this query


IMTENAMe----------------IMTENUMBER---------FREQ-------monthofcal------FREQMONTH
RADIUS GAUGE--------------Q99401A04-------------24-----------JAN------JAN

TAPE-----------------------Q08433D01-------------12-----------JAN------JAN

DIGITAL TORQUE WRENCH---Q06421F51-------------6------------JAN------JAN
DIGITAL TORQUE WRENCH---Q06421F51-------------6------------JAN------JUL

DIGITAL TORQUE WRENCH---Q09428F51-------------3------------JUN------JUN
DIGITAL TORQUE WRENCH---Q09428F51-------------3------------JUN------SEP
DIGITAL TORQUE WRENCH---Q09428F51-------------3------------JUN------DEC
DIGITAL TORQUE WRENCH---Q09428F51-------------3------------JUN------MAR




i waNT BOLD TO BE FILLED BY FORMULA SOMETHING LIKE THIS

can this formula be put IF REQUIRED SUGGEST AN IDEA

exp1: add [monthofcal]+[freq]
-----------[jan] + 6
so that it gives result [JUL]


VbaInet pls look into the query named [bothjoined]

 

[jdraw]

From a relational database perspective you would not have duplicate data. Perhaps you should tell us what you are trying to achieve, and perhaps someone will have some options for ways to do it.

The general advice is DO NOT store calculated values. Use a query to do the calculation; print the query result if you need it.

If you want to Update field(s) in a record, you use an UPDATE query.
see http://www.techonthenet.com/sql/update.php

 

[eddii]

Thanks jdraw Re: HELP : IN Duplicaiting record in query based on condition

Thanks jdraw for reply

the filed named [freq] contains month intervals and the field named [monthofcal] contains the start of month

example :

FREQ-------monthofcal------FREQMONTH
6------------JAN------
6------------JAN------


if [freq] is 6 then it is two times a year
so [monthofcal] for in the query i get is as above ( jan,jan) and change should be in [freqmonth]

but i want this to be

FREQ-------monthofcal------FREQMONTH
6------------JAN------JAN
6------------JAN------JUL

so [monthofcal] for in the query i get is as above ( jan,Jan) and change should be in [freqmonth] as ( Jan,Jul)

i want this result but dont know how to do this just check with my query named [bothjoined] which is a combination of two queries joined together by [freq] so that the records will repeat twice and thrice etc

 

[vbaInet]

You're not explaining the original idea behind this. Let's just forget about what you're trying to do right now and try to explain what the overall objective of this exercise is?

 

[eddii]

Thanks vabInet :Re: HELP : IN Duplicaiting record in query based on condition

Thanks vabInet for the patience and listening to me
i was out of office so could not reply sorry

i am preparing a calibration database in which i have a list of instruments which are entered once in the table and these instruments will be calibrated every now and them based on the frequency[freq] of calibration which may vary from (1/4months)once in 4 months, once in 6 months (1/6months) and (1/year)once in a year and (1/2years) once in 2 years and (1/3years)once in three years and (1/4years) once in 4 years. and these will be planned in respective months [monthofcal] like (jan,feb,mar,... etc till dec)

and all the calibrated results will be in a seperate table which will have only a reference number [imtenumber] and this will link both tables

when we check for calibration certificate of one whole year form jan to dec what happens is all the imtes will be appeared in the report only once. but we would have done calibration two or 3 times for instrument whose frequency (1/4months)once in 4 months, should come every four months in a year and once in 6 months (1/6months) should come twice in a year.

what i want is in a query based on frequency the month of cal should change and i should have the record 2 or 3 times so that i will be able to see the report

still easy means

if freq is 6 then it should be twice in a query with different month

if [freq]=6 and [monthofcal]=jan then it should show in other field as jan in one row and jul in next row
6=jan
6=jul

 

[vbaInet]

I didn't completely follow but I get the gist. It sounds like a design flaw to want to display bogus values based on frequency.

Every calibration should be recorded with the date it was calibrated and the item it was calibrated. It is from this table that you will get the correct number of records per tool.

 

[eddii]

Thanks vbaInet :Re: HELP : IN Duplicaiting record in query based on condition

thanks vbaInet

your are right i have stored all calibration details in a table which has calibration date and next calibraiton date
but the problem is i cannot connect based on calibration date because some times they do not calibration according to the plan

example if it should be calibrated in jun/2011 they might do the calibration on aug/2011 delay by 2 months

and some times they dont calibrated at all which is missed that year

so i have decided to get details on next calibraiton due date basis which calculates on frequency if i matches its calibrated or else it is pending

and some time the instruments will be scraped that year and it should not come in next year plan so i have done in this way

just try to get some idea to solve this issue because i know you will find the way for it

if dont mind pls help thank you