one (1 Viewer)

[daioros]

EDITED: Changed "Bikes" for "Computers" (to make it clear that data of different tables cannot be mixed).

Hi, everyone!

Imagine a database with three tables: "Users", "Cars" and "Computers".

Which is the easiest way to get only ONE report that shows each user and which cars and computers has been using (all data at the same time)?
Don't think about ownership of nothing, think more about usage of them.


Example of output:
...User1
..................Car1
..................Computer1
..................Computer2
...User2
..................Car7
..................Car8
...User3
..................Computer8
..................Computer9
...User4
..................Car1
..................Car3
..................Car4
..................Computer8
...


(To understand why this chaos, they gave me two separate Excel tables to work with)
Thanks in advance!

 

[Minty]

Welcome to AWF!

Your Cars and Bikes should be in one table with a "Vehicle type" identifier.

Assuming a vehicle can only belong to one person at a time then with the single vehicle table it's a simple query to generate the report you want.

Maybe put up a picture of your tables and relationships?

 

[daioros]

Welcome to AWF!

Your Cars and Bikes should be in one table with a "Vehicle type" identifier.

Assuming a vehicle can only belong to one person at a time then with the single vehicle table it's a simple query to generate the report you want.

Maybe put up a picture of your tables and relationships?

Thanks for your reply.
It's been more than 10 years since my last time with Access, so I couldn't see an easy solution to this.

And sorry, I cannot upload any picture due to privacy concerns.
But no worries, asume the simplest database ever.

But i'm not sure if it can be done that way, because (let's imagine a little more) "Vehicles" can be owned by two people.

thanks again.

 

[Gasman]

Thanks for your reply.
It's been more than 10 years since my last time with Access, so I couldn't see an easy solution to this.

And sorry, I cannot upload any picture due to privacy concerns.
But no worries, asume the simplest database ever.

But i'm not sure if it can be done that way, because (let's imagine a little more) "Vehicles" can be owned by two people.

thanks again.

So you would have a UserVehicle table ?
And you are seriously saying you cannot show a picture of your tables?

 

[daioros]

So you would have a UserVehicle table ?
And you are seriously saying you cannot show a picture of your tables?

If I can avoid it, no "UserVehicle" table.

And no, I cannot show you anything.
But assume that relationships are 1:M as usual.

thanks.

 

[The_Doc_Man]

You are over-constraining your situation to the point that Access might not be able to help you.

If an owner can have more than one vehicle and a vehicle can have more than one owner, you have a many/many relationship, which leads to three tables:

1. Owners
2. Vehicles (with a type field for Auto or Bike)
3. An Owner-Vehicle Junction table.

To say "if I can avoid it" is nice - but in this case, may not be possible if your problem description is accurate.

 

[LarryE]

It's pretty unlikely that one car could have several owners (although in car racing not unknown), but one owner could have several cars so:

1. An Owners table with a PrimaryKey OwnerID
2. A Vehicle table with a Primary Key VehicleID and a Foreign Key OwnerID. VehicleType is a field in this table.
3. Create a relationship between Owner table OwnerID and Vehicle table OwnerID.

 

[CJ_London]

It's pretty unlikely that one car could have several owners

My current car was owned by someone else so I don’t see why a car can’t have several owners. OP’s requirement is vague to say the least

 

[LarryE]

My current car was owned by someone else so I don’t see why a car can’t have several owners. OP’s requirement is vague to say the least

Yes, if you re keeping a history then you need a history table with dates owned from and owned to etc. that's true. I get the impression the OP was just keeping a simple list. You also need a junction table if car shows or car races are an issue. In fact, any reason where both cars and owners come together.

 

[CJ_London]

Personally I’m not interested in guessing the OP’s actual requirement. As described and the OP has stated that a vehicle can be owned by more than one person and a joining table is not allowed means the vehicle fk has to go into the owners table and consequently owners cannot own more than one vehicle.

the same applies to bikes with the same constraint

However the example shows owner’s owning more than one vehicle and more than one bike.

so a joining table is unavoidable unless you go for a very inefficient design that will need constant redesign

 

[MajP]

I do not know if anyone really answered your question.
The answer is to create the query and build a report on the query using Sorting and Grouping feature. You would group on User. That will create the view you want.

Create a grouped or summary report - Microsoft Support

Create a report in Access that groups data and displays subtotals, averages, and other aggregates.

support.microsoft.com

Sorting and grouping is a little bit of an Art to get it looking nice.

 

