Inserting Records in Query Based on Value from Field Table

D

david.isaacks

SQL

SELECT Count(Contacts.StatusJuly) AS CountOfStatusJuly,
Contacts.StatusJuly, [LastName] & ", " & [FirstName] AS [Full Name]
FROM Contacts
GROUP BY Contacts.StatusJuly, [LastName] & ", " & [FirstName];

StatusJuly contains three values. Normal, New, At Risk. If [Full Name]
is assinged a Normal status then I need that record to be present four
time.

Is this possible? I have been trying to work in a total query, but not
sure if I am pointed in the righ tdirection.

David
 
M

Michel Walsh

Hi,


With a table, Iotas, one field, iota, records with values from 1 to 4 (or
more):



SELECT Count(Contacts.StatusJuly) AS CountOfStatusJuly,
Contacts.StatusJuly, [LastName] & ", " & [FirstName] AS [Full Name]
FROM Contacts AS c INNER JOIN Iotas As a
ON iif(c.Status="Normal", a.iota <=4, a.iota=1)
GROUP BY Contacts.StatusJuly, [LastName] & ", " & [FirstName];



Sure, I am a little bit "show off" here, a simple WHERE clause would do just
fine too:


SELECT Count(Contacts.StatusJuly) AS CountOfStatusJuly,
Contacts.StatusJuly, [LastName] & ", " & [FirstName] AS [Full Name]
FROM Contacts AS c, Iotas As a
WHERE iif(c.Status="Normal", a.iota <=4, a.iota=1)
GROUP BY Contacts.StatusJuly, [LastName] & ", " & [FirstName];



Hoping it may help,
Vanderghast, Access MVP
 
D

david.isaacks

Thank you Sir.

David
Michel said:
Hi,


With a table, Iotas, one field, iota, records with values from 1 to 4 (or
more):



SELECT Count(Contacts.StatusJuly) AS CountOfStatusJuly,
Contacts.StatusJuly, [LastName] & ", " & [FirstName] AS [Full Name]
FROM Contacts AS c INNER JOIN Iotas As a
ON iif(c.Status="Normal", a.iota <=4, a.iota=1)
GROUP BY Contacts.StatusJuly, [LastName] & ", " & [FirstName];



Sure, I am a little bit "show off" here, a simple WHERE clause would do just
fine too:


SELECT Count(Contacts.StatusJuly) AS CountOfStatusJuly,
Contacts.StatusJuly, [LastName] & ", " & [FirstName] AS [Full Name]
FROM Contacts AS c, Iotas As a
WHERE iif(c.Status="Normal", a.iota <=4, a.iota=1)
GROUP BY Contacts.StatusJuly, [LastName] & ", " & [FirstName];



Hoping it may help,
Vanderghast, Access MVP




SQL

SELECT Count(Contacts.StatusJuly) AS CountOfStatusJuly,
Contacts.StatusJuly, [LastName] & ", " & [FirstName] AS [Full Name]
FROM Contacts
GROUP BY Contacts.StatusJuly, [LastName] & ", " & [FirstName];

StatusJuly contains three values. Normal, New, At Risk. If [Full Name]
is assinged a Normal status then I need that record to be present four
time.

Is this possible? I have been trying to work in a total query, but not
sure if I am pointed in the righ tdirection.

David
Click to expand...
Click to expand...
 
D

david.isaacks

Thanks,

What does lotas & iota mean or stand for?

David

Michel said:
Hi,


With a table, Iotas, one field, iota, records with values from 1 to 4 (or
more):



SELECT Count(Contacts.StatusJuly) AS CountOfStatusJuly,
Contacts.StatusJuly, [LastName] & ", " & [FirstName] AS [Full Name]
FROM Contacts AS c INNER JOIN Iotas As a
ON iif(c.Status="Normal", a.iota <=4, a.iota=1)
GROUP BY Contacts.StatusJuly, [LastName] & ", " & [FirstName];



Sure, I am a little bit "show off" here, a simple WHERE clause would do just
fine too:


SELECT Count(Contacts.StatusJuly) AS CountOfStatusJuly,
Contacts.StatusJuly, [LastName] & ", " & [FirstName] AS [Full Name]
FROM Contacts AS c, Iotas As a
WHERE iif(c.Status="Normal", a.iota <=4, a.iota=1)
GROUP BY Contacts.StatusJuly, [LastName] & ", " & [FirstName];



Hoping it may help,
Vanderghast, Access MVP




SQL

SELECT Count(Contacts.StatusJuly) AS CountOfStatusJuly,
Contacts.StatusJuly, [LastName] & ", " & [FirstName] AS [Full Name]
FROM Contacts
GROUP BY Contacts.StatusJuly, [LastName] & ", " & [FirstName];

StatusJuly contains three values. Normal, New, At Risk. If [Full Name]
is assinged a Normal status then I need that record to be present four
time.

Is this possible? I have been trying to work in a total query, but not
sure if I am pointed in the righ tdirection.

David
Click to expand...
Click to expand...
 
D

david.isaacks

