By using this site, you agree to our updated Privacy Policy and our Terms of Use. Manage your Cookies Settings.
434,998 Members | 2,871 Online
Bytes IT Community
+ Ask a Question
Need help? Post your question and get tips & solutions from a community of 434,998 IT Pros & Developers. It's quick & easy.

Working around not being able to update a union

P: 3
Hi, I am trying to create a form which displays fuel uplifts for different flights, they are then reconciled against invoices and therefore need to be updated (invoice numbers added).

All flights and their associated details are kept in a table ‘tblFlights’, there are fields for each uplift (maximum of three). A simplistic example is below…

FlightID FuelUplift1 InvoiceNo1 FuelUplift2 InvoiceNo2 FuelUplif3 InvoiceNo3
175 100 3927 450 3928
176 200


Firstly, what is the best way to query the table and display individual uplifts? (I have done it using a union on the same table, but not sure if this is correct) Wanting a result similar to below…

FlightID FuelUplift InvoiceNo
175 100 3927
175 450 3928
176 200

Secondly, I need to display the results from the query in a form. The problem I ran into when doing this using a union query is that you can’t update it.

Am I trying the impossible? Or going about it completely the wrong way?

Really appreciate any help …. Chris
Aug 25 '06 #1
Share this Question
Share on Google+
4 Replies


MMcCarthy
Expert Mod 10K+
P: 14,534
A table would not normally have similar fields like this. If you separate the Fuel uplift and invoice no out into another table with a primary key reference of Invoice No (assuming no duplicates) and a reference to the Flight ID in the flight table.

Table 1
FlightID, etc.
175
176

Table 2
InvoiceNo, FlightID, FuelUplift
3927 175 100
3928 175 450
3929 176 200


Hi, I am trying to create a form which displays fuel uplifts for different flights, they are then reconciled against invoices and therefore need to be updated (invoice numbers added).

All flights and their associated details are kept in a table ‘tblFlights’, there are fields for each uplift (maximum of three). A simplistic example is below…

FlightID FuelUplift1 InvoiceNo1 FuelUplift2 InvoiceNo2 FuelUplif3 InvoiceNo3
175 100 3927 450 3928
176 200


Firstly, what is the best way to query the table and display individual uplifts? (I have done it using a union on the same table, but not sure if this is correct) Wanting a result similar to below…

FlightID FuelUplift InvoiceNo
175 100 3927
175 450 3928
176 200

Secondly, I need to display the results from the query in a form. The problem I ran into when doing this using a union query is that you can’t update it.

Am I trying the impossible? Or going about it completely the wrong way?

Really appreciate any help …. Chris
Aug 25 '06 #2

P: 3
Unfortunately the table has been set up this way from then beginning (a number of years ago), and has loads of data already in it, plus there are many other parts of the database which now rely on this. Therefore I am reluctant to change it as it would mean huge structural changes.

If it is going to be an ongoing issue I might have to look into doing a big redesign?!?

Thanks,

Chris
Aug 25 '06 #3

MMcCarthy
Expert Mod 10K+
P: 14,534
I understand your predicament. However, in may experience and table structure that's not fully normalised causes these query issues to arise again and again. You are essentially limiting your query options.

Unfortunately the table has been set up this way from then beginning (a number of years ago), and has loads of data already in it, plus there are many other parts of the database which now rely on this. Therefore I am reluctant to change it as it would mean huge structural changes.

If it is going to be an ongoing issue I might have to look into doing a big redesign?!?

Thanks,

Chris
Aug 25 '06 #4

P: 3
Thanks mmccarthy

Will have to come up with another plan ... more than likely it will involve changing the entire structure and normalising the tables.

Chris
Aug 25 '06 #5

Post your reply

Sign in to post your reply or Sign up for a free account.