Intricate query display
Hi all, I have set out some data, however I am not sure if it is possible to list it in a specific way
For each product (prod_ID) there is events that happen, no matter what date the event is (like lines 20-24 & 32 & 33)
product is the Formula 1 event
For each departure (dep_ID) there would be different things happening (like lines 25 & 31)
departure is the specific package (where as there could be different-longer packages with different itinerary)
Data:
What I want to do is:
List all itinerary items in date(dep_date)/time(itin_s
eg under product=3&departure=6
Day 1
-Check-in
-Concert: Calvin Harris
Day 2
-Free Practice 1
-Free Practice 2
Day 3
-Free Practice 3
-Qualifying
Day 4
-Race
-Concert: P!NK
Day 5
-Check-out
eg under product=3
Day 1
-Free Practice 1
-Free Practice 2
Day 2
-Free Practice 3
-Qualifying
Day 3
-Race
-Concert: P!NK
For each product (prod_ID) there is events that happen, no matter what date the event is (like lines 20-24 & 32 & 33)
product is the Formula 1 event
For each departure (dep_ID) there would be different things happening (like lines 25 & 31)
departure is the specific package (where as there could be different-longer packages with different itinerary)
Data:
TABLE: prod_itin
itin_ID brand_ID cons_ID date_added itin_live prod_ID dep_ID itin_day itin_IATA_start itin_start itin_finish itin_IATA_finish itin_item
20 GPT 1 9/08/2017 1:00:00 PM Yes 3 1 AEGPA 1:00:00 PM 2:30:00 PM Free Practice 1
21 GPT 1 9/08/2017 1:00:00 PM Yes 3 1 AEGPA 5:00:00 PM 6:30:00 PM Free Practice 2
22 GPT 1 9/08/2017 1:00:00 PM Yes 3 2 AEGPA 2:00:00 PM 3:00:00 PM Free Practice 3
23 GPT 1 9/08/2017 1:00:00 PM Yes 3 2 AEGPA 5:00:00 PM 6:00:00 PM Qualifying
24 GPT 1 9/08/2017 1:00:00 PM Yes 3 3 AEGPA 5:00:00 PM Race
25 GPT 1 9/08/2017 1:00:00 PM Yes 3 6 1 AUH 2:00:00 PM Check-in
31 GPT 1 9/08/2017 1:00:00 PM Yes 3 6 5 10:00:00 AM AUH Check-out
32 GPT 1 9/08/2017 1:00:00 PM Yes 3 6 1 AEGPA 8:00:00 PM Concert: Calvin Harris
33 GPT 1 9/08/2017 1:00:00 PM Yes 3 4 AEGPA 8:00:00 PM Concert: P!NK
TABLE: prod_deps
dep_ID brand_ID cons_ID date_added dep_live prod_ID dep_date dep_type dep_conf dep_sold dep_prod dep_redir dep_redir_live
5 GPT 1 9/08/2017 11:38:00 AM Yes 3 24/11/2017 40 Yes No Yes No
6 GPT 1 9/08/2017 11:38:00 AM Yes 3 23/11/2017 35 Yes No Yes NoWhat I want to do is:
List all itinerary items in date(dep_date)/time(itin_s
eg under product=3&departure=6
Day 1
-Check-in
-Concert: Calvin Harris
Day 2
-Free Practice 1
-Free Practice 2
Day 3
-Free Practice 3
-Qualifying
Day 4
-Race
-Concert: P!NK
Day 5
-Check-out
eg under product=3
Day 1
-Free Practice 1
-Free Practice 2
Day 2
-Free Practice 3
-Qualifying
Day 3
-Race
-Concert: P!NK
ASKER
Hi Pat, yes I realise that what I want is not going to be done easily. this is why I am here asking for help....
Do you want help with creating a report?
If you must have a query (I'm sure you'll have a "reason"), and you can't figure out how to do it yourself using Union queries (don't forget to generate a sequence number along the way because queries need to be sortable and there is no data in your request that would allow the rows to be sorted into the correct order) you should probable look into a gig and paying someone to do it for you. It won't be me because I don't take gigs. When you want to do something that violates Codd's 13 laws, expect it to be difficult because the RDBMS isn't going to provide any integrated, helpful solution.
Union queries can be manipulated to do what you want. But, you STILL have to follow the rules for union queries.
1. all sub queries require the same number of columns
2. all rows must conform to the data types for the columns. So if query 1, is text, number, number, date; then query 2 must also be text, number, number, date.
3. there are limits to the number of queries each union can hold. You can often get around this by unioning smaller union queries.
OR
You can write out a text file in the format you want by using VBA.
If you must have a query (I'm sure you'll have a "reason"), and you can't figure out how to do it yourself using Union queries (don't forget to generate a sequence number along the way because queries need to be sortable and there is no data in your request that would allow the rows to be sorted into the correct order) you should probable look into a gig and paying someone to do it for you. It won't be me because I don't take gigs. When you want to do something that violates Codd's 13 laws, expect it to be difficult because the RDBMS isn't going to provide any integrated, helpful solution.
Union queries can be manipulated to do what you want. But, you STILL have to follow the rules for union queries.
1. all sub queries require the same number of columns
2. all rows must conform to the data types for the columns. So if query 1, is text, number, number, date; then query 2 must also be text, number, number, date.
3. there are limits to the number of queries each union can hold. You can often get around this by unioning smaller union queries.
OR
You can write out a text file in the format you want by using VBA.
ASKER
Sorry for the lateness...
What about doing a double query ?
for example...
What if I run 1 query (which ever has an earlier start - departure query) first and within this I run the other query (event query)?
If it matches show 1 not both ?
Would this worK?
What about doing a double query ?
for example...
What if I run 1 query (which ever has an earlier start - departure query) first and within this I run the other query (event query)?
If it matches show 1 not both ?
Would this worK?
I don't see anything in either table that connects the rows into a set and I don't see anything that can be used to connect the two tables. It looks to me more like the "tables" are actually spreadsheets.
ASKER
dep_ID is the common factor between them
Most of the records in the first table don't have a dep_Id value.
ASKER
Sorry! I meant prod_ID
The records in the top table that have a dep_id do NOT have a prod_id.
ASKER
Ah sorry, they were suppose to all have a prod_ID
Sorry. I cannot match your sample data with your request. Your data shows 6 items for day 1 but you only want to show 2. How are you deciding WHICH 2 to show?
Then on day 2, you reference items that the data indicates are for Day 1.
Day 3 references day 2 items
Day 4 references items from two different days.
Either the data you posted is incorrect or the report you want is messed up.
Clean up both of them and post them again, include an attached spreadsheet or a loaded database because no one should ever have to create a table to help you with this.
I don't really have any more time to spend on this.
Then on day 2, you reference items that the data indicates are for Day 1.
Day 3 references day 2 items
Day 4 references items from two different days.
Either the data you posted is incorrect or the report you want is messed up.
Clean up both of them and post them again, include an attached spreadsheet or a loaded database because no one should ever have to create a table to help you with this.
I don't really have any more time to spend on this.
This question needs an answer!
Become an EE member today
7 DAY FREE TRIALMembers can start a 7-Day Free trial then enjoy unlimited access to the platform.
View membership options
or
Learn why we charge membership fees
We get it - no one likes a content blocker. Take one extra minute and find out why we block content.
What you are describing is a report. The report can have subreports where details can be in completely different formats. This will be trivial compared to trying to bend a query to your will.