I keep getting errors. Tell me it cannot find table or query lotas. If
I name a table then it can't find iota and then returns no records.

David

Michel said:
Hi,


With a table, Iotas, one field, iota, records with values from 1 to 4 (or
more):



SELECT Count(Contacts.StatusJuly) AS CountOfStatusJuly,
Contacts.StatusJuly, [LastName] & ", " & [FirstName] AS [Full Name]
FROM Contacts AS c INNER JOIN Iotas As a
ON iif(c.Status="Normal", a.iota <=4, a.iota=1)
GROUP BY Contacts.StatusJuly, [LastName] & ", " & [FirstName];



Sure, I am a little bit "show off" here, a simple WHERE clause would do just
fine too:


SELECT Count(Contacts.StatusJuly) AS CountOfStatusJuly,
Contacts.StatusJuly, [LastName] & ", " & [FirstName] AS [Full Name]
FROM Contacts AS c, Iotas As a
WHERE iif(c.Status="Normal", a.iota <=4, a.iota=1)
GROUP BY Contacts.StatusJuly, [LastName] & ", " & [FirstName];



Hoping it may help,
Vanderghast, Access MVP




SQL

SELECT Count(Contacts.StatusJuly) AS CountOfStatusJuly,
Contacts.StatusJuly, [LastName] & ", " & [FirstName] AS [Full Name]
FROM Contacts
GROUP BY Contacts.StatusJuly, [LastName] & ", " & [FirstName];

StatusJuly contains three values. Normal, New, At Risk. If [Full Name]
is assinged a Normal status then I need that record to be present four
time.

Is this possible? I have been trying to work in a total query, but not
sure if I am pointed in the righ tdirection.

David
Click to expand...
Click to expand...
 
D

david.isaacks

I think what i was trying to say is that I want to display the same
records x number of times based on the value of a field.

[StatusJuly] has three values that could be entered into it. New,
Normal, or At Risk.

I want to run a query that list all the records based off of what thier
[StatusJuly] is.

New = 8
Normal = 4
At Risk = 8

If I don't need a query to do this, then can I do it in a report.

Query/Report

[FullName] [StatusJuly]


David
 
M

Michel Walsh

Hi,


Iotas is a table you create. It has one field, iota, its primary key. For
rows, add values from 1 to "n", in your case, it seems you just need 8
records (with values from 1 to 8), but you can add more if you want, or if
you need for other situations.


Hoping it may help,
Vanderghast, Access MVP


Thanks,

What does lotas & iota mean or stand for?

David

Michel said:
Hi,


With a table, Iotas, one field, iota, records with values from 1 to 4
(or
more):



SELECT Count(Contacts.StatusJuly) AS CountOfStatusJuly,
Contacts.StatusJuly, [LastName] & ", " & [FirstName] AS [Full Name]
FROM Contacts AS c INNER JOIN Iotas As a
ON iif(c.Status="Normal", a.iota <=4, a.iota=1)
GROUP BY Contacts.StatusJuly, [LastName] & ", " & [FirstName];



Sure, I am a little bit "show off" here, a simple WHERE clause would do
just
fine too:


SELECT Count(Contacts.StatusJuly) AS CountOfStatusJuly,
Contacts.StatusJuly, [LastName] & ", " & [FirstName] AS [Full Name]
FROM Contacts AS c, Iotas As a
WHERE iif(c.Status="Normal", a.iota <=4, a.iota=1)
GROUP BY Contacts.StatusJuly, [LastName] & ", " & [FirstName];



Hoping it may help,
Vanderghast, Access MVP




SQL

SELECT Count(Contacts.StatusJuly) AS CountOfStatusJuly,
Contacts.StatusJuly, [LastName] & ", " & [FirstName] AS [Full Name]
FROM Contacts
GROUP BY Contacts.StatusJuly, [LastName] & ", " & [FirstName];

StatusJuly contains three values. Normal, New, At Risk. If [Full Name]
is assinged a Normal status then I need that record to be present four
time.

Is this possible? I have been trying to work in a total query, but not
sure if I am pointed in the righ tdirection.

David
Click to expand...
Click to expand...
Click to expand...
 
M

Michel Walsh

Hi,


Indeed, if, as example, you have a field Qty and want to print one label for
each product, it would be:

SELECT a.*
FROM a
INNER JOIN iotas AS b
ON a.qty >= b.iota


which will produce a number of records equal to the "qty" specified in the
original record.



Hoping it may help,
Vanderghast, Access MVP
 

Ask a Question

Want to reply to this thread or ask your own question?

You'll need to choose a username for the site, which only take a couple of moments. After that, you can post your question and our members will help you out.

Ask a Question

Similar Threads

Access Creating names from fields in access database. 0
combine a new primary key value as the fullname initial 2
Union query- count records 2
INSERT Query woes... 9
I want to display the same records x number of times based on the value of a field. 2
Trying to combine multiple records into one record in a Query 0
Append from 1 table to 2 tables 3
Trying to combine multiple records into one record in a Query 1

Top