[LarryE]

For sure we don't know what the OP wants to track, so without that, no answer is possible.

 

[GPGeorge]

If I can avoid it, no "UserVehicle" table.

And no, I cannot show you anything.
But assume that relationships are 1:M as usual.

thanks.

Designing a usable database application requires following the principles of Normalization. If you have multiple users and multiple vehicles and any user can drive any vehicle, the only viable way to model that is the UserVehicle table. It's not an alternative design choice; it's the proper design.

I think sometimes people get overwhelmed by the task of learning and implementing valid relational table designs. However, it's a lot like deciding learning to drive a commercial vehicles is too time-consuming, so you start hauling things on the back of a bicycle. Yeah, you'll eventually get most of the stuff most of the way to destination most of the time, but the effort required is much more intense and time-consuming.

If you don't want to create that proper design, Access probably isn't the right tool for the job.

 

[LarryE]

Designing a usable database application requires following the principles of Normalization. If you have multiple users and multiple vehicles and any user can drive any vehicle, the only viable way to model that is the UserVehicle table. It's not an alternative design choice; it's the proper design.

I think sometimes people get overwhelmed by the task of learning and implementing valid relational table designs. However, it's a lot like deciding learning to drive a commercial vehicles is too time-consuming, so you start hauling things on the back of a bicycle. Yeah, you'll eventually get most of the stuff most of the way to destination most of the time, but the effort required is much more intense and time-consuming.

If you don't want to create that proper design, Access probably isn't the right tool for the job.

Of course it's overwhelming. It's a complex software product. Remember the first one you developed? I do. 1999 converting from EXCEL and no forum to consult and no one else on site to consult either. I made mistakes but eventually got a design, and from then on, knew at least the basics.. I was never a programmer, so I didn't know anything about proper coding techniques and so I learned the hard way. I just used ACCESS and EXCEL as a tool to make life a little easier for me. I knew about business process workflows though and that helped a lot. You need to know exactly WHAT you want to track though. Without that defined, you're toast.

 

[daioros]

Designing a usable database application requires following the principles of Normalization. If you have multiple users and multiple vehicles and any user can drive any vehicle, the only viable way to model that is the UserVehicle table. It's not an alternative design choice; it's the proper design.

I think sometimes people get overwhelmed by the task of learning and implementing valid relational table designs. However, it's a lot like deciding learning to drive a commercial vehicles is too time-consuming, so you start hauling things on the back of a bicycle. Yeah, you'll eventually get most of the stuff most of the way to destination most of the time, but the effort required is much more intense and time-consuming.

If you don't want to create that proper design, Access probably isn't the right tool for the job.

Normalization! Ha ha ha!
That was my first thought.

But two weeks ago they gave me this quick task for "yesterday" and couldn't find a quick and elegant solution.
And today they gave me another "crappy" Excel tables again.

Thanks!

 

[daioros]

thanks to everybody!
If I have a little time this afternoon I'll watch the video, post a example of relationships and report needed.

 

[NauticalGent]

But two weeks ago they gave me this quick task for "yesterday" and cannot find a quick solution.
And today they gave me another "crappy" Excel tables again.

I had an "old salt" for a leader while I was in the Navy who was fond of telling (growling at) me: "You can either TAKE time to do it right, or MAKE time to do it over..."

 

[Pat Hartman]

I think I worked for that guy also but I wasn't in the Navy And the man was right. Taking shortcuts doesn't pay and I have the scars to prove it.

As you describe your original example, if you really can't merge the two tables (and we all doubt it), you need to use a union query to make that report - OR use a main report with one subreport for cars and the second for bikes.

The data for the three tables cannot be rendered correctly by simply joining all three tables because although the two vehicle tables are related to the owner, they are not related to each other. You have two parallel relationships rather than one hierarchical relationship. When you join:
a--->b, a--->c - you end up creating a Cartesian product. Tables b and c will be multiplied by each other. If Joe has 2 cars and 4 computers, he will show eight rows, NOT 6 because 2 * 4 = 8.
car1, computer1
car1, computer2
car1, computer3
car1, computer4
car2, computer1
car2, computer2
car2, computer3
car2, computer4

 

[daioros]

This is ridiculous!
Now they don't like Access, they want the same results, but in Excel.
Way harder and, if we want it elegant, then impossible.
And once a week they will add more users and data. (WTF? Microsoft Access is the right solution!)

Sorry and thanks everybody for your time and ideas.

 

[Pat Hartman]

Good luck