relating query's (1 Viewer)

cluster

New member
Local time
Today, 06:31
Joined
Dec 19, 2021
Messages
9
I got a table of 7 numbers (between 1-45) on a certain datel. Now I want to get them in to a query with a date row and row numbers from 1-45 now would like to mark those numbers picked on a certain date marked with an X. I tried this with IIF but can't get it. I managed to make a query where the dates of '1' are being submitted but I can't seem to get them in my in my collum of '1' I'll post a snapshot with it

this is what i'd like to see in the end as an example:

numbers on certain date in one table :
Datenr1nr2nr3nr4nr5nr6nr7
06/10/20012568224142

now i want to get them in a different query in this way depending on what is on first table offcourse
date123456789....45
06/10/2001xxxx.....45

statistic.JPG


Marked the X's by hand offcourse
 

plog

Banishment Pending
Local time
Yesterday, 23:31
Joined
May 11, 2011
Messages
11,013
Whatever it is you are ultimately trying to accomplish, this isn't the way to do it in Access. Both of the tables you described are improperly set up and should be fixed if you want to proceed in Access. That process of properly setting up data in a database is called normalization:


If you would like to tell us the ultimate aim of what you are trying to accomplish, we can help you get there with a properly structured database. So please, tell us what this data represents and then what you are ultimately working towards with this query. Don't talk in terms of this query or what you are going to do with it, tell us in general what the database is for and what the data you produce will help you achieve.
 

cluster

New member
Local time
Today, 06:31
Joined
Dec 19, 2021
Messages
9
well, lets put it this way
In Belgium we have a lottery, i'm trying to make a statistic of it (my dad did this before succesfully by hand).
now what i'm trying to do is the following:
i have a database of each draw of 7 numbers (random numbers from 1-45) on weekly bases
now i want those numbers drawn represented in a query marked with x's and sorted on date offcourse
like i showed in previous post
 

bastanu

AWF VIP
Local time
Yesterday, 21:31
Joined
Apr 13, 2010
Messages
1,207
Here you go, you need a union query first to normalize your data as recommended by plog then a crosstab to display it as you want.
 

Attachments

  • Database29.accdb
    388 KB · Views: 221

oleronesoftwares

Passionate Learner
Local time
Yesterday, 21:31
Joined
Sep 22, 2014
Messages
1,140
i have a database of each draw of 7 numbers (random numbers from 1-45) on weekly bases
now i want those numbers drawn represented in a query marked with x's and sorted on date offcourse
for your numbers table, do away with fields nr1, nr2, etc , just have a single column called nr




You might have to write 45 insert queries(to handle for each of the numbers 1-45), and put them in a macro to be run regularly

The insert query will insert the date, and X

e.g for 1
INSERT INTO secondarytable ( date, 1 )
SELECT numbers.date, "X" AS expr1
FROM numbers
WHERE (((numbers.nr)="1") AND ((numbers.posted)="no"));


for 2 it will be
INSERT INTO secondarytable ( date, 2 )
SELECT numbers.date, "X" AS expr1
FROM numbers
WHERE (((numbers.nr)="2") AND ((numbers.posted)="no"));;


finally
You can have a column in the numbers table to indicate the records that have been inserted into the secondary table, which will be done via an update statement that will run after the insert queries in the macro

e.g UPDATE numbers SET posted.Condition = "yes";

you can then put a msgbox at the end of the macro, this will show the message "routine successful" this msg box will not show if any of the queries did not work, but if all queries worked then from the msg box you will know.
 

cluster

New member
Local time
Today, 06:31
Joined
Dec 19, 2021
Messages
9
Great bastanu,

This was exactly what I was looking for, I did came across that 'union queary' but didn't see it fit to use, I also tried crosstab with my tab but to no good, this is exactly what I needed many thanks.
by the way where do you write this code to make that query a 'union query'?

To oleronesoftwares

I was thinkin' in the same direction of making 45 queries, but in the end I couldn't have written the insert queries cause I wouldn't possibly know where to write them (you might if you like wanna fill me in on that).
But anyways Bastanu's idea came out great without having to write 45 inserts.

Offcourse I do want to thank you for your effort.

Kind Regards to both of you
:p:p:p (me VERY happy!!!!)
 

Users who are viewing this thread

Top Bottom