Help with converting Excel to Access

[Minty]

You have used a full join between the two tables.
Paste this SQL into your query SQL design view and you will see the difference.

SELECT tblTestRigs.RigID, tblTestRigs.BayID, tblTests.EquipID, tblTests.TestType, tblTests.TestCompleted, tblTests.Failure, tblTests.TestComments, tblTestRigs.InService
FROM tblTestRigs LEFT JOIN tblTests ON tblTestRigs.LocationID = tblTests.LocationID;

If you click on the Join line in the query design view it will show you the available options.

 

[InfernoJaffa]

That worked perfect for joining the two tables.
I now need to join all the tables together to get a view to display:
RigID, BayID, SerialNo, Build, TestType, Failure, TestCompleted, TestComments

When add add another link I get an error saying it could not execute due to ambiguous outer joins?

 

[Minty]

Use similar outer joins to the one in the existing query.

However I think you need to step back from trying to exactly duplicate your Excel sheets. You have the option here to re-evaluate and produce cleverer options to interact with your data. I personally don't think you need to see all 400 bays in one go.

If you do and you want to update them I would be tempted to simply use the query you have created to display them in a form, and then double click a bay number to open a pop up form and enter the new equipment on test details to it.

Think about your business processes and build the data and forms around those.

How do you pick a bay to put a unit into? Visually? Or simply allocate the first available free bay? Use the database to help you think about this process.

 

[InfernoJaffa]

Hi Minty,

Thank you for your help, I have no idea with access so have given up on the task.:banghead:

 

[Minty]

I think you are being a little hasty - but also expecting results within 2 or 3 days when you have little or no experience with a new product is a little unrealistic.

Read some of the on-line guides, download a few samples and look at how they work.

Keep what you have and slowly work on it in the background - break down the tasks into small manageable bits and learn as you go, it will be worth it in the long